This commit adds the SQLiteConn.FileControlInt() method which calls the
underlying sqlite3_file_control() function with an int argument. This can
be used for low-level operations on SQLite databases such as persisting
the WAL file after database close.
The busy_timeout pragma was added in sqlite 3.7.15 as an alternative
to calling sqlite3_busy_timeout directly:
https://sqlite.org/pragma.html#pragma_busy_timeout
While there's no functional change here, using the pragma does align
setting busy_timeout with other settings and removes the special case
for calling sqlite3_busy_timeout directly.
This can be used like in the test; I wrote a little wrapper around
sql.DB which uses this, and allows concurrent reads but just one single
write. This is perhaps a better generic "table locked"-solution than
setting the connections to 1 and/or cache=shared (although even better
would be to design your app in such a way that this doesn't happpen in
the first place, but even then a little seat belt isn't a bad thing).
The parsing adds about 0.1ms to 0.2ms of overhead in the wrapper, which
isn't too bad (and it caches the results, so only needs to do this
once).
At any rate, I can't really access functions from sqlite3-binding.c from
my application, so expose it via SQLiteStmt.
Add a shortcut for PRAGMA cache_size; this is a pretty useful setting:
the default of -2000 (2M) is not especially high, and a lot of people
will probably want to increase this.
For example, while running a bunch of fairy expensive queries in
parallel:
With SetMaxOpenConns(1):
-2000: 5762ms
-20000: 4714ms
With SetMaxOpenConns(20):
-2000: 3067ms
-20000: 2532ms
Which isn't a bad performance boost for changing a single number.
This option was enabled by default in sqlite3 on 2014-07-03.
This setting does nothing. It can now be disabled with
SQLITE_DISABLE_FTS3_UNICODE. See the upstream commit:
https://sqlite.org/src/info/0cc0230ae9cfc976
I think this change was imported into this project with commit
ee9da4840d on 2015-06-12.
This "disables the use of compiler-specific built-in functions such
as __builtin_bswap32()" (from the SQLite docs) so this change might
produce slightly better code. My primary motivation, however, is that
the "default" configuration for SQLite, which is widely tested, does
not set this preprocessor macro.
From looking at Github issues, it appears this was added to avoid a
build error on Mac OS X 10.11, in 2017:
https://github.com/mattn/go-sqlite3/issues/386
There have been a number of changes to sqlite3 since we tried this
last. I think it would be worth trying to remove this setting again.
I found a machine running Mac OS X 10.11.6. It was able to build and
run the tests in this package with this change.
Mac OS X 10.11 is has not been supported by Apple since 2018
(currently Apple is releasing updates for Mac OS 10.13 and newer; 11
is the current release). However, Go 1.14 is supported, and it
requires Mac OS X 10.11 or newer: https://golang.org/doc/go1.14
Go 1.15 only supports Mac OS 10.12 and newer:
https://golang.org/doc/go1.15
* Fix "cannot start a transaction within a transaction" issue
[why]
If db.BeginTx(ctx, nil) context is cancelled too fast, "BEGIN" statement can be
completed inside DB, but we still try to cancel it with sqlite3_interrupt.
In such case we get context.Cancelled or context.DeadlineExceeded from exec(),
but operation really completed. Connection returned into pool, and returns "cannot
start a transaction within a transaction" error for next db.BeginTx() call.
[how]
Handle status code returned from cancelled operation.
[testing]
Added unit-test which reproduces issue.
* Reduce TestQueryRowContextCancelParallel concurrency
[why]
Tests times out in travis-ci when run with -race option.
* Allow unused named parameters
Try to bind all named parameters and ignore those not used.
* Allow "@" and "$" for named parameters
* Add tests for named parameters
Co-authored-by: Guido Berhoerster <guido+go-sqlite3@berhoerster.name>
* adding SystemErrno to Error, and fixing error logic when open fails
* fix for old versions of libsqlite3 that do not have sqlite3_system_errno defined
* fixing pre-processor logic
[why]
see https://github.com/mattn/go-sqlite3/issues/607
SQLite default journal mode is DELETE, but forcing it on open causes "database is locked"
if other connection exists with WAL mode, for example.
[how]
Don't set DELETE mode if not set in DSN explicitly.
[testing]
Run tests in my project where WAL mode is used.
[why]
Context cancellation goroutine is not in sync with Next() method lifetime.
It leads to sql.ErrNoRows instead of context.Canceled often (easy to reproduce).
It leads to interruption of next query executed on same connection (harder to reproduce).
[how]
Do query in goroutine, wait when interruption done.
[testing]
Add unit test that reproduces error cases.
As opposed to []byte arrays. This brings sqlite closer
in line with other dbs like postgres, allowing downstream
consumers to assume the scanned value is string across underlying
dbs.
See,
$ gometalinter --vendor --disable-all --enable=misspell ./...
sqlite3.go:1379:45⚠️ "succesfully" is a misspelling of "successfully" (misspell)
sqlite3.go:1390:30⚠️ "registerd" is a misspelling of "registered" (misspell)
sqlite3_func_crypt.go:16:27⚠️ "ceasar" is a misspelling of "caesar" (misspell)
sqlite3_func_crypt.go:43:59⚠️ "Ceasar" is a misspelling of "Caesar" (misspell)
sqlite3_opt_userauth_test.go:450:27⚠️ "succesful" is a misspelling of "successful" (misspell)
sqlite3_opt_userauth_test.go:456:27⚠️ "succesful" is a misspelling of "successful" (misspell)
Apparently the cgo typechecks get better on tip, so use C.int instead
of Go integers.
Build tip as part of the Travis build, so we can ensure that any
errors are resolved before they get released to a wider audience.
sql.Driver, sql.Conn, sql.Tx sql.Stmt, and sql.Rows are not interfaces.
Updated the comments to refer to the correct interfaces: driver.Driver,
driver.Conn, driver.Tx, driver.Stmt, and driver.Rows.
Because the closed property of the SQLiteRows's *SqliteStmt
was not guarded, it was causing an issue during context
cancellation.
be424d27ac/sqlite3.go (L1785-L1796)
When a statement is performing a query(), if it determines that
the context has been canceled, it will launch a goroutine that
closes the resulting driver.Rows if it's not already completed.
If the driver.Rows is not done (and the context has been canceled),
it will interrupt the connection and more importantly, perform
a rows.Close(). The method rows.Close() guards the closed bool with
a sync.Mutex to set it to true.
If a reader is reading from the SqliteRow, it will call Next()
and that performs this check:
be424d27ac/sqlite3.go (L1915-L1917)
Because this is not guarded, a data race ensues, and this was
actually caught by the Go race detector recently.
I didn't include a test case here because the fix seemed
straightforward enough and because race conditions are hard
to test for. It's been verified in another program that this
fixes the issue. If tests should be provided I'm more than
happy to do so.
Joshua Hull