Hello Jay, here is information update.
I find 2 new things.
1. This issue happens even two conns running in same thread.
2. And it is more important ! This issue only happens in WAL mode !
As I know, databases with journal mode use a file change counter to update
schema while databases with WAL mode use wal-index to do this.
I'm using macOS with the system builtin sqlite framwork with 3.8.10.2. I think
it should be a bug in wal-index.
Here is the very simple code to reproduce this problem.
```
sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
sqlite3* handle1;
rc = sqlite3_open(path, handle1);
assert(rc==0);
// rc = sqlite3_exec(handle1, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
// assert(rc==0);
sqlite3* handle2;
rc = sqlite3_open(path, handle2);
assert(rc==0);
// rc = sqlite3_exec(handle2, "PRAGMA journal_mode=WAL", NULL, NULL, NULL);
// assert(rc==0);
rc = sqlite3_exec(handle1, "CREATE TABLE test1 (i INTEGER)", NULL, NULL, NULL);
assert(rc==0);
//The sql below will return an error with 'no such table: test1' in WAL, but
succeeds in journal mode.
rc = sqlite3_exec(handle2, "SELECT * FROM test1", NULL, NULL, NULL);
assert(rc==0);
```
Original Message
Sender:Jay kreibich...@kreibi.ch
Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
Date:Friday, Aug 18, 2017 19:46
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !
On Aug 18, 2017, at 4:04 AM, sanhua.zh sanhua...@foxmail.com wrote: I am using
SQLite in multi-thread mode, which means that different threads using different
SQLite connection. And now I find an issue that the results of SQLite C
interface returned is expired while the schema of database is changed. The
following sample runs in different threads, but I force them to
runsequentially. Thread 1: 1. Conn A: Open, PRAGMA journal_mode=WAL Thread
2: 2.ConnB: Open, PRAGMA journal_mode=WAL Thread 1: 3.ConnA: CREATE TABLE
sample (i INTEGER); Thread 2: 4.ConnB: PRAGMA table_info('sample')
Firstly, both thread 1 and 2 do initialization for their own conn, which is to
read to schema into memory. Then, Conn A creates a table with Conn A.
Finally, `PRAGMA table_info(sample)` is called in thread 2 with Conn B and it
returns nothing. The same thing could happen if I change the step 4 to
`sqlite3_table_column_metadata` or some other interfaces. I do know the
reason should be the expired in-memory-schema. But I find no docs about which
interface will or will not update the schema and what should I do while I call
a non-update-schema interface ? See the bottom of the sqlite3_prepare*() docs:
https://www.sqlite.org/c3ref/prepare.html And the SQLITE_SCHEMA docs:
https://www.sqlite.org/rescode.html#schema As the docs say, make sure you’re
using sqlite3_prepare*_v2() or _v3(). If a statement is prepared with these
newer versions, it will handle most expiration situations automatically by
re-preparing the statement. Generally speaking, if you do get an SQLITE_SCHEMA
error, you need to rollback the current transaction, re-prepare the statements,
and try again. -j -- Jay A. Kreibich J A Y @ K R E I B I.C H "Intelligence is
like underwear: it is important that you have it, but showing it to the wrong
people has the tendency to make them feel uncomfortable." -- Angela Johnson
_______________________________________________ sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
