[Bro-Dev] [JIRA] (BIT-1325) multiple sqlite writers to same db file yields "database is locked" error

[Tony Cebzanov (JIRA)]

     [ https://bro-tracker.atlassian.net/browse/BIT-1325?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Tony Cebzanov updated BIT-1325:
-------------------------------
    Attachment: bro_sqlite_busy_wait.patch

I threw together a pretty naive patch to try to work around this problem for now -- it simply checks the sqlite error code, and if it comes back as SQLITE_BUSY it does a short usleep (repeating as necessary) until the call succeeds.

I've only tested it lightly, and in theory with a lot of log writers and locks being held for a long time this could cause the log writer threads to fall behind as they all wait to acquire the lock, but it meets my needs for what I'm doing now.

A better solution wold probably involve the log writers waiting until they have X number of records then inserting them as part of a single transaction (flushed on some kind of idle timeout) but I don't really know enough about the bro architecture to make that happen right now.

> multiple sqlite writers to same db file yields "database is locked" error
> -------------------------------------------------------------------------
>
>                 Key: BIT-1325
>                 URL: https://bro-tracker.atlassian.net/browse/BIT-1325
>             Project: Bro Issue Tracker
>          Issue Type: Problem
>          Components: Bro
>    Affects Versions: 2.3
>            Reporter: Tony Cebzanov
>              Labels: logging, sqlite
>         Attachments: bro_sqlite_busy_wait.patch
>
>
> I want to have multiple log streams logged to the same sqlite database, but when trying to log to sqlite using the following configuration:
> {code}
>     local filter: Log::Filter =
>         [
>         $name="sqlite_conn",
>         $path="analysis",
>         $config=table(["tablename"] = "conn"),
>         $writer=Log::WRITER_SQLITE
>         ];
>     Log::add_filter(Conn::LOG, filter);
>     local http_filter: Log::Filter =
>         [
>         $name="sqlite_http",
>         $path="analysis",
>         $config=table(["tablename"] = "http"),
>         $writer=Log::WRITER_SQLITE
>         ];
>     Log::add_filter(HTTP::LOG, http_filter);
> {code}
> I get the following error:
> {code}
> error: analysis/Log::WRITER_SQLITE: Error executing table creation statement: database is locked
> {code}



--
This message was sent by Atlassian JIRA
(v6.4-OD-15-055#64014)