2. PHPRunner General questions
  3. Ignore SQLite locked database error
This topic is locked

Ignore SQLite locked database error

 		12/3/2019 3:35:43 AM
PHPRunner General questions
A
 alang author

I have a list view of an SQLite database table which a have setup to refresh every 20sec or so. The table has dynamic data which is populated by another process running simultaneously on the same computer and has several tabs that allow a filtered subset of the records - these tabs are configured to show the number of records on the tabs.
Every so often the PHPRunner code will encounter an error caused by the database being locked (contention of this type is expected) but I would prefer the PHPRunner code to ignore the error and continue regular refresh of the page rather than stop with the PHP error message.
I have tried the following line in BeforeProcessList event:

DB::CurrentConnection()->setSilentMode(true);


.. however I still get the error - it appears to be from the code that is populating the number of items on the tabs.
Any suggestions as to how I can avoid the error message? I have read a little bit about different modes of connection to the SQLite database which is better for concurrent access (WAL mode - see https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/) - I haven't tried it - not sure whether the PHPRunner interface would work with this or not.
A
 		alang author 12/5/2019

I have done some more playing with this and it seems to me at this point that PHPRunner cannot really handle concurrent access to a table which is being written by another process:

  • Tried setting WAL mode on the connection but it made no difference
  • Mostly the error came from the code that counted the rows to display on the list page
  • I disabled the tab numbers but this would also contribute to the error source
  • Sometimes the error was generated elsewhere - probably reading the grid data



I can see the dilemma because if the database cannot be read then you either display nothing or retry until you can read the database.
Seems like a reasonable workaround solution would be to create a "shadow" table that is copied by custom event code. If the database is locked, then skip the copy process. The data displayed may be a little "stale" but barely noticeable to the viewer - certainly better than getting locked on a PHP error page <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=89572&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
Sergey Kornilov admin 12/5/2019

I'm not sure if PHPRunner should be doing anything here. If we issue a simple select query and database is busy and returns an error to PHP application - it should be treated as an error. Probably with deeper digging into the code you can find a way just to ignore this error but it will mislead your users.
I see two possible solutions here.

  1. Switch to a more reliable database that will be able to return data without locking tables.
  2. Or use a more sophisticated approach to update data behind the scene using a temporary table.
    PS.

DB::CurrentConnection()->setSilentMode(true);


I'm not 100% sure if setSilentMode() function should be able to help. Not all databases may support it and also there is a chance this is not a SQL syntax error but an access error which is treated differently.
A
 		alang author 12/6/2019

Thanks Sergey - agreed.