database is locked regression

[frioux]

My tool that uses this consistently gets database is locked. I applied the suggested fix from the FAQ and it worked for an older vesion of go-sqlite3 but not for the current version. I bisected it down to e02bbc0.

Let me know how I can assist with more details; the sqlite db I'm using is my firefox cookie jar, which is at /home/frew/.mozilla/firefox/qxp4lwy0.default/cookies.sqlite on my laptop. If you want to test it you can check out https://github.com/frioux/leatherman and run a command like this:

go build && echo "https://github.com/frioux/leatherman/commit/17ba99b79bfd741fd8aac2a4cd534aad087c83fd" | ./leatherman expand-url

[frioux]

Btw I worked around this issue by forking this repo and using the parent sha of the buggy commit, so if you try my test above via leatherman you'll want to use 49f738c5f5681dee0cef71d6e3664b69cba21e60

[MiloszD]

I had the same issue after updating to current version.
I found the solution that works for me so maybe it will help in your case.

I had sqlite file in WAL journal mode. But new driver couldn't connect to it, until i changed my connection strings in all my projects, so it contains "&_journal_mode=WAL" in dsn.

Setting "?cache=shared&mode=ro&_busy_timeout=9999999" alone, and adding
db.SetMaxOpenConns(1) was insufficient.

[frioux]

This worked for me, but I am surprised since I am only reading from the database. I wonder if the error could be more clear?

[gjrtimmer]

Question: what is the original journal mode of the firefox sqlitedb?

[frioux]

In retrospect I think firefox probably didn't change and that I upgraded go-sqlite3 on accident.

[gjrtimmer]

According to your research on which commit changed everything.

I think I understand what happend.

The journal mode was added into the DSN. And the default was set to DELETE because that is what the official documentation of SQLite states. However when opening a connection it sets the Journal Mode to DELETE while within the database file there already may have been an other selection of the journal mode.

Will look into fixing this.

[gjrtimmer]

@frioux I've created a fix for you.

Added a Journal Mode: AUTO in which the connection will take the journal mode from the database. This means that if firefox uses WAL mode this should now automatically be read from the database. Because journal mode was not added before through the DSN your behaviour slipped through the testing. I apologize for the inconvenience.

Please note the following: The fix was done here:

Repo: github.com/GJRTimmer/go-sqlite3
Branch: fix/features

Important: The fix is build upon the future v2.0.0 branch and also on top the the new embedded SEE native encryption.

Almost all options you use within a DSN starting with _ (underscore) do not work because there where changed for the v2,0.0 branch. Complete conversion list is available within the Wiki. which already was created and updated for the v2.0.0 version.

Would it be possible to test your code. You might need to change your DSN.

[frioux]

Sure, I'll try it out. If you wanted you could though, my tool is open source at https://github.com/frioux/leatherman. I'll comment back here when I get a chance (maybe Friday or Saturday.)

[gjrtimmer]

@frioux You might want to clone my fork of this repo where I do a lot of work on it.

github.com/GJRTimmer/go-sqlite3 the master is currently at version 2.0.0 with all the fixes you might require. Let me know.

[frioux]

@gjrtimmer even with the v2.0.0 branch checked out I get: /home/frew/go/src/github.com/GJRTimmer/go-sqlite3 when I try to build or test along with your version.

[gjrtimmer]

You did change your imports paths to github.com/GJRTimmer/go-sqlite3/driver ?

[frioux]

No, I'll do that.

…

-- Sent from a rotary phone rented from Ma Bell

On Fri, Jul 27, 2018, 1:17 AM Gert-Jan Timmer ***@***.***> wrote: You did change your imports paths to github.com/GJRTimmer/go-sqlite3/driver ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#607 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAAf46AJ97PxeV-bcQxqeOq2UMCM_w11ks5uKsyxgaJpZM4VNbZN> .

[alkemir]

We found the same issue. Our application uses sqlite3 databases extensively, and recently our tests would fail with "database is locked". We also bisected it down to e02bbc0. Adding "?_journal=WAL" to our DSN seems to fix the issue. So apparently, when using DELETE, multiple connections to a single database cannot be established (although our code calls db.Exec("PRAGMA journal_mode=WAL") just two lines after sql.Open. We are available for further debugging.

[frioux]

@gjrtimmer The following patch worked on my code:

diff --git a/expandURL.go b/expandURL.go
index df11a1d..43080dc 100644
--- a/expandURL.go
+++ b/expandURL.go
@@ -11,9 +11,9 @@ import (
        "os"
        "regexp"
 
+       _ "github.com/GJRTimmer/go-sqlite3/driver" // sqlite3 required
        "github.com/frioux/mozcookiejar"
        "github.com/headzoo/surf"
-       _ "github.com/mattn/go-sqlite3" // sqlite3 required
        "golang.org/x/net/publicsuffix"
 )
 
@@ -49,12 +49,11 @@ func cj() *cookiejar.Jar {
                fmt.Fprintln(os.Stderr, "MOZ_COOKIEJAR should be set for expand-url to work")
                return jar
        }
-       db, err := sql.Open("sqlite3", "file:"+path+"?cache=shared&_journal_mode=WAL")
+       db, err := sql.Open("sqlite3", "file:"+path+"?journal_mode=WAL")
        if err != nil {
                fmt.Fprintf(os.Stderr, "Failed to open db: %s\n", err)
                os.Exit(1)
        }
-       db.SetMaxOpenConns(1)
        defer db.Close()
 
        err = mozcookiejar.LoadIntoJar(db, jar)

It's still disappointing to need to know these details when the sqlite3 commandline tool doesn't require it, but I get it.

[gjrtimmer]

@frioux @alkemir Please update to my latest master; either remove journal_mode from DSN or use journal_mode=auto

I found the issue and fixed it. It now defaults first to the Journal Mode which is present within the Database META data. Instead of forcing it to DELETE it now respects and works as it should be. Sorry for this.

Let me know about your test when you have updated to my latest master.

[frioux]

@gjrtimmer

# github.com/GJRTimmer/go-sqlite3/driver
../../GJRTimmer/go-sqlite3/driver/config.go:1221: cfg.Key undefined (type *Config has no field or method Key)
../../GJRTimmer/go-sqlite3/driver/config.go:1222: cfg.Key undefined (type *Config has no field or method Key)

[gjrtimmer]

Oeps

[gjrtimmer]

@frioux I forgot to merge the fix done in v2.0.0 into my master. My deepest apologies.

[gjrtimmer]

All DONE

[frioux]

I have

caliburn 💀 🕝 [7031] ~/go/src/github.com/GJRTimmer/go-sqlite3 «v2.0.0» <141> 
$ git log -1                                                                    2:28:22 pm
commit cf260029609d8ed22e318c6e3b192cec864151ff (HEAD -> v2.0.0, origin/v2.0.0)
Author: Gert-Jan Timmer <gjr.timmer@gmail.com>
Date:   2018-08-02 11:45:39 +0200

    fix: journal mode
    
    Journal Mode should only be enforced when the user set it.
    
    Fixes: #607

but

caliburn 💀 🕝 [7033] ~GHGO/leatherman «master¹» 
$ go build                                                                      2:29:05 pm
# github.com/GJRTimmer/go-sqlite3/driver
../../GJRTimmer/go-sqlite3/driver/config.go:1221: cfg.Key undefined (type *Config has no field or method Key)
../../GJRTimmer/go-sqlite3/driver/config.go:1222: cfg.Key undefined (type *Config has no field or method Key)

[frioux]

Ugh, this has regressed again. My code currently includes:

	db, err := sql.Open("sqlite3", "file:"+path+"?cache=shared&_journal_mode=WAL")
	if err != nil {
		return nil, errors.Wrap(err, "Failed to open db")
	}
	db.SetMaxOpenConns(1)

and yet I'm getting database is locked. I'm gonna try to use go modules to pin to a specific sha at this point.

[alkemir]

@frioux are you using mattn's or GJRTimmer's repository?

[frioux]

As a side note, it does appear that firefox, the owner of the sqlite database here, may actually be locking the file, rather than just using it in a different mode. Verified by running sqlite3 $MOZ_COOKIEJAR .tables and getting Error: database is locked. That would explain why I've been unsuccessful at finding a workaround :/

[frioux]

A terrible workaround I found is that I can copy the database before connecting to it. I'll update this thread if that eventually causes issues, but in the meantime, have fun:

	orig, err := os.Open(path)
	if err != nil {
		return nil, errors.Wrap(err, "os.Open for copying")
	}

	dest, err := ioutil.TempFile("", "")
	if err != nil {
		return nil, errors.Wrap(err, "ioutil.TempFile for copying")
	}

	_, err = io.Copy(dest, orig)
	if err != nil {
		return nil, errors.Wrap(err, "io.Copy for copying")
	}
	err = dest.Close()
	if err != nil {
		return nil, errors.Wrap(err, "dest.Close for copying")
	}
	err = orig.Close()
	if err != nil {
		return nil, errors.Wrap(err, "orig.Close for copying")
	}

	db, err := sql.Open("sqlite3", "file:"+dest.Name())
	if err != nil {
		return nil, errors.Wrap(err, "Failed to open db")
	}
	defer db.Close()

        // use db

	err = os.Remove(dest.Name())
	if err != nil {
		return nil, errors.Wrap(err, "Failed to clean up db copy")
	}

[alkemir]

Can you try previous versions of your tool to confirm that this is a regression, and not some new behavior on Firefoxes side?

[frioux]

Well that's what I mean; I am pretty sure this is a change in Firefox at this point.

[azavorotnii]

Issue still happens in "master". Have a PR to fix it:
#747

[mattn]

Is this closable? (#747)

[marco-m]

Hello @frioux would you be willing to try once more, since #747 has been merged on 2019-09 ?

[frioux]

I switched to copying the database at startup so these issues went away.

…

On Thu, Jun 18, 2020, 10:42 AM Marco Molteni ***@***.***> wrote: Hello @frioux <https://github.com/frioux> would you be willing to try once more, since #747 <#747> has been merged on 2019-09 ? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#607 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAB7Y6YTFCT75V7TU43RXTRXJGXZANCNFSM4FJVWZGQ> .

[realh]

I'm having big problems with this. No matter what I try I keep getting "database is locked" errors. I've tried the workarounds in the FAQ etc, they don't work. With this driver, an sql.DB can only be used in the goroutine that created it. Even if the first goroutine is not actively using the database (no Stmts left open etc), any attempt to run an INSERT statement from a different goroutine causes this error. If I use transactions the error is deferred to Commit(), even if I Close() all the Stmts first. I've also tried using a separate Conn for each set of operations, and double-checking they're all closed asap, but it doesn't make any difference.

BTW, everything was OK until I started using sql.Stmt. Before, my queries/statements were one-shot, so I was using Query/Exec directly on the DB handle. But now I need to reuse Stmts for efficiency. I've made sure that all the Stmts are prepared in the same goroutine that they're executed in, and only one goroutine is using Stmts. One thing I haven't tried is moving the Execs to the same goroutine that opened the DB. I'm beginning to think that wouldn't help anyway. According to sqlite's wiki the error is caused by trying "to do two incompatible things with a database at the same time from the same database connection". My Conn is not leaking to another goroutine, at least not in my code. And that page implies that concurrent INSERTs are not incompatible operations anyway.

[rittneje]

@realh Do you have some sample code that demonstrates the error you are observing?

[realh]

Not at the moment, unless you have a DVB-T or DVB-S receiver.

…

On Fri, 26 Mar 2021 at 21:39, rittneje ***@***.***> wrote: @realh <https://github.com/realh> Do you have some sample code that demonstrates the error you are observing? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#607 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACDAKLYLRTJSGSRMUL3J2Z3TFT5HLANCNFSM4FJVWZGQ> .

-- TH

[azavorotnii]

@realh what connection options do you specify? There should not be any issues trying to use db connection from one go-routine in another; but depending on journal mode you might be unable to have > 1 simultaneous connection. WAL mode should enable multiple connections:
db, err := sql.Open("sqlite3", "file:"+path+"?cache=shared&_journal_mode=WAL")

[realh]

I started with no options, then I added ?cache=shared, but it still didn't help. Adding &_journal_mode=WAL has fixed it, thanks for your help.

But it seems like both these options have caveats, and attempting to use sqlite concurrently is a bad idea. What I'll do is dedicate a goroutine to sqlite and send all requests to and from it over channels.

[rittneje]

There is no issue using SQLite concurrently, provided that you understand certain interactions between database/sql and SQLite itself.

One of the ways that people get the "database is locked" errors is they attempt to modify the database in the middle of a rows.Next() loop. When you do that, database/sql tries to use a second database connection, since the first one is still "in use". But under journal mode (the default), a second database connection cannot modify the database while the first connection has a read lock. So the second connection hits its busy timeout and returns that error. To resolve this, you need to make both the reads and writes part of the same transaction.

Another way that error can happen is if you attempt to read and then write within a single transaction (in either WAL mode or journal mode). If another connection modifies the database between the read and the write, you will get that error because SQLite can no longer guarantee the consistency of the data your transaction has been working with. To resolve this, you need to use IMMEDIATE transactions.

You should never use shared cache mode, as doing so just leads to further issues.

[realh]

The problem could be that I have multiple Stmts open at the same time, and come to think of it, they are being called from different goroutines. But according to the wiki link I posted before, that should now only be a problem if I try to CREATE or DROP a table. The fix would have been to finalize or reset the statements after each Exec, but I don't think I have access to reset through the go API, and finalize would mean they wouldn't be reusable.

[rittneje]

@realh Can you confirm whether the error you are seeing is "database is locked" or "database table is locked"? They mean slightly different things.

[realh]

It's the first one (without 'table').

[rittneje]

As I suspected, the error you are getting is SQLITE_BUSY, not SQLITE_LOCKED. (Unfortunately, that page you linked has the wrong error message for SQLITE_LOCKED, adding to the confusion.) https://sqlite.org/rescode.html#busy

So the issue you are observing is due to multiple database connections interacting poorly, likely because of how database/sql is trying to manage a connection pool for you. This is the class of issues I described above.

[mholt]

Thanks for the explanation @rittneje. Instead of putting the reads and writes in the same transaction (which is difficult to do in my situation), I was able to resolve this problem by syncing DB calls with a sync.RWMutex, including all rows.Next() loops.