Frequent “Database is Locked” Errors from SQLite Object Cache Plugin

Resolved rikq
(@rikq)

5 months, 2 weeks ago
We recently started noticing a large number of errors in our PHP error logs that look like this:
```
PHP Fatal error:  Uncaught SQLite3Exception: Unable to execute statement: database is locked in /wordpress/folder/path/wp-content/object-cache.php:2656
Stack trace:
#0 /wordpress/folder/path/wp-content/object-cache.php(2656): SQLite3Stmt->execute()
#1 /wordpress/folder/path/wp-content/object-cache.php(541): WP_Object_Cache->clear_flag()
#2 /wordpress/folder/path/wp-content/object-cache.php(2685): WP_Object_Cache->__construct()
#3 /wordpress/folder/path/wp-includes/load.php(893): wp_cache_init()
#4 /wordpress/folder/path/wp-settings.php(147): wp_start_object_cache()
#5 /wordpress/folder/path/wp-config.php(134): require_once('...')
#6 /wordpress/folder/path/wp-load.php(50): require_once('...')
#7 /wordpress/folder/path/wp-blog-header.php(13): require_once('...')
#8 /wordpress/folder/path/index.php(17): require('...')
#9 {main}
  thrown in /wordpress/folder/path/wp-content/object-cache.php on line 2656
```
These errors may have been occurring ever since we installed the plugin, but we only recently became aware of them.

Versions: WordPress: 6.8.1 SQLite: 3.45.1 php: 8.3.6 Server: nginx/1.24.0 Plugin: 1.5.6 APCu: 5.1.22 igbinary: 3.2.13.

We’ve had multiple incidents of data loss in the past related to object caching in WordPress (while trying the “Redis Object Cache” plugin as well as a couple of others). The data losses are especially related to WooCommerce data. So, even though this current SQLite error doesn’t appear to affect our site in any obvious way, I’m concerned that some write operations could be silently failing, leading to data corruption or loss that we might only discover too late.

I found a similar support topic here, but there doesn’t seem to be a clear resolution. For context, we are not using network-attached storage.

This may be unrelated but our cached data size setting on the SQLite Object Cache plugin settings page is 1024MiB. Our site is fairly large. We increased this number by looking at the statistics page and incrementally raising the size as recommended. Maybe as a consequence of this, the statistics page of the plugin has always taken a long time (over 30 seconds) to load up. While creating this support ticket, I tried to grab a screenshot of the statistics page, but the page doesn’t even load anymore. It times out.

Besides these issues, we have not (yet?) noticed anything else. The website frontend is fast and seems to work fine.

Any insight or suggestions would be greatly appreciated.

Viewing 3 replies - 1 through 3 (of 3 total)

Plugin Author OllieJones
(@olliejones)

5 months, 1 week ago
Thanks for the defect report!

(About redis) I hope you have pointed out your problems with redis to Till Krüss, the author of the Redis Object Cache plugin. None of us object cache developers like customer data loss. Also, I discovered that the default Redis configuration on its Ubuntu package doesn’t have its maxmemory parameter set, which lets it grow indefinitely. You might try setting that if you try redis again. The WordPress object cache needs some kind of eviction policy otherwise it fills with little-used stuff.

If you have a gigabyte-sized cache you must be running a large, and maybe very busy, site. (Congratulations on your success.) That’s a larger cache size than I have tested with so far.

I can suggest a couple of possible workarounds.
1. Disable the APCu cache. That eliminates the use of the clear_flag() operation that times out as each page view initializes your cache.
2. Try setting WP_SQLITE_OBJECT_CACHE_TIMEOUT to a larger value than the default of 5000 milliseconds in your wp-config,php . 10000 might be a good choice.
3. Reduce the size of the object cache. The same issue with eviction policy applies here as in redis.
I’ll try to fix the stats page timeout.
Thread Starter rikq
(@rikq)

5 months ago
Thanks for the response.

I’m hoping you can help clarify the logic behind this portion of code in the plugin’s object-cache.php file:
```
/* If wp-cli code cached something into SQLite, clear the APCu cache because it's stale. */
if ( $this->apcu_active && $this->clear_flag() ) {
	$this->apcu_clear_cache();
}
```
On our site, we use custom WP-CLI commands triggered by real cron jobs to perform a variety of tasks. Some of those tasks make use of the WordPress transient API.

From the comment in the snippet above, it seems that our APCu cache may be getting cleared every time one of these WP-CLI cron jobs runs. We’d like to continue benefiting from APCu caching if possible, but our WP-CLI operations are also important to our workflow.

To help us decide on the best trade-off, could you explain why the APCu cache becomes stale and needs to be cleared whenever WP-CLI code writes to SQLite?
Plugin Author OllieJones
(@olliejones)

4 months, 2 weeks ago

APCu has a big limitation: All the worker processes in an Apache or php-fpm server process pool share a single APCu cache. But php command-line processes, like those used in wp-cli, do not share that APCu cache. Instead, each php command-line process gets its own private copy of the APCu cache. That is, umm, not ideal. Might even call it silly. But it’s the way APCu works.

So, whenever a wp-cli process runs it’s necessary to invalidate the APCu cache so all cached items are retrieved from the SQLite cache instead. Otherwise web server php programs (handling page requests from browsers) will pick up stale cache entries that don’t contain the latest stuff from the wp-cli programs. That can cause real trouble. The monkey business with set_flag() and clear_flag() — shown in your question — lets the wp-cli processes tell the web server processes to invalidate the cache.

If you have a wp-cli intensive workflow, please consider not using APCu.

Viewing 3 replies - 1 through 3 (of 3 total)

You must be logged in to reply to this topic.