`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 e02bbc03819ea6dfc61212b7dbd772bc0809ae7a.

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 [email protected] 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 https://github.com/mattn/go-sqlite3/issues/607#issuecomment-408347338, 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 <[email protected]>
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]

mattn's, as far as I know GJRTimmer never fixed the issue I mentioned above

[alkemir]

Can you provide a minimal example? I am unable to reproduce with

import (
	"database/sql"
	"log"
	"os"

	_ "github.com/mattn/go-sqlite3"
)

func main() {
	os.Remove("./foo.db")

	path := "./foo.db"

	db, err := sql.Open("sqlite3", "file:"+path+"?cache=shared&_journal_mode=WAL")
	if err != nil {
		log.Fatal(err)
	}
	db.SetMaxOpenConns(1)
	defer db.Close()

	sqlStmt := `
	create table foo (id integer not null primary key, name text);
	delete from foo;
	`
	_, err = db.Exec(sqlStmt)
	if err != nil {
		log.Printf("%q: %s\n", err, sqlStmt)
		return
	}
}

[frioux]

package main

import (
        "database/sql"
        "fmt"
        "log"
        "os"

        _ "github.com/mattn/go-sqlite3"
)

func main() {
        if len(os.Args) != 2 {
                fmt.Fprintf(os.Stderr, "usage: %s <file.db>\n", os.Args[0])
                os.Exit(1)
        }
        path := os.Args[1]

        db, err := sql.Open("sqlite3", "file:"+path+"?cache=shared&_journal_mode=WAL")
        if err != nil {
                log.Fatal(err)
        }
        db.SetMaxOpenConns(1)
        defer db.Close()

        sqlStmt := ` select 1 `
        _, err = db.Exec(sqlStmt)
        if err != nil {
                log.Printf("%q: %s\n", err, sqlStmt)
                return
        }
}

Pass it the firefox cookies.sqlite file, eg on my laptop it's /home/frew/.mozilla/firefox/qxp4lwy0.default/cookies.sqlite

[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: https://github.com/mattn/go-sqlite3/pull/747