Recent

Author Topic: [SOLVED] program crashes when disk with database gets temporary full  (Read 998 times)

Hartmut

  • Sr. Member
  • ****
  • Posts: 494
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.
« Last Edit: August 31, 2021, 12:34:31 pm by Hartmut »

Hartmut

  • Sr. Member
  • ****
  • Posts: 494
Re: program crashes when disk with database gets temporary full
« Reply #1 on: August 25, 2021, 04:10:15 pm »
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
« Last Edit: August 31, 2021, 12:34:05 pm by Hartmut »

 

TinyPortal © 2005-2018