[syslog-ng] syslog-ng hangs with high message volume to sqlite database

[Patrick Hemmer]

I seem to have run into a situation where syslog-ng (3.3.4) will hang 
when sending a high message volume to an sqlite3 database.
When I run the `loggen` utility with a high message rate, syslog-ng 
writes out the following error:
Jan 28 22:58:12 storm.feystorm.net syslog-ng[22839]: Error running SQL 
query; type='sqlite3', host='', port='', user='syslog-ng', 
database='/syslogtest/logs.sql3', error='5: database is locked', 
query='COMMIT'
Jan 28 22:58:12 storm.feystorm.net syslog-ng[22839]: SQL transaction 
commit failed, rewinding backlog and starting again;

Syslog-ng will sit stuck like this for a minute and then log the error 
again. Sometimes it'll write a few more entries to the database before 
it errors, but its never more than a few. I'm not sure if this issue is 
a problem with libdbi or syslog-ng, which is why I'm here and not the 
bug tracker. I came across this issue trying to track down another issue 
where syslog-ng seems to hang completely and will never recover (wont 
respond to anything other than a `kill -9`). Whether this is the same 
issue or not, I do not know.
I was able to trigger this with the following loggen command:
loggen -r 60 -I 1 --active-connections=2 -x ./socket
This server is a fairly low power server (its a central log collector 
for my home network), so it you might have to significantly bump up the 
message rate to duplicate the issue.


Config:
@version: 3.3

options {
     keep_hostname(yes);
     chain_hostnames(no);
     normalize_hostnames(yes);
     use_dns(no);
     use_fqdn(yes);
     stats_freq(0);
     mark_freq(0);
};

source s_socket {
     unix-stream("/syslogtest/socket");
};
source s_internal {
     internal();
};
destination d_internal {
     file("/syslogtest/internal.log");
};
log { source(s_internal); destination(d_internal); };

destination d_sqlite {
     sql(
         type('sqlite3')
         database("/syslogtest/logs.sql3")
         table("logs")
         columns("time", "time_r", "host", "facility", "priority", 
"program", "pid", "tag", "message")
         values("$S_UNIXTIME", "$R_UNIXTIME", "$FULLHOST", 
"$FACILITY_NUM", "$LEVEL_NUM", "$PROGRAM", "$PID", "$DBTAG", "$MSG")
         null("")
         flags(explicit-commits)
         flush_lines(10)
         flush_timeout(200)
     );
};
filter f_test1 {
     message('^prg00000') or program('syslog-ng');
};
filter f_test2 {
     message('^prg00001') or program('syslog-ng');
};
log { source(s_internal); source(s_socket); destination(d_sqlite); };
#log { source(s_internal); source(s_socket); filter(f_test1); 
destination(d_sqlite); };
#log { source(s_internal); source(s_socket); filter(f_test2); 
destination(d_sqlite); };





Database schema:
CREATE TABLE logs (id integer primary key autoincrement, time unsigned 
integer not null, time_r unsigned integer not null, host text not null, 
facility unsigned integer not null, priority unsigned integer not null, 
program text not null, pid unsigned integer, tag text, message text not 
null);
CREATE INDEX host on logs (host, time);
CREATE INDEX program on logs (program, time);