4

My scenario is simple, I have one process generating some data and putting it into the database (currently 5 seconds after the last one is finished) and then there are any number of processes opening a connection to read a single record to use internally (currently 5 seconds after the last one is finished). The database is located on the local drive and the OS is Windows Server 2012 R2.

With the reader processes I am occasionally receiving an error when connecting to the sqlite database, when the connection is opened an [FireDAC][Phys][SQLite] ERROR: unable to close due to unfinalized statements or unfinished backups exception is thrown and I'm stumped on the cause and the meaning of the error message (in the case of opening a connection).

My connection is created like so (in both the reader and writer application):

connection := TFDConnection.Create(nil);
connection.Params.Add('DriverID=SQLite');
connection.Params.Add('Database=' + aDatabasePath);
connection.Params.Add('OpenMode=CreateUTF16');
connection.Params.Add('LockingMode=Normal');
connection.Params.Add('JournalMode=WAL');
connection.Params.Add('Synchronous=Full');
connection.Params.Add('UpdateOptions.LockWait=True');
connection.Params.Add('BusyTimeout=30000');
connection.Params.Add('SQLiteAdvanced=temp_store=MEMORY');
connection.Params.Add('SQLiteAdvanced=page_size=4096');
connection.Params.Add('SQLiteAdvanced=auto_vacuum=FULL');

connection.Open();

After investigating the EFDDBEngineException that gets thrown there is only a single error in the list of errors and it contains ErrorCode=5 which sqlite errorcodes and sqlite result codes say are the SQLITE_BUSY error.

Investigating the callstack 

ntdll.dll                    KiUserExceptionDispatcher
FireDAC.Phys.SQLite          TFDPhysSQLiteConnection.InternalDisconnect
FireDAC.Phys                 TFDPhysConnection.ConnectBase
ntdll.dll                    KiUserExceptionDispatcher
FireDAC.Phys.SQLiteWrapper   TSQLiteStatement.PrepareBase
FireDAC.Phys.SQLiteWrapper   TSQLiteStatement.Prepare
FireDAC.Phys.SQLiteWrapper   TSQLiteStatement.Prepare
FireDAC.Phys.SQLite          TFDPhysSQLiteConnection.InternalExecuteDirect
FireDAC.Phys.SQLite          SetPragma
FireDAC.Phys.SQLite          TFDPhysSQLiteConnection.InternalConnect
FireDAC.Phys                 TFDPhysConnection.ConnectBase
FireDAC.Phys                 TFDPhysConnection.DoConnect
FireDAC.Phys                 TFDPhysConnection.Open
FireDAC.Comp.Client          TFDCustomConnection.DoInternalLogin
FireDAC.Comp.Client          TFDCustomConnection.DoLogin
FireDAC.Comp.Client          TFDCustomConnection.DoConnect
Data.DB                      TCustomConnection.SetConnected
FireDAC.Comp.Client          TFDCustomConnection.SetConnected
Data.DB                      TCustomConnection.Open

It's obviously not liking something that is happening in TSQLiteStatement.PrepareBase which then results in TFDPhysConnection.ConnectBase attempting to cleanup whatever point the creating of the connection is up to but where would the unfinalized statement be?

I Close() and Free() every TFDQuery and the connection when I'm finished.

What am I missing?

On a side note because it is a problem for me. Once the error occurs the WAL and SHM files don't get collapsed into the database file, and if I try to run the reader application on my dev machine under the debugger pointing at the database in the shared folder it locks completely when trying to open a connection and ending all other readers and the writer process doesn't unlock it and then I need to restart my dev machine.

Clayton Johnson
  • 279
  • 4
  • 16
  • You must not use [WAL](http://www.sqlite.org/wal.html) with a network file system. As for the error message, it indicates that some connection object could not be closed; does some read use more than one connection? – CL. Oct 17 '16 at 07:02
  • Hey thanks for that advise, do you have a reference for not using WAL with a network file system? Would be interesting in reading more, I figured that my dev machine locking would have something to do with the network file system locking :(. Also all of the processes create a connection, use it to do their task and then close it, wait x period of time and then repeat so I'm fairly confident that at any time each process only has one connection to the database – Clayton Johnson Oct 17 '16 at 07:26
  • Text in blue is a link; you can click it. And the error message says that some query was not closed. – CL. Oct 17 '16 at 07:27
  • oh didn't notice that, cheers – Clayton Johnson Oct 17 '16 at 07:29
  • 1
    You are setting `SQLiteAdvanced` parameter badly. It should be `SQLiteAdvanced=temp_store=MEMORY;page_size=4096;auto_vacuum=FULL`. – Victoria Jul 14 '17 at 08:16

0 Answers0