database lock error by frequent operation of sqlite-related plugins

[dgy18787]

Hello,

I have updated the SQLite and struct, ratings plugins to the latest version, but these days "database locked" error often occurs and it makes the DokuWiki fail to function.

The error seems to happen in the following processes, based on the error log of DokuWiki:

First, the DokuWiki exceeds the IO limited by the server: 2023-12-09 07:48:07 /path_doku/inc/io.php(538) E_ERROR: Allowed memory size of 67108864 bytes exhausted (tried to allocate 16777224 bytes)

then, the SQLite seems to try to be in WAL mode but failed:

2023-12-09 13:12:51	/path_doku/lib/plugins/sqlite/SQLiteDB.php(132)	SQLite: Could not set WAL mode.
  {
      "errorInfo": null
  }

then, the database is locked:

2023-12-09 13:13:01	/path_doku/lib/plugins/sqlite/SQLiteDB.php(473)	SQLite: Could not read dbversion from opt table due to unexpected error
  {
      "dbname": "struct",
      "exception": "PDOException",
      "message": "SQLSTATE[HY000]: General error: 5 database is locked",
      "code": 0
  }

and more error:

2023-12-09 13:13:11	/path_doku/lib/plugins/sqlite/SQLiteDB.php(132)	PDOException: SQLSTATE[HY000]: General error: 5 database is locked
  #0 /path_doku/lib/plugins/sqlite/SQLiteDB.php(170): dokuwiki\plugin\sqlite\SQLiteDB->query('CREATE TABLE IF...')
  #1 /path_doku/lib/plugins/sqlite/SQLiteDB.php(479): dokuwiki\plugin\sqlite\SQLiteDB->exec('CREATE TABLE IF...')
  #2 /path_doku/lib/plugins/sqlite/SQLiteDB.php(404): dokuwiki\plugin\sqlite\SQLiteDB->currentDbVersion()
  #3 /path_doku/lib/plugins/sqlite/SQLiteDB.php(79): dokuwiki\plugin\sqlite\SQLiteDB->applyMigrations()
  #4 /path_doku/lib/plugins/struct/helper/db.php(27): dokuwiki\plugin\sqlite\SQLiteDB->__construct('struct', '/path...')
  #5 /path_doku/lib/plugins/struct/helper/db.php(50): helper_plugin_struct_db->init()
  #6 /path_doku/lib/plugins/struct/meta/Assignments.php(51): helper_plugin_struct_db->getDB()
  #7 /path_doku/lib/plugins/struct/meta/Assignments.php(37): dokuwiki\plugin\struct\meta\Assignments->__construct()
  #8 /path_doku/lib/plugins/struct/action/cache.php(54): dokuwiki\plugin\struct\meta\Assignments::getInstance()
  #9 /path_doku/inc/Extension/EventHandler.php(80): action_plugin_struct_cache->handleCacheSchemachange(Object(dokuwiki\Extension\Event), NULL)
  #10 /path_doku/inc/Extension/Event.php(74): dokuwiki\Extension\EventHandler->process_event(Object(dokuwiki\Extension\Event), 'BEFORE')
  #11 /path_doku/inc/Extension/Event.php(132): dokuwiki\Extension\Event->advise_before(true)
  #12 /path_doku/inc/Extension/Event.php(199): dokuwiki\Extension\Event->trigger(Array, true)
  #13 /path_doku/inc/Cache/Cache.php(86): dokuwiki\Extension\Event::createAndTrigger('PARSER_CACHE_US...', Object(dokuwiki\Cache\CacheRenderer), Array)
  #14 /path_doku/inc/parserutils.php(152): dokuwiki\Cache\Cache->useCache()
  #15 /path_doku/inc/parserutils.php(96): p_cached_output('/path...', 'xhtml', 'sidebar')
  #16 /path_doku/inc/template.php(1592): p_wiki_xhtml('sidebar', '', false)
  #17 /path_doku/lib/tpl/dokuwiki/main.php(53): tpl_include_page('sidebar', true, true)
  #18 /path_doku/inc/actions.php(27): include('/path...')
  #19 /path_doku/doku.php(126): act_dispatch()
  #20 {main}

When the error occurs, at first, the whole page of the site (like the index) cannot be rendered properly, it will stop the rendering at the sidebar and show the error message on the position where the sidebar should be; if one refresh the page several times, the page will be broken and only a error information with the hint saying the sqlite might have problems:

PDOException: SQLSTATE[HY000]: General error: 5 database is locked
An unforeseen error has occured. This is most likely a bug somewhere. It might be a problem in the sqlite plugin.

More info has been written to the DokuWiki error log.

I suspected the issue was caused by ratings plugins as the plugins is not updated for years. but after disabling it, the SQLite didn't work again and this time it was caused by struct. the strange thing is that we have not updated the struct data for a long time (just look up, no write or update), I have no idea what kind of operation caused the database lock.

I think this error is also related to a limited RAM assigned to a single php thread and a limited time of execution. Is there any possible solution for it? Thanks!