weird sqlite error "database schema has changed"

Forum > Databases

(1/4) > >>

Чебурашка:
Hello everyone,

I am observing a behaviour I do not understand in sqlite, I post here to know if somebody has similar situations, maybe is a intrinsic sqlite error not related to FP.

I need to create a table to temporary store the result of a query and I DO NOT use TEMP tables (and I won't use them).

I follow this procedure

1. Open a connection C
2. Open a transaction T on C
3. (using C and T) create the table TB to persist the result of the query
4. (using C and T) Execute a insert into ... select in order to populate TB
5. (using C and T) Execute a query to read data from TB
6. Drop table TB
7. Commit T
8. Close C

The problem is the following:

Case1: if in 3 and 6 I use "CREATE IF NOT EXISTS"/"DROP IF EXISTS" I get a "database schema has changed error" when I finally drop the table in step 6

Case2: if in 3 and 6 I use the simple "CREATE"/"DROP" I do not get any error.

Why IF NOT EXISTS/IF EXIST generate such an error?

--- 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";}};} ---function LoadWorkCyclesAndDiametersInInterval( logger: TLogger; aliasName: string; machineId: Integer; startDate: TDateTime; endDate: TDateTime; workCyclesAndDiametersInIntervalDiameters: TObjectList; workCyclesAndDiametersInIntervalWorkCycles: TObjectList; workCyclesAndDiametersInInterval: TObjectList; out errorMessage: string): Boolean; procedure DoLoadWorkCyclesAndDiametersInInterval(connection: TMEPConnection; transaction: TSQLTransaction); var names, sqls: TMEPDatabaseIOStrings; queries: TQueries; q: TSQLQuery; summary_item: TWorkCycleAndDiameter; workcycle: TWorkCycle; diameter: TDiameter; begin names := TMEPDatabaseIOStrings.Create(); names.Add('ctt'); names.Add('dtt'); names.Add('ttp'); names.Add('workcycles'); names.Add('diameters'); names.Add('summary'); sqls := TMEPDatabaseIOStrings.Create(); sqls.Add('create table if not exists LWCADII' + ' (' + ' customerNo varchar(255),' + ' scheduleNo varchar(255),' + ' jobOrderNo varchar(255),' + ' min_start_date datetime, ' + ' max_end_date datetime' + ' )'); sqls.Add('drop table if exists LWCADII'); sqls.Add(' insert into LWCADII (customerNo, scheduleNo, jobOrderNo, min_start_date, max_end_date)' + ' select ' + ' customerNo ' + ' , scheduleNo ' + ' , jobOrderNo ' + ' , min(startDate)' // it might happen by mistake that same c/s/j is used for multiple diameter + ' , max(endDate) ' + ' from ' + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_ProductionBatches].Name + ' where' + ' refMachine = :refMachine' + ' and (:startDate <= startDate)' + ' and (endDate < :endDate)' + ' group by customerNo, scheduleNo, jobOrderNo' + ' order by startDate, customerNo, scheduleNo, jobOrderNo'); sqls.Add('select ' + ' customerNo ' + ', scheduleNo ' + ', jobOrderNo ' + ', min_start_date ' // it might happen by mistake that same c/s/j is used for multiple diameter + ', max_end_date ' + ' from LWCADII'); sqls.Add('select distinct ' + ' materialDiameter ' + ' from ' + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_ProductionBatches].Name + ' where' + ' refMachine = :refMachine' + ' and (:startDate <= startDate)' + ' and (endDate < :endDate)' + ' order by materialDiameter'); sqls.Add('select ' + ' customerNo ' + ', scheduleNo ' + ', jobOrderNo ' + ', materialDiameter ' + ', sum(totalPiecesExpected) as actual_expected_count ' + ', sum(totalPiecesProduced) as actual_produced_count ' + ', sum(totalWeight) as actual_weight ' + ', sum(totalEnergyWh) as total_energy_Wh ' + ', sum(totalElapsedSeconds) as total_elapsed_seconds ' + ', sum(totalPiecesProduced * productLength) as total_length_mm ' + ' from ' + DBObjectDescriptors[TDBObjectsEnum.dbo_T1103_ReportingCache_ProductionBatches].Name + ' where' + ' refMachine = :refMachine' + ' and (:startDate <= startDate)' + ' and (endDate < :endDate)' + ' group by customerNo, scheduleNo, jobOrderNo, materialDiameter' + ' order by customerNo, scheduleNo, jobOrderNo, materialDiameter'); queries := connection.QueriesCreate(names, sqls, transaction); try try q := queries.Queries['dtt']; // make sure table is dropped q.ExecSQL(); except // if did not exist then ok end; q := queries.Queries['ctt']; // make sure table is created q.ExecSQL(); q := queries.Queries['ttp']; // populated temporary table q.ParamByName('refMachine').AsInteger := machineId; q.ParamByName('startDate').AsDateTime := startDate; q.ParamByName('endDate').AsDateTime := endDate ; q.ExecSQL(); {%REGION 'Load workcycles'} q := queries.Queries['workcycles']; q.Open(); while(not q.EOF) do begin workcycle := TWorkCycle.Create( q.FieldByName('customerNo' ).AsString , q.FieldByName('scheduleNo' ).AsString , q.FieldByName('jobOrderNo' ).AsString , q.FieldByName('min_start_date' ).AsDateTime , q.FieldByName('max_end_date' ).AsDateTime); workCyclesAndDiametersInIntervalWorkCycles.Add(workcycle); q.Next(); end; q.Close(); {%ENDREGION} {%REGION 'Load diameters'} q := queries.Queries['diameters']; q.ParamByName('refMachine').AsInteger := machineId; q.ParamByName('startDate').AsDateTime := startDate; q.ParamByName('endDate').AsDateTime := endDate ; q.Open(); while(not q.EOF) do begin diameter := TDiameter.Create(q.FieldByName('materialDiameter').AsFloat); workCyclesAndDiametersInIntervalDiameters.Add(diameter); q.Next(); end; q.Close(); {%ENDREGION} {%REGION 'Load summary'} q := queries.Queries['summary']; q.ParamByName('refMachine').AsInteger := machineId; q.ParamByName('startDate').AsDateTime := startDate; q.ParamByName('endDate').AsDateTime := endDate ; q.Open(); while(not q.EOF) do begin summary_item := TWorkCycleAndDiameter.Create( q.FieldByName('customerNo').AsString , q.FieldByName('scheduleNo').AsString , q.FieldByName('jobOrderNo').AsString , q.FieldByName('materialDiameter').AsFloat , q.FieldByName('actual_produced_count' ).AsInteger , q.FieldByName('actual_weight' ).AsFloat , q.FieldByName('total_energy_Wh' ).AsFloat , q.FieldByName('total_length_mm' ).AsFloat / 1000 , q.FieldByName('total_elapsed_seconds' ).AsFloat); workCyclesAndDiametersInInterval.Add(summary_item); q.Next(); end; q.Close(); {%ENDREGION} q := queries.Queries['dtt']; // make sure table is deleted befor exit q.ExecSQL(); finally FreeAndNil(names); FreeAndNil(sqls); FreeAndNil(queries); end; end; var connection: TMEPConnection; transaction: TSQLTransaction;begin Result := False; errorMessage := ''; connection := TMEPConnection.Create(); connection.AliasName := aliasName; try connection.ConnectionOpen(); transaction := connection.TransactionCreate(); try transaction.StartTransaction(); try DoLoadWorkCyclesAndDiametersInInterval(connection, transaction); transaction.Commit(); Result := True; except on E: Exception do begin transaction.Rollback(); errorMessage := logger.LogExceptionErrorS(E, 'Unexpected error while loading work cycles and diameters in interval'); end; end; finally FreeAndNil(transaction); end; finally FreeAndNil(connection); end;end;

rvk:
I'm not sure about SQLite but aren't DDL statements that change the database structure (like CREATE and DROP TABLE) executed OUTSIDE the transaction? (for some databases they are).

You could try to move 3 (create) to before 2 (opening the transaction) and 6 (drop) to after 7 (commit).

So:
1. Open a connection C
2. (using C and T) create the table TB to persist the result of the query
3. Open a transaction T on C
4. (using C and T) Execute a insert into ... select in order to populate TB
5. (using C and T) Execute a query to read data from TB
6. Commit T
7. Drop table TB
8. Close C

Does that work? (not sure if the components can execute sql without an active transaction though).

Zvoni:

--- Quote from: rvk on May 16, 2022, 10:59:08 am ---I'm not sure about SQLite but aren't DDL statements that change the database structure (like CREATE and DROP TABLE) executed OUTSIDE the transaction? (for some databases they are).
--- End quote ---
No.
For SQLite it's legal to wrap CREATE TABLE/DROP TABLE in a Transaction
Meaning: As long as the Transaction is not Committed, a "created" Table doesn't "exist" for the DROP and vice versa

At a guess: IF (NOT) EXISTS is basically a SELECT on "sqlite_master", and since the Transaction is not committed.....
Would have to check source-code of SQLite

Чебурашка:

--- Quote from: Zvoni on May 16, 2022, 11:58:32 am ---
--- Quote from: rvk on May 16, 2022, 10:59:08 am ---I'm not sure about SQLite but aren't DDL statements that change the database structure (like CREATE and DROP TABLE) executed OUTSIDE the transaction? (for some databases they are).
--- End quote ---
No.
For SQLite it's legal to wrap CREATE TABLE/DROP TABLE in a Transaction
Meaning: As long as the Transaction is not Committed, a "created" Table doesn't "exist" for the DROP and vice versa

At a guess: IF (NOT) EXISTS is basically a SELECT on "sqlite_master", and since the Transaction is not committed.....
Would have to check source-code of SQLite

--- End quote ---

Thanks.
Just to add few bits more: opening the command line sqlite3 it does not show errors (and I also checked in the middle of create and drop, the table was actually created).

tt@debian:~$ sqlite3 databasetest
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> begin transaction;
sqlite> create table if not exists XXXX (id integer);
sqlite> drop table if exists XXXX;
sqlite> commit;
sqlite> .q

Zvoni:
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

Navigation

[0] Message Index

[#] Next page