[SOLVED] program crashes when disk with database gets temporary full

[Hartmut]

My program runs in the background and writes every 30 seconds some data into an SQLite database. It crashed, when the disk with the database got full (e.g. by failure of another program).

I improved procedure sql_close() to catch 2 exceptions. Now it crashes not longer, but when enough disk space is available again, it does not "forget" it's error state and crashes, when I exit the program.

Here comes a small demo. It updates every 3 seconds one record, until you press a key:

Code: Pascal  [Select][+][-]

1. {$mode objfpc}{$H+}
2.  
3. uses sysutils,sqlite3conn,sqldb,db,crt;
4.  
5. const FspecDB = 'd:\test.sqlite'; {select your database (it is created automatically)}
6.  
7. var DataSourceX: TDataSource;
8.     DBConnectionX: TSQLite3Connection;
9.     SQLQueryX: TSQLQuery;
10.     SQLTransactionX: TSQLTransaction;
11.  
12. procedure sql_init(fspecDB: string);
13.    {complete initialization of the SQLite-database in file 'fspecDB'}
14.    begin
15.    DataSourceX:=TDataSource.Create(nil);       // create all vars:
16.    DBConnectionX:=TSQLite3Connection.Create(nil);
17.    SQLQueryX:=TSQLQuery.Create(nil);
18.    SQLTransactionX:=TSQLTransaction.Create(nil);
19.  
20.    DBConnectionX.Transaction:=SQLTransactionX; // connect all vars:
21.    SQLQueryX.Database:=DBConnectionX;
22. // SQLQueryX.Transaction:=SQLTransactionX; // happens automatically
23.    DataSourceX.Dataset:=SQLQueryX;
24.  
25.    DBConnectionX.Name:='DBConnection';
26.    DBConnectionX.DatabaseName:=fspecDB;        // assign filespec
27.    end; {sql_init}
28.  
29. function sql_commit: boolean;
30.    {commits and returns if successful}
31.    begin
32.    write('[commit] ');
33.  
34.    try
35.       SQLTransactionX.Commit;
36.       exit(true); {everything was OK}
37.    except
38.       on E: Exception do
39.          begin
40.          writeln('COMMIT-Error: ', E.message);
41.          end;
42.    end; {try}
43.  
44.    exit(false); {an Error occured}
45.    end; {sql_commit}
46.  
47. function sql_exec(sql: string; commit: boolean): boolean;
48.    {executes a non-SELECT-SQL-command and returns if successful}
49.    begin
50.    write(sql, ' '); {show SQL-command}
51.  
52.    try
53.       SQLQueryX.Close;
54.       SQLQueryX.SQL.Text:=sql;
55.       DBConnectionX.Connected:= True; // establish the connection to the DB
56.       SQLTransactionX.Active:= True;  // activate the transaction
57.       SQLQueryX.ExecSQL;
58.       if commit then
59.          if not sql_commit then exit(false); {if an Error occured}
60.       writeln('<OK>');
61.       exit(true); {everything was OK}
62.    except
63.       on E: Exception do
64.          begin
65.          writeln('SQL-Error: ', E.message);
66.          end;
67.    end; {try}
68.  
69.    exit(false); {an Error occured}
70.    end; {sql_exec}
71.  
72. procedure sql_close;
73.    {closes the DB-Connection}
74.    begin
75. write('<A> ');
76.    SQLQueryX.Close;                   // close SQL-query (makes no problems)
77.  
78. write('<B> ');
79.    try
80.       SQLTransactionX.Active:=False;  // close Transaction:
81.    except
82.       on E:Exception do
83.          begin
84.          writeln('ERROR B = ',  E.Message);
85.          writeln('B) ', SQLTransactionX.Active);
86.          end;
87.    end; {try}
88.  
89. write('<C> ');
90.    try
91.       DBConnectionX.Connected:=False; // close DB-connection:
92.    except
93.       on E:Exception do
94.          begin
95.          writeln('ERROR C = ',  E.Message);
96.          writeln(DBConnectionX.Connected);
97.          end;
98.    end; {try}
99.  
100. writeln('<D>');
101.    end;
102.  
103. procedure sql_done;
104.    {closes the DB-Connection and free's all vars}
105.    begin
106.    sql_close; {close the DB-Connection}
107.  
108.    write('<E>'); DataSourceX.Free;
109.    write('<F>'); DBConnectionX.Free;
110.    write('<G>'); SQLQueryX.Free;
111.    write('<H>'); SQLTransactionX.Free; writeln('<I>');
112.    end;
113.  
114. var nr: longint;
115.     ok: boolean;
116.  
117. begin {main}
118. sql_init(FspecDB); // init SQL-DB
119.                                                {create 1 table with 1 record: }
120. ok:=sql_exec('CREATE TABLE IF NOT EXISTS table1 (count INTEGER)',true);
121. ok:=sql_exec('DELETE FROM table1',true);
122. ok:=sql_exec('INSERT INTO table1 VALUES (1)',true);
123.  
124. nr:=0;
125. repeat inc(nr);                             {update 1 record every 3 seconds: }
126.        write(nr, ') ');
127.        ok:=sql_exec('UPDATE table1 SET count=' + IntToStr(nr),false);
128.           {update here more other records ...}
129.        if sql_commit then writeln('<OK>'); {use only 1 common commit}
130.        sql_close;   {allow database-access to others while sleeping}
131.        sleep(3000);
132.        writeln;
133. until crt.KeyPressed;
134.  
135. sql_done; // closes the DB-Connection and free's all vars
136. end.

And here is the output of the program:

Code: Text  [Select][+][-]

1. // the start of the program shows:
2. CREATE TABLE IF NOT EXISTS table1 (count INTEGER) [commit] <OK>
3. DELETE FROM table1 [commit] <OK>
4. INSERT INTO table1 VALUES (1) [commit] <OK>
5. 1) UPDATE table1 SET count=1 <OK>
6. [commit] <OK>
7. <A> <B> <C> <D>
8.  
9. 2) UPDATE table1 SET count=2 <OK>
10. [commit] <OK>
11. <A> <B> <C> <D>
12. ...
13.  
14. // when the disk got full it changes to:
15. 5) UPDATE table1 SET count=5 SQL-Error: DBConnection : database or disk is full
16. [commit] COMMIT-Error: cannot commit - no transaction is active
17. <A> <B> ERROR B = cannot rollback - no transaction is active
18. B) TRUE
19. <C> ERROR C = cannot rollback - no transaction is active
20. TRUE
21. <D>
22. ...
23.  
24. // when the disk has again enough space:
25. 9) UPDATE table1 SET count=9 <OK>
26. [commit] COMMIT-Error: cannot commit - no transaction is active
27. <A> <B> ERROR B = cannot rollback - no transaction is active
28. B) TRUE
29. <C> ERROR C = cannot rollback - no transaction is active
30. TRUE
31. <D>
32. ...
33.  
34. // when you exit the program:
35. <E><F>An unhandled exception occurred at $080728AC:
36. EDatabaseError: cannot rollback - no transaction is active

We see: when the disk gets full, we get an exception "cannot rollback - no transaction is active" in sql_close(), which stays, even if the disk has again enough space.

What I want is:
 - when the disk gets full (or is accidently unmounted on Linux), the program shall not crash (this I could "manage") and it shall continue to work in Memory.
 - as soon as above problem ist solved, the program shall again write into the database without creating those exceptions.
Please, how can I achieve this?

Versions:
 - FPC 3.2.0 and 3.0.4
 - Linux Ubuntu 18.04 and Windows 7

I'm only an "advanced beginner" to databases, so please be not too short with suggestions or explainations. I attached a compilable project. Thanks for your help in advance.

[Hartmut]

Maybe I've found a solution. Because I got no posts, I invested more time in many experiments and sometime I stumbled over TSQLTransaction.Options. I played with the possible options and 'stoUseImplicit' seems to solve my problem. Now my sql_init() looks like:

Code: Pascal  [Select][+][-]

1. procedure sql_init(fspecDB: string);
2.    {complete initialization of the SQLite-database in file 'fspecDB'}
3.    begin
4.    DataSourceX:=TDataSource.Create(nil);       // create all vars:
5.    DBConnectionX:=TSQLite3Connection.Create(nil);
6.    SQLQueryX:=TSQLQuery.Create(nil);
7.    SQLTransactionX:=TSQLTransaction.Create(nil);
8.  
9.    DBConnectionX.Transaction:=SQLTransactionX; // connect all vars:
10.    SQLQueryX.Database:=DBConnectionX;
11. // SQLQueryX.Transaction:=SQLTransactionX; // happens automatically
12.    DataSourceX.Dataset:=SQLQueryX;
13.  
14.    DBConnectionX.Name:='DBConnection';
15.    DBConnectionX.DatabaseName:=fspecDB;        // assign filespec
16.    SQLTransactionX.Options:=[stoUseImplicit];
17.    end; {sql_init}

Now in my small demo I get only this kind of errors (which is ok):

5) UPDATE table1 SET count=5 SQL-Error: DBConnection : database or disk is full

an all other errors never appear any longer.

But before I implement this in my real world program, I want to ask the experts about the meaning and consequences of this command, because I do not really understand this from the documentation:

Options can be used to control the behaviour of SQLDB for this transaction:
 - stoUseImplicit: Use the implicit transaction support of the DB engine. This means that no explicit transaction start and stop commands will be sent to the server when the Commit or Rollback methods are called (effectively making them a no-op at the DB level).
 - stoExplicitStart: When set, whenever an SQL statement is executed, the transaction must have been started explicitly. Default behaviour is that the TSQLStatement or TSQLQuery start the transaction as needed.

Here are my questions: Does above command SQLTransactionX.Options:=[stoUseImplicit] mean:
 - transactions and rollbacks are completely deactivated and impossible?
 - all commands to start a transaction or to do a commit or rollback are completely ignored?

In this particular program I do not need any rollbacks. But I want to know this for other programs, whether to add this command there or not, because "disk full" might happen always...

Thanks in advance.

Update:
You can find an answer to my questions in https://forum.lazarus.freepascal.org/index.php/topic,56078.0.html