Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

And which commands will check whether the schema is expired.
As far as I know, SELECT command will but PRAGMA table_info will not do this.


Original Message
Sender:Simon slavinslav...@bigfraud.org
Recipient:SQLite mailing listsqlite-us...@mailinglists.sqlite.org
Date:Monday, Aug 21, 2017 11:47
Subject:Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !


On 21 Aug 2017, at 3:22am, sanhua.zh sanhua...@foxmail.com wrote:  If so, who 
or which doc can tell me that which SQL will or will not update the schema ? 
Commands with CREATE, DROP, or ALTER update schema. Simon. 
___ 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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Simon Slavin]

On 21 Aug 2017, at 3:22am, sanhua.zh  wrote:

> If so, who or which doc can tell me that which SQL will or will not update 
> the schema ?

Commands with CREATE, DROP, or ALTER update schema.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

Firstly, I use `PRAGMA table_info('sample')` in my sample case, which means 
that it also calls `sqlite3_prepare_v2` but do not re-prepare. Maybe it does 
not contain the specific OP checking the schema.


Secondly, it's hard to know when the schema is changed in multi-conns 
implementation. So as telling them to re-prepare.


So, As your word "comthis situation would be a problem" said, is it that I 
should not use the non-update-schema-operation in multi-conns implementation ?
If so, who or which doc can tell me that which SQL will or will not update the 
schema ?


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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Simon Slavin]

On 18 Aug 2017, at 5:39pm, Jens Alfke  wrote:

> Actually, "multi-threaded mode" usually means using a _single_ connection on 
> multiple threads.
> 
> What you're doing — a separate connection for each thread — is effectively* 
> the same as running multiple single-threaded processes, i.e. it doesn't 
> involve any concurrency within SQLite.

We need a grid, with number of connections along the top and number of threads 
down the side.  Might be useful in the documentation.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jens Alfke]

> On Aug 18, 2017, at 2:04 AM, sanhua.zh  wrote:
> 
> I am using SQLite in multi-thread mode, which means that different threads 
> using different SQLite connection.

Actually, "multi-threaded mode" usually means using a _single_ connection on 
multiple threads.

What you're doing — a separate connection for each thread — is effectively* the 
same as running multiple single-threaded processes, i.e. it doesn't involve any 
concurrency within SQLite.

—Jens

* There are slight differences, because of some global state in the SQLite 
library (like logging callbacks), but that's irrelevant to what's going on here.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jay Kreibich]

On Aug 18, 2017, at 7:37 AM, Clemens Ladisch  wrote:

> Jay Kreibich wrote:
>> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:
>>> sanhua.zh wrote:
 1. Conn A: Open, PRAGMA journal_mode=WAL
 2.ConnB: Open, PRAGMA journal_mode=WAL
 3.ConnA: CREATE TABLE sample (i INTEGER);
 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.
 
 I do know the reason should be the expired in-memory-schema.
>>> 
>>> No, SQLite automatically detects schema changes.
>> 
>> …but only automatically re-prepares the expired statements if the statement
>> was originally prepared using sqlite3_prepare*_v2 or _v3.
> 
> The word "expired" in the OP is misleading; this problem has nothing to do
> with schema expiration (that would result in an SQLITE_SCHEMA error).

Ah…. OK yes.

 -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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Clemens Ladisch]

Jay Kreibich wrote:
> On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:
>> sanhua.zh wrote:
>>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>>> 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.
>>>
>>> I do know the reason should be the expired in-memory-schema.
>>
>> No, SQLite automatically detects schema changes.
>
> …but only automatically re-prepares the expired statements if the statement
> was originally prepared using sqlite3_prepare*_v2 or _v3.

The word "expired" in the OP is misleading; this problem has nothing to do
with schema expiration (that would result in an SQLITE_SCHEMA error).


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jay Kreibich]

On Aug 18, 2017, at 4:04 AM, sanhua.zh  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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Jay Kreibich]

On Aug 18, 2017, at 5:33 AM, Clemens Ladisch  wrote:

> sanhua.zh wrote:
>> 1. Conn A: Open, PRAGMA journal_mode=WAL
>> 2.ConnB: Open, PRAGMA journal_mode=WAL
>> 3.ConnA: CREATE TABLE sample (i INTEGER);
>> 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.
>> 
>> I do know the reason should be the expired in-memory-schema.
> 
> No, SQLite automatically detects schema changes.

…but only automatically re-prepares the expired statements if the statement
was originally prepared using sqlite3_prepare*_v2 or _v3.


> It's likely that the second connection started its transaction before
> the first connection committed its own, so it still sees the old state
> of the database.

Unlike most RDBMS environments, SQLite handles DDL as part of
normal transactions.  I don’t think this situation would be a problem,
and would be handled by the normal locking mechanisms.

  -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

Re: [sqlite] SQLite's Results Are Expired While Schema Is Changed !

[Clemens Ladisch]

sanhua.zh wrote:
> 1. Conn A: Open, PRAGMA journal_mode=WAL
> 2.ConnB: Open, PRAGMA journal_mode=WAL
> 3.ConnA: CREATE TABLE sample (i INTEGER);
> 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.
>
> I do know the reason should be the expired in-memory-schema.

No, SQLite automatically detects schema changes.

It's likely that the second connection started its transaction before
the first connection committed its own, so it still sees the old state
of the database.


Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite's Results Are Expired While Schema Is Changed !

[sanhua.zh]

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 ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users