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

[Johanna Amann (JIRA)]

    [ https://bro-tracker.atlassian.net/browse/BIT-1325?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=19914#comment-19914 ] 

Johanna Amann commented on BIT-1325:
------------------------------------

I just took a look at the source - the reason this happens at the moment is the way that we write to SQLite database. We currently create a prepared statement when opening the database for logging; this statement is then re-used for each logging line inserted into the database.

The database (apparently) remains locked as long as that statement is active. In theory, we could walk away from this approach; however, that would mean not using a prepared statement anymore and having to re-execute the insert sql statement for each line that we get sent from the main thread. Which will mean slower executions. It also means that we will have to deal with locking issues -- the current approach sidesteps that because the database is locked for writing from the moment it opens.

After looking at this, I am not sure if we even want to or should support multiple simultaneous writers to sqlite. It seems to come with a whole can of worms and potential problems. Reading from databases while a thread writes to them should still be possible at the moment.

But - I am open for other opinions on this...

> 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.2
>            Reporter: Tony Cebzanov
>              Labels: logging, sqlite
>
> 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)