New table logged as "no such table" during sqlite3_prepare_v2

(1) By Joe M (jmonaco) on 2022-10-27 16:12:14 [source]

Howdy,

We are running sqlite version 3.34.1

We have an application with many sqlite3 connections to the same database. Connections use a private cache and read connections use SQLITE_OPEN_FULLMUTEX. When a transaction commits, the read connections flush their ongoing read transactions to ensure they do not read stale data after the transaction commit.

We also have register a logger callback with the sqlite3_log() function: https://www.sqlite.org/c3ref/log.html

Shortly after a table is created, we see a read connection attempt to prepare a statement using that table. The logger we registered logs the following message during the prepare():

 sqlite_logger: SQLite Error: (1) no such table: new_table in "SELECT ..."

Additional logging shows this error log is coming from sqlite3_prepare_v2():

  0x7f90c2d3cfe0: sqlite3_prepare_v2+80
  0x7f90c5439651: prepareStmt()

Further, we have bug catching code to log if sqlite3_prepare_v2 returns an error other than SQLITE_OK. We do not see the bug catching code firing in this case.

So, we have the following: A new table is created, a read connection attempts to prepare a statement with the table, the logger reports the table doesn't exist, and sqlite3_prepare_v2() returns SQLITE_OK. What's going on here?

Thanks!

(2) By Simon Slavin (slavin) on 2022-10-27 16:20:26 in reply to 1 [link] [source]

Temporarily change SQLITE_OPEN_SHAREDCACHE to SQLITE_OPEN_PRIVATECACHE, just for testing purposes. Does this fix the problem ?

(3) By Joe M (jmonaco) on 2022-10-27 16:32:02 in reply to 2 [link] [source]

We are already using SQLITE_OPEN_PRIVATECACHE. Are you suggesting to change it to SQLITE_OPEN_SHAREDCACHE?

(4) By Keith Medcalf (kmedcalf) on 2022-10-27 17:05:33 in reply to 1 [link] [source]

So what you are complaining about is the fact that a message was logged that the table does not exist, but yet the statement prepared properly and when executed accesses the table (which was created although there was a debuging message logged that it did not).

Is this a correct summary of the situation?

Is there a message logged that the schema changed?

You will note that sqlite_prepare_v2 (and later) will, unless you have specified not to do so, automatically re-prepare statements on schema changes and that the only way you can determine (other than by noting to oneself -- Aha! I changed the schema!) that the schema changed is because the "debug logging interface" notices that the schema changed and issues you some debugging messages to that effect.

(5) By Keith Medcalf (kmedcalf) on 2022-10-27 17:10:56 in reply to 4 [link] [source]

Note also that 3.34.0 is rather old. Mayhaps the debug logging messages changed betwixt then and now.

(6) By Joe M (jmonaco) on 2022-10-27 17:25:38 in reply to 4 [link] [source]

Yeah that's a correct summary of the issue. It's certainly a minor/low priority problem. I simply want to try to understand the message better since it caused some initial confusion among our team.

There are no messages about the schema being changed.

Also, we are working on upgrading to 3.39.2 soon. I will check by the end of the week if this log still shows up in that version.

(7) By Gunter Hick (gunter_hick) on 2022-10-28 06:19:39 in reply to 6 [link] [source]

If a connection A creates a new table and connection B wants to access it, connection A has to commit its transaction before connection B begins a new transaction. I take it this is what you mean with "flush their ongoing read transactions".

In that case, it seems that sqlite3_prepare_v2() is detecting (and logging) that the new table is not (yet) in its internal schema cache, but can recover the situation be re-reading the schema, which now contains the new table, and thus successfully prepares the statement.