Create an index inside transaction

BufMaster · Post by **BufMaster** » 12.11.2020, 15:50

Hi,

I try to create an index inside a transaction, but I don't how Zeos+SQLite fail.

After reducing my code to MVP test, I ask your help to find where I've made a mistake or if it's a bug inside Zeos or SQLite ?

I use Lazarus 2.0.10 and the latest Zeos (7.2.8 ) and SQLite (3.33.0) on Windows 10 64bit.

Code: Select all

const
  dbPath='C:\...\data.sqlite';
  dllPath='C:\...';
  logPath='C:\...\sql.txt';
procedure TForm1.Button1Click(Sender: TObject);
var
   db : TZConnection;
   SqlSpy : TZSQLMonitor;
   sql : TZQuery;
begin
  db:=TZConnection.Create(nil);
  with db do
  begin
    Protocol  := 'sqlite-3';
    HostName  := '';
    Database  := dbPath;
    User      := '';
    Password  := '';
    TransactIsolationLevel:=tiReadCommitted;
    ReadOnly  := False;
    AutoCommit:= True;
    LibraryLocation:=dllPath;
    with Properties do
    begin
      Clear;
      Add('foreign_keys=1');
      Add('ExtendedErrorMessage=1');
    end;
  end;

  SqlSpy := TZSQLMonitor.Create(nil);
  SqlSpy.FileName:=logPath;
  SqlSpy.AutoSave:=True;
  SqlSpy.Active:=True;

  db.Connect;

  sql:=TZQuery.Create(nil);
  sql.Connection:=db;

  db.StartTransaction;
  sql.SQL.Clear;
  sql.SQL.Add('CREATE TABLE t0(c0);');
  sql.ExecSQL;
  db.Commit;

  db.StartTransaction; // remove to work
  sql.SQL.Clear;
  sql.SQL.Add('CREATE INDEX i0 ON t0(c0);');
  sql.ExecSQL;
  db.Commit;           // remove to work

  sql.Free;

  db.Disconnect;

  db.Free;
  SqlSpy.Free;
end;

Above code fail with and error which is not an error

Code: Select all

2020-11-12 15:46:13 cat: Connect, proto: sqlite-3, msg: CONNECT TO "C:\...\data.sqlite" AS USER ""
2020-11-12 15:46:13 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA cache_size = 10000
2020-11-12 15:46:13 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-11-12 15:46:13 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA show_datatypes = ON
2020-11-12 15:46:13 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-11-12 15:46:13 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA foreign_keys = 1
2020-11-12 15:46:13 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-11-12 15:46:13 cat: Transaction, proto: sqlite-3, msg: BEGIN TRANSACTION
2020-11-12 15:46:13 cat: Prepare, proto: sqlite-3, msg: Statement 2 : CREATE TABLE t0(c0);

2020-11-12 15:46:13 cat: Execute prepared, proto: sqlite-3, msg: Statement 2
2020-11-12 15:46:13 cat: Transaction, proto: sqlite-3, msg: COMMIT TRANSACTION
2020-11-12 15:46:13 cat: Transaction, proto: sqlite-3, msg: BEGIN TRANSACTION
2020-11-12 15:46:13 cat: Transaction, proto: sqlite-3, msg: ROLLBACK TRANSACTION
2020-11-12 15:46:13 cat: Transaction, proto: sqlite-3, msg: BEGIN TRANSACTION
2020-11-12 15:46:13 cat: Prepare, proto: sqlite-3, msg: Statement 3 : CREATE INDEX i0 ON t0(c0);

2020-11-12 15:46:13 cat: Execute prepared, proto: sqlite-3, msg: Statement 3
2020-11-12 15:46:13 cat: Execute, proto: sqlite-3, msg: COMMIT TRANSACTION, errcode: 1, error: Error: SQL logic error
Message: not an error
2020-11-12 15:46:18 cat: Transaction, proto: sqlite-3, msg: COMMIT TRANSACTION

If I remove transaction around index creation, it works !

Code: Select all

2020-11-12 15:01:16 cat: Connect, proto: sqlite-3, msg: CONNECT TO "C:\...\data.sqlite" AS USER ""
2020-11-12 15:01:16 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA cache_size = 10000
2020-11-12 15:01:16 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-11-12 15:01:16 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA show_datatypes = ON
2020-11-12 15:01:16 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-11-12 15:01:16 cat: Prepare, proto: sqlite-3, msg: Statement 1 : PRAGMA foreign_keys = 1
2020-11-12 15:01:16 cat: Execute prepared, proto: sqlite-3, msg: Statement 1
2020-11-12 15:01:16 cat: Transaction, proto: sqlite-3, msg: BEGIN TRANSACTION
2020-11-12 15:01:16 cat: Prepare, proto: sqlite-3, msg: Statement 2 : CREATE TABLE t0(c0);

2020-11-12 15:01:16 cat: Execute prepared, proto: sqlite-3, msg: Statement 2
2020-11-12 15:01:17 cat: Transaction, proto: sqlite-3, msg: COMMIT TRANSACTION
2020-11-12 15:01:17 cat: Transaction, proto: sqlite-3, msg: BEGIN TRANSACTION
2020-11-12 15:01:17 cat: Transaction, proto: sqlite-3, msg: ROLLBACK TRANSACTION
2020-11-12 15:01:17 cat: Prepare, proto: sqlite-3, msg: Statement 3 : CREATE INDEX i0 ON t0(c0);

2020-11-12 15:01:17 cat: Execute prepared, proto: sqlite-3, msg: Statement 3
2020-11-12 15:01:17 cat: Disconnect, proto: sqlite-3, msg: DISCONNECT FROM "C:\...\data.sqlite"

For information this code was functionnal (10 year ago on 32bit system with Delphi 4).

Could you help me ?