Forum > Databases

weird sqlite error "database schema has changed"

<< < (2/4) > >>

rvk:

--- Quote from: Zvoni on May 16, 2022, 12:45:40 pm ---EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited

--- End quote ---
That's why I said I thought DDL statements like CREATE and DROP are done outside the transaction (and if they are in sqlite_master even before commit that is correct).

@tt, can you test this by beginning a transaction, create the table and then doing a rollback. Is the table after that still there?
If the table is still there, that's proof the CREATE is done outside the transaction directly on the database schema.

tt:

--- Quote from: Zvoni on May 16, 2022, 12:45:40 pm ---Just found something else: Do you use prepared Statements?
https://stackoverflow.com/questions/41347346/causes-of-sqlite-general-error-17-database-schema-has-changed

--- Quote ---Attempting to execute a prepared statement defined before the schema change will result in a SQLite General error: 17 database schema has changed error.
--- End quote ---

EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited

EDIT2: As far as i could find out: If you prepare a Statement, it kinda saves a "schema_version" internally against which the statement was prepared.
If you then change the schema (and Creating/dropping tables is changing the schema) you get a KABOOM, when trying to execute said statement

--- End quote ---


Sorry, I initially thought that this explanation was fine to me, but then I realized that I am still confused.

The call


--- Code: Pascal  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---    queries := connection.QueriesCreate(names, sqls, transaction); 
creates all the TSQLQueries described in statements before that, and puts them in a sort of map, to ease later usage and don't create the queries in the middle of the logic.

So yes, I create the queries in advance (even if I am not sure that this means using prepared statements).

But still, why if I create/drop the table using the the if not exists/if exists clauses I get a "database schema has changed" error while if I do not use the cite clauses I dont get any error?

All in all:
1. as the support table does not exists before I create it,  putting if not exists or nothing is equivalent
2. as the support table exists before I drop it, putting if exists or nothing is equivalent
3. And, in both cases:
3.1 I am using statements somehow created before
3.2 I am changing the schema

So I still don't undertstand what is wrong with if not exists/if exists clauses.

Anyway that for the effort in helping me.

tt:

--- Quote from: rvk on May 16, 2022, 01:16:03 pm ---
--- Quote from: Zvoni on May 16, 2022, 12:45:40 pm ---EDIT: I was in error. A CREATED table is in sqlite_master even if Transaction is not commited

--- End quote ---
That's why I said I thought DDL statements like CREATE and DROP are done outside the transaction (and if they are in sqlite_master even before commit that is correct).

@tt, can you test this by beginning a transaction, create the table and then doing a rollback. Is the table after that still there?
If the table is still there, that's proof the CREATE is done outside the transaction directly on the database schema.

--- End quote ---

Thank you rvk.
I have tried doing that before writing the post, from within the sqlite3 client:



--- Code: Text  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> begin transaction;sqlite> create table if not exists XXXX (id integer);sqlite> .qtt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> .schemasqlite> .q   tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> begin transaction;sqlite> create table if not exists XXXX (id integer);sqlite> commit;sqlite> .qtt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> .schemaCREATE TABLE XXXX (id integer);sqlite> .q 
As you can see, if I start the transaction, unless I do the commit before exiting, table is not created.

rvk:
Ok. Then somehow the CREATE is rolled back in the transaction.
Not as I thought...


--- Code: ---SQLite version 3.38.5 2022-05-06 15:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> begin transaction;
sqlite> .schema
sqlite> create table ABC (id integer);
sqlite> .schema
CREATE TABLE ABC (id integer);
sqlite> rollback;
sqlite> .schema
sqlite> .q
--- End code ---

Zvoni:

--- Quote from: tt on May 16, 2022, 01:35:53 pm ---Thank you rvk.
I have tried doing that before writing the post, from within the sqlite3 client:



--- Code: Text  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> begin transaction;sqlite> create table if not exists XXXX (id integer);sqlite> .qtt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> .schemasqlite> .q   tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> begin transaction;sqlite> create table if not exists XXXX (id integer);sqlite> commit;sqlite> .qtt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> .schemaCREATE TABLE XXXX (id integer);sqlite> .q 
As you can see, if I start the transaction, unless I do the commit before exiting, table is not created.

--- End quote ---
Not correct.
If you start a Transaction, but quit before committing you execute an implicit Rollback.
As long as you're still inside your transaction, the table is in sqlite_master
Try:

--- Code: Text  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---tt@debian:~$ rm databasetest tt@debian:~$ sqlite3 databasetestSQLite version 3.27.2 2019-02-25 16:06:06Enter ".help" for usage hints.sqlite> begin transaction;sqlite> create table if not exists XXXX (id integer);sqlite> .schema  //--> THIS ONE!sqlite> SELECT * FROM sqlite_master WHERE name='XXX'  //--> OR THIS ONEsqlite> PRAGMA table_info('XXX')  //--> OR THIS ONEsqlite> .q 
EDIT: But i agree with tt: The error is not making any sense. IF (NOT) EXIST should work irrespective if the Transaction has been committed or not.
Just did a quick test to CREATE TABLE IF NOT EXISTS inside a Transaction: Works without error

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version