SQLite "the database schema has changed"
lazarus / 16-Databases

----------------------------------------------------------------------
SymbolicFrank · 2022-10-21 14:46

Quite regularly, I get the above error. Ok, I create a completely new
database (dropping existing tables), insert values in them and read
sequentially through them.

The error almost always happens when writing things after traversing
through tables or views and then writing data to a completely
different table. If I use the same connection (after a commit, of
course), it happens. And if I use a completely new connection, the
tread silently closes and the application becomes idle. No error,
though.

I came across this thread on their forum, where they state that it
isn't an error, but just a warning without impact, only to show that
the statement has to be reprepared. But the Lazarus library simply
treats it as an error and stops execution. Sometimes silently.

Is this as it should be? It makes working with SQLite very difficult.

----------------------------------------------------------------------
Чебурашка · 2022-10-21 15:09

Similar to this

https://forum.lazarus.freepascal.org/index.php/topic,59358.0.html

If the SQLite team says that is not an error, but only a notification,
then our library should handle as such, and not raise an error like
now.

----------------------------------------------------------------------
SymbolicFrank · 2022-10-24 10:00

Quote from: tt on October 21, 2022, 05:09:25 pmSimilar to this

https://forum.lazarus.freepascal.org/index.php/topic,59358.0.html

If the SQLite team says that is not an error, but only a notification,
then our library should handle as such, and not raise an error like
now.

Thanks. Yes, that seems like the same issue.

The idea is this:

1. I (drop if exist and) create multiple tables.
2. I write a lot of data into them.
3. I create some views.
4. I open one and upload the records through a REST connection.
5. The result is a GUID that I have to store.

First, I tried adding an extra field to the table containing the
records and writing it in there: 'schema has changed' error.
Then, I created a new table that only contains the primary key and the
GUID, and write them in there: 'schema has changed' error.
Next, I cache them in a TStringList and write them afterwards:
depending on how I do it, I either get a 'schema has changed' error,
or a silent 'the database is locked' one, that kills the thread.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TAction.WriteUUIDs;var  q: TSQLquery;  s: string;  i: Integer;begin 
if (MyUUIDList.Count > 0) and Assigned(SourceConnection) then  begin 
 SourceConnection.Transaction.Commit;   
SourceConnection.ExecuteDirect('delete from ' + UUIDTable);    q :=
CreateQuery(SourceConnection);    q.SQL.Text := 'insert into ' +
UUIDTable +      ' (ID, UUID) values (:ID, :UUID);';    for i := 0 to
MyUUIDList.Count - 1 do    begin      s := MyUUIDList.Names[i];     
q.ParamByName('ID').Value := s;      q.ParamByName('UUID').Value :=
MyUUIDList.Values[s];      q.ExecSQL;      //q.Close;    end;    try 
   q.SQLTransaction.Commit;    except      on e:Exception do
Msg(e.Message);    end;    FreeQuery(q);  end;  MyUUIDList.Free;end;
This is the 'the database is locked' configuration. The 'Close', as
recommended at the end of the thread linked in the above thread, makes
no difference.

This is on win 10, trunk. I'll see if I can make a small demo-project.

----------------------------------------------------------------------
SymbolicFrank · 2022-10-24 12:03

This shows all problems (comment out one of the blocks at both
locations for the different errors):

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
SQLiteTest;{$mode ObjFPC}{$H+} uses  DB, SQLDB, SQLite3Conn; var 
conn, c: TSQLConnector;  query, q: TSQLQuery;  s, guid: string;  id:
Integer;begin  conn := TSQLConnector.Create(nil);  conn.ConnectorType
:= 'SQLite3';  conn.DatabaseName := 'SQLDB';  conn.UserName := ''; 
conn.Password := '';  conn.Transaction :=
TSQLTransaction.Create(nil);   query := TSQLQuery.Create(nil); 
query.UniDirectional := True;  query.DataBase := conn; 
query.SQLTransaction := conn.Transaction;   conn.ExecuteDirect('drop
table if exists table1');  conn.ExecuteDirect('drop table if exists
table2');  conn.ExecuteDirect('drop table if exists table3'); 
conn.ExecuteDirect('create table table1 (ID integer primary key, name
text)');  conn.ExecuteDirect('create table table2 (GUID text primary
key, name text)');  conn.ExecuteDirect('create table table3 (ID
integer primary key, GUID text)');  conn.Transaction.Commit;   c :=
TSQLConnector.Create(nil);  c.ConnectorType := 'SQLite3'; 
c.DatabaseName := 'SQLDB';  c.UserName := '';  c.Password := ''; 
c.Transaction := TSQLTransaction.Create(nil);   query.SQL.Text :=
'insert into table1 (ID, name) values (:ID, :name)'; 
query.ParamByName('ID').Value := 1;  query.ParamByName('name').Value
:= 'bla';  query.ExecSQL;  query.ParamByName('ID').Value := 2; 
query.ParamByName('name').Value := 'etc';  query.ExecSQL; 
query.ParamByName('ID').Value := 3;  query.ParamByName('name').Value
:= 'zomg';  query.ExecSQL;   query.SQL.Text := 'insert into table2
(GUID, name) values (:GUID, :name)';  query.ParamByName('GUID').Value
:= '12345';  query.ParamByName('name').Value := 'bla'; 
query.ExecSQL;  query.ParamByName('GUID').Value := '23456'; 
query.ParamByName('name').Value := 'etc';  query.ExecSQL; 
query.ParamByName('GUID').Value := '34567'; 
query.ParamByName('name').Value := 'zomg';  query.ExecSQL;  
query.SQLTransaction.Commit;   //conn.ExecuteDirect('drop view if
exists together');  // Single connection  //s := 'create view together
as select table1.ID, table1.name, table2.GUID ';  //s := s + 'from
table1 left join table2 using(name)';  //conn.ExecuteDirect(s); 
//conn.Transaction.Commit;  c.ExecuteDirect('drop view if exists
together');     // Two connections  s := 'create view together as
select table1.ID, table1.name, table2.GUID ';  s := s + 'from table1
left join table2 using(name)';  c.ExecuteDirect(s); 
c.Transaction.Commit;   query.SQL.Text := 'select * from together'; 
query.Open;   q := TSQLQuery.Create(nil);  q.UniDirectional := True;  
q.DataBase := conn;                          // Single connection 
q.SQLTransaction := conn.Transaction;  //q.DataBase := c;            
               // Two connections  //q.SQLTransaction :=
c.Transaction;   q.SQL.Text := 'insert into table3 (ID, GUID) values
(:ID, :GUID)';  while not query.EOF do  begin    id :=
query.FieldByName('ID').Value;    guid :=
query.FieldByName('GUID').Value;    q.ParamByName('ID').Value := id; 
 q.ParamByName('GUID').Value := guid;    q.ExecSQL;    query.Next; 
end;  query.Close;  q.SQLTransaction.Commit;   q.Free;  c.Free; 
query.Free;  conn.Free;end.
The configuration above also crashes the IDE.

Normally, this runs in multiple threads, hence the multiple
connections. Sharing connections among threads is not a good idea,
even if they're not running at the same time.

----------------------------------------------------------------------
Zvoni · 2022-10-24 14:39

ouch.
Just out of curiosity: Have you tried to close and reopen the
connection before querying your DB at the point it KABOOMs?
btw: IIRC, you should use "journal_mode = WAL" if multiple
users/connections are accessing an SQLite-DB

----------------------------------------------------------------------
SymbolicFrank · 2022-10-24 14:53

Quote from: Zvoni on October 24, 2022, 04:39:48 pmouch.
Just out of curiosity: Have you tried to close and reopen the
connection before querying your DB at the point it KABOOMs?
btw: IIRC, you should use "journal_mode = WAL" if multiple
users/connections are accessing an SQLite-DB
I'll experiment some more tomorrow, and I'll try those.

I already posted a thread about being unable to supply SQLite
configuration parameters, so I know the current configuration isn't
optimized for multiple connections. I take pains to keep everything
serialized, otherwise I would run most of it concurrent.

I had an import tool, but that did everything in code. Now it supports
multiple sources and destinations, I define actions and put the whole
configuration and the tables/views used in an ini-file. So I have a
whole list of actions, of which I could run all the ones with the same
source and destination at the same time. But SQLite limits that.

Then again, having to install and manage an SQL server on every client
doesn't sound all that great, either.

----------------------------------------------------------------------
SymbolicFrank · 2022-10-26 07:21

Ok. Closing all other connections works. The 'journal_mode = WAL'
prevents Lazarus from crashing, but that's about it. And it is hard to
implement: I use a TSQLConnector, because there are multiple supported
databases. That doesn't have an ExecSQL procedure and I cannot cast
it, because that procedure is protected and not virtual.

Without the option to supply your own parameters to the SQLite3
runtime, multiple connections aren't possible.


What a mess. I expected problems when I chose FPC/Lazarus for this
project, but one of the most important reasons to go for it was that I
expected rock-solid database support. And every database type I used
so far needs fixing...

----------------------------------------------------------------------
Zvoni · 2022-10-26 08:59

Quote from: SymbolicFrank on October 26, 2022, 09:21:14 amWithout the
option to supply your own parameters to the SQLite3 runtime, multiple
connections aren't possible.
Huh? How do you figure that?
Excerpt from my own code
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i conn :=
TSQLConnector.Create(nil);  conn.ConnectorType := 'SQLite3'; 
conn.DatabaseName := 'SQLDB';  conn.UserName := '';  conn.Password :=
'';  Tran:=TSQLTransaction.Create(Conn);  Conn.Transaction:=Tran;   If
conn.ConnectorType = 'SQLite3' Then Begin   
Conn.Params.Add('foreign_keys=ON');   
Conn.Params.Add('journal_mode=WAL');  //HERE  End; 
Query:=TSQLQuery.Create(Nil);  Query.DataBase:=Conn; 
Query.Transaction:=Tran;  Conn.Open;
I'd be very surprised if TSQLConnector (or its ancestors) wouldn't
have the Params-Collection

----------------------------------------------------------------------
SymbolicFrank · 2022-10-26 10:26

Ok, I added it.

I checked all the code and made sure every action starts with:
'connection.Open' and ends with 'connection.Close'.

If you look at the opening of the connection:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TSQLite3Connection.DoInternalConnect;const  PRAGMAS:array[0..1] of
string=('foreign_keys','journal_mode');var  filename: ansistring; 
pvfs: PChar;  i,j: integer;begin  Inherited;  if DatabaseName = ''
then    DatabaseError(SErrNoDatabaseName,self);  InitializeSQLite; 
filename := DatabaseName;  if FVFS <> '' then    pvfs :=
PAnsiChar(FVFS)  else    pvfs := Nil;  checkerror(sqlite3_open_v2(PAns
iChar(filename),@fhandle,GetSQLiteOpenFlags,pvfs));  if
(Length(Password)>0) and assigned(sqlite3_key) then    checkerror(sqli
te3_key(fhandle,PChar(Password),StrLen(PChar(Password))));  for
i:=Low(PRAGMAS) to High(PRAGMAS) do begin   
j:=Params.IndexOfName(PRAGMAS[i]);    if j <> -1 then     
execsql('PRAGMA '+Params[j]);  end;end;
you see that there are only two parameters supported. If you look at
the SQLite documentation:

Code: [Select]    #define SQLITE_CONFIG_SINGLETHREAD  1  /* nil */
    #define SQLITE_CONFIG_MULTITHREAD   2  /* nil */
    #define SQLITE_CONFIG_SERIALIZED    3  /* nil */
    #define SQLITE_CONFIG_MALLOC        4  /* sqlite3_mem_methods* */
    #define SQLITE_CONFIG_GETMALLOC     5  /* sqlite3_mem_methods* */
    #define SQLITE_CONFIG_SCRATCH       6  /* No longer used */
    #define SQLITE_CONFIG_PAGECACHE     7  /* void*, int sz, int N */
    #define SQLITE_CONFIG_HEAP          8  /* void*, int nByte, int
min */
    #define SQLITE_CONFIG_MEMSTATUS     9  /* boolean */
    #define SQLITE_CONFIG_MUTEX        10  /* sqlite3_mutex_methods*
*/
    #define SQLITE_CONFIG_GETMUTEX     11  /* sqlite3_mutex_methods*
*/
    /* previously SQLITE_CONFIG_CHUNKALLOC 12 which is now unused. */
    #define SQLITE_CONFIG_LOOKASIDE    13  /* int int */
    #define SQLITE_CONFIG_PCACHE       14  /* no-op */
    #define SQLITE_CONFIG_GETPCACHE    15  /* no-op */
    #define SQLITE_CONFIG_LOG          16  /* xFunc, void* */
    #define SQLITE_CONFIG_URI          17  /* int */
    #define SQLITE_CONFIG_PCACHE2      18  /* sqlite3_pcache_methods2*
*/
    #define SQLITE_CONFIG_GETPCACHE2   19  /* sqlite3_pcache_methods2*
*/
    #define SQLITE_CONFIG_COVERING_INDEX_SCAN 20  /* int */
    #define SQLITE_CONFIG_SQLLOG       21  /* xSqllog, void* */
    #define SQLITE_CONFIG_MMAP_SIZE    22  /* sqlite3_int64,
sqlite3_int64 */
    #define SQLITE_CONFIG_WIN32_HEAPSIZE      23  /* int nByte */
    #define SQLITE_CONFIG_PCACHE_HDRSZ        24  /* int *psz */
    #define SQLITE_CONFIG_PMASZ               25  /* unsigned int
szPma */
    #define SQLITE_CONFIG_STMTJRNL_SPILL      26  /* int nByte */
    #define SQLITE_CONFIG_SMALL_MALLOC        27  /* boolean */
    #define SQLITE_CONFIG_SORTERREF_SIZE      28  /* int nByte */
    #define SQLITE_CONFIG_MEMDB_MAXSIZE       29  /* sqlite3_int64 */

you see that there are some interesting configuration options for the
runtime you cannot set. SQLITE_CONFIG_SINGLETHREAD is the default. To
change it for use with FPC, the only option I can think of is building
by own SQLite3.dll and preconfigure it.

But, while checking that out I noticed the OpenFlags and found the
solution:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
connection.OpenFlags := [sofReadWrite, sofCreate, sofFullMutex,
sofSharedCache];
If you do that for each connection, multiple connections work

But it is a bummer that you cannot use that with a TSQLConnector, as
it is not related to a TSQLite3Connection. So I have to go back to
casting TSQLConnections.

----------------------------------------------------------------------
Zvoni · 2022-10-26 11:38

Quote from: SymbolicFrank on October 26, 2022, 12:26:28 pmBut it is a
bummer that you cannot use that with a TSQLConnector, as it is not
related to a TSQLite3Connection. So I have to go back to casting
TSQLConnections.
Wouldn't be too sure about that.
You need to include the Unit for which the TSQLConnector is supossed
to work,
so in a way, TSQLConnector "relays" codecalls to the chosen DB-System,
so there must be a communication between TSQLConnector and
TSQlite3Conn

EDIT
Quoteyou see that there are only two parameters supported.
Wrong. Even the source code you posted shows clearly, that the
PRAGMA's are executed after connection is established.
The two PRAGMA's in the source code are the only two supported to be
set via the Params-Collection of the Connection.

Conn.Params.Add('journal_mode=WAL');
Conn.Open;

You're still free to fire off an ExecSQL('PRAGMA
MyPragmaName=SomeValue') as a first command after opening the
connection or whenever you feel like it,
which the source-code you showed is actually doing.

EDIT2: Just saw, that TSQLConnector's ancestor TSQLConnection offers a
published Property "Params"

----------------------------------------------------------------------
SymbolicFrank · 2022-10-26 12:59

Ah, yes, I see it. My mistake.

But for the TSQLite3Connection you need to use the OpenFlags, which
only exist there.

See also here.