SQLite Forum

Database Schema Changed Error
Login

Database Schema Changed Error

(1) By anonymous on 2020-05-28 09:51:40 [link] [source]

We are using System.Data.SQLite dll with version 1.0.111.0 for one of the database operation.

In this operation, we are creating a new sqlite connection with following properties.

var connectionStringBuilder = new SQLiteConnectionStringBuilder { SyncMode = SynchronizationModes.Normal, Version = 3, DataSource = fileInfo.FullName, ReadOnly = false, };

Once we create a new instance of connection, opening the connection and creating a simple table with following query within a transaction.

CREATE TABLE IF NOT EXISTS TestTable( Guid Blob, Increment INTEGER NOT NULL )

Successfully able to create the table.

In our next business logic, we need to query this table to read some data and this call is happening in a another thread using a same connection which is already created during creation of table. This operation is still a synchronous operation.

SELECT Increment FROM TestTable WHERE Guid = @Guid

Problem Statement:

Whenever while executing the above mentioned select statement we are getting following error in Application Console:

"SQLite error <17>: Statement aborts at 9: [SELECT Increment FROM TestTable WHERE Guid = @Guid] Database schema has changed"

Our Analysis on this issue: 1. Whenever this error logs in our console, not getting SQLite Exception or any base exception(verified through our logging mechanism). 2. This error not causing any issues to our workflows. Hence not seeing any side effects or application crash. 3. This error not occurring when we do the SELECT operation second time. Hence error is coming during first SELECT operation only. 4. We are able to see the same error with earlier version of System.Data.SQLite(1.0.102.0)

Workaround we found to avoid this error in console: 1. Create new SQLite connection during SELECT operation. Hence avoiding using the same connection created during table creation.

Our Questions: 1. Why we are seeing this error in Console? 2. Is there any retrying mechanism to prepare the SQL statement in SQLite whenever this error occurs? That's the reason still our application behaving as per the expectation. 3. How critical this error?Do we need to ignore this error?User feels bad to see this error in his console.

(2) By Simon Slavin (slavin) on 2020-05-29 04:13:05 in reply to 1 [link] [source]

A transaction is related to a connection. If you have two threads sharing a connection, and one starts a transaction, that transaction includes SQL commands from both threads.

So if both your threads are executing simultaneously, and the timing of the two threads is not coordinated, you have a complicated and unpredictable situation where one thread might COMMIT a transaction at the same time as another thread is executing SQL commands which are inside it.

One cure is to design your own multiplexing to make sure this does not happen. Another is for each thread to open its own connection.

(3) By Clemens Ladisch (cladisch) on 2020-05-30 11:59:20 in reply to 1 [source]

This is not an error but a warning. The first execution of the statement will fail (this is why it says "error"), but SQLite will then automatically re-prepare the statement with the new schema and re-execute it.

(4.1) By Keith Medcalf (kmedcalf) on 2020-05-31 05:31:47 edited from 4.0 in reply to 3 [link] [source]

sqlite3_exec will only automatically re-prepare the statement if it was originally prepared using sqlite3_prepare_v2 or sqlite3_prepare_v3. Statements prepared using sqlite3_prepare interface do not store the statement text and therefore cannot be automatically re-prepared.

sqlite3_exec may still return a schema changed error if it is unable to re-prepare the statement within the number of re-tries that have been configured.

https://sqlite.org/c3ref/prepare.html

Nevertheless, the reprepare operation will be logged in the log even if it is carried out transparently and no error is returned to the user -- and I think this is the complaint -- the log is logging that a re-prepare was required even though no error was presented to the application.

This is logged to the sqlite log even if it was handled internally because the purpose of the log is to facilitate debugging. And there are cases where knowing that the database engine did a re-prepare of a statement even though no schema changes occurred my be a useful debugging aid. If a schema change did occur then one should expect to see this message.

(5) By anonymous on 2020-06-16 04:48:00 in reply to 2 [link] [source]

Hi Simon,

Thank you very much for your quick response to my query. Even though two threads are trying to do DB transactions, they are coordinated. And also we are not seeing any deviations in our business logic with this error.

So we have come to a conclusion like internally SQLite is re-preparing the statement and able to execute the query.

(6) By anonymous on 2020-06-16 04:49:39 in reply to 3 [link] [source]

Thank you Clemens for response.

Yes we are agreeing to your point of re-preparing the statement, because this error not causing any side effects to our business logic.

(7) By anonymous on 2020-06-16 04:50:39 in reply to 4.1 [link] [source]

Thank You Keith for response.

(8) By Keith Medcalf (kmedcalf) on 2020-06-16 15:09:47 in reply to 6 [link] [source]

There are other cases in which a re-prepare will happen on calling sqlite3_exec even if the database schema has not changed if you are using SQLITE_ENABLE_STAT4.

Consider the following simple example:

create table x(data text collate nocase unique);
select data from x where data like ?;

When you sqlite3_prepare_v2 the select statement, the query planner has absolutely no idea whether to do a full-scan of data, or a partial-index scan of data because this decision depends on the value of the parameter. So it generates a query plan which will always work, which is a full-scan of data.

However, when SQLITE_ENABLE_STAT4 is in effect, then the statement will be re-prepared when sqlite3_exec is called in order that the "value" of the parameter may be examined, in this case to isolate whether these is a fixed prefix to the like parameter that can be used to limit the scan and if so, will regenerate the statement plan to now use a partial-index scan of data rather than a full-scan of data.

Since SQLITE_ENABLE_STAT4 collects additional histogram (selectivity) statistics, an additional re-prepare may occur at sqlite3_exec time for any statement in which an "indexed" relation to a parameter is used because the planner might not be able to determine the most efficient plan until after the parameters to the query are known, and this does not happen until sqlite3_exec is executed for the first time after the parameters are bound.

This behaviour is why the use of SQLITE_ENABLE_STAT4 may violate the Query Plan Stability guarantee since it enables re-evaluation of the plan after the bindings are set based on the values of the parameters, and not merely based on the query itself, the schema, and the statistics.