"table not found" errors when concurrently reading from an in memory db

[jmunson]

I ran into a bug where occasionally I was getting "table not found" errors for a database we create on startup and never write to or modify. I managed to reproduce it in the following gist

https://gist.github.com/jmunson/7b1974215d34439688b0 (name it main_test.go in an empty dir and go test -v should work)

This test file will create an in memory db and create a key->value table with 3 entries, then we spawn 20 goroutines that each query for all 3 entries.

If this is ran with GOMAXPROCS set to 2 or more without setting MaxOpenConns to 1, then we get "table not found" errors.

I'm not sure if this is something that can be fixed or should just be documented around, but I thought I'd share in case anyone else runs into this.

[anacrolix]

I've gotten this too. The reason is that each connection created by database/sql opens ":memory:", and thus gets a different in-memory database. Call sql.Open("sqlite3", "file::memory:?cache=shared") and this won't happen.

[robertknight]

Just run into this. As a possible fix, go-sqlite3 could warn if ":memory:" is used as the DB path and the cache parameter is not explicitly provided as either 'shared' or 'private'. Thoughts?

[anacrolix]

I think a note in the Go docs that this is a common mistake, deferring to the SQLite documentation on the matter is better. When wrap something and introduce new behaviours in the wrapper, you only create further confusion.

[robertknight]

I think that's a fair point, except that it may not be obvious to newcomers that database/sql is using a pool of connections and how this interacts with :memory:

After doing some further performance work on my current app, it actually looks like connection pooling results a pretty severe performance penalty with SQLite and that a single open connection is a better choice in any case. I've yet to write a proper benchmark for that and understand why that is though.

[Brian-McM]

Was there any resolution to what the best method of fixing this was?

[mattn]

If you use :memory:, connection will be separated in the goroutine.

[domthinks]

@Brian-McM based on what @robertknight said, I used https://golang.org/pkg/database/sql/#DB.SetMaxOpenConns to set the DB.SetMaxOpenConns to 1.

DB.SetMaxOpenConns(1) // this resolved the "table not found" in memory error for me.

[Brian-McM]

I think I ended up doing something that, but it was pretty long time ago @domthinks so hard to remember.

[mattn]

did you try file=xxx.db?mode=memory ?

[zimmski]

Just ran into this file::memory:?cache=shared and file:thisisauniquenamebutnotafile?mode=memory&cache=shared worked for me. This should be documented. Maybe adapt the documentation for the Open method to give more examples and add a warning right there? Does that sound OK?

I do not thing that ".SetMaxOpenConns(1)" should be used since then the advantages of having more than one connection are simply gone. Also, it might be possible that a connection is dropped/expires and then SQLite just drops the database. So this can happen with one connection too. Maybe it would be wise to have the shared argument for mode=memory per default?

[mattn]

This is related on the spec or behavior on sqlite3. If we will add this into doc, it will be FAQ style. However I'm not native speaker. Anyone, could you please send me pull-request to add this FAQ?