SQLite: Error in prepare_query with DELETE / delete_expired_transients

[mbirth]

Bug Description

Every day at 15:10 wp-cron.php fails with the following output:

WordPress database error <div style="clear:both">&nbsp;</div><div class="queries" style="clear:both;margin_bottom:2px;border:red dotted thin;"><p>Queries made or created this session were:</p><ol><li>Raw query:
DELETE a, b FROM wp_options a, wp_options b
			WHERE a.option_name LIKE &#039;_transient_%&#039;
			AND a.option_name NOT LIKE &#039;_transient_timeout_%&#039;
			AND b.option_name = CONCAT( &#039;_transient_timeout_&#039;, SUBSTRING( a.option_name, 12 ) )
			AND b.option_value &lt; 1676124615</li><li>Rewritten:
DELETE a, b FROM wp_options a, wp_options b
			WHERE a.option_name LIKE &#039;_transient_%&#039;
			AND a.option_name NOT LIKE &#039;_transient_timeout_%&#039;
			AND b.option_name = CONCAT( &#039;_transient_timeout_&#039;, SUBSTRING( a.option_name, 12 ) )
			AND b.option_value &lt; 1676124615</li><li>With Placeholders:
DELETE a, b FROM wp_options a, wp_options b
			WHERE a.option_name LIKE  :param_0 
			AND a.option_name NOT LIKE  :param_1 
			AND b.option_name = CONCAT(  :param_2 , SUBSTRING( a.option_name, 12 ) )
			AND b.option_value &lt; 1676124615</li><li>Prepare:
DELETE a, b FROM wp_options a, wp_options b
			WHERE a.option_name LIKE  :param_0 
			AND a.option_name NOT LIKE  :param_1 
			AND b.option_name = CONCAT(  :param_2 , SUBSTRING( a.option_name, 12 ) )
			AND b.option_value &lt; 1676124615</li></ol></div><div style="clear:both;margin_bottom:2px;border:red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">Error occurred at line 705 in Function <code>prepare_query</code>. Error message was: Problem preparing the PDO SQL Statement.  Error was: SQLSTATE[HY000]: General error: 1 near "a": syntax error.</div><pre>#0 WP_SQLite_PDO_Engine->get_error_message() called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php:250]
#1  WP_SQLite_DB->query(DELETE a, b FROM wp_options a, wp_options b
			WHERE a.option_name LIKE '_transient_%'
			AND a.option_name NOT LIKE '_transient_timeout_%'
			AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )
			AND b.option_value < 1676124615) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/option.php:1056]
#2  delete_expired_transients() called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/class-wp-hook.php:308]
#3  WP_Hook->apply_filters(, Array ()) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/class-wp-hook.php:332]
#4  WP_Hook->do_action(Array ()) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/plugin.php:565]
#5  do_action_ref_array(delete_expired_transients, Array ()) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-cron.php:188]
</pre> for query DELETE a, b FROM wp_options a, wp_options b
			WHERE a.option_name LIKE '_transient_%'
			AND a.option_name NOT LIKE '_transient_timeout_%'
			AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) )
			AND b.option_value < 1676124615 made by do_action_ref_array('delete_expired_transients'), WP_Hook->do_action, WP_Hook->apply_filters, delete_expired_transients, WP_SQLite_DB->query, WP_SQLite_DB->print_error
WordPress database error <div style="clear:both">&nbsp;</div><div class="queries" style="clear:both;margin_bottom:2px;border:red dotted thin;"><p>Queries made or created this session were:</p><ol><li>Raw query:
DELETE a, b FROM wp_options a, wp_options b
				WHERE a.option_name LIKE &#039;_site_transient_%&#039;
				AND a.option_name NOT LIKE &#039;_site_transient_timeout_%&#039;
				AND b.option_name = CONCAT( &#039;_site_transient_timeout_&#039;, SUBSTRING( a.option_name, 17 ) )
				AND b.option_value &lt; 1676124615</li><li>Rewritten:
DELETE a, b FROM wp_options a, wp_options b
				WHERE a.option_name LIKE &#039;_site_transient_%&#039;
				AND a.option_name NOT LIKE &#039;_site_transient_timeout_%&#039;
				AND b.option_name = CONCAT( &#039;_site_transient_timeout_&#039;, SUBSTRING( a.option_name, 17 ) )
				AND b.option_value &lt; 1676124615</li><li>With Placeholders:
DELETE a, b FROM wp_options a, wp_options b
				WHERE a.option_name LIKE  :param_0 
				AND a.option_name NOT LIKE  :param_1 
				AND b.option_name = CONCAT(  :param_2 , SUBSTRING( a.option_name, 17 ) )
				AND b.option_value &lt; 1676124615</li><li>Prepare:
DELETE a, b FROM wp_options a, wp_options b
				WHERE a.option_name LIKE  :param_0 
				AND a.option_name NOT LIKE  :param_1 
				AND b.option_name = CONCAT(  :param_2 , SUBSTRING( a.option_name, 17 ) )
				AND b.option_value &lt; 1676124615</li></ol></div><div style="clear:both;margin_bottom:2px;border:red dotted thin;" class="error_message" style="border-bottom:dotted blue thin;">Error occurred at line 705 in Function <code>prepare_query</code>. Error message was: Problem preparing the PDO SQL Statement.  Error was: SQLSTATE[HY000]: General error: 1 near "a": syntax error.</div><pre>#0 WP_SQLite_PDO_Engine->get_error_message() called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php:250]
#1  WP_SQLite_DB->query(DELETE a, b FROM wp_options a, wp_options b
				WHERE a.option_name LIKE '_site_transient_%'
				AND a.option_name NOT LIKE '_site_transient_timeout_%'
				AND b.option_name = CONCAT( '_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) )
				AND b.option_value < 1676124615) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/option.php:1071]
#2  delete_expired_transients() called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/class-wp-hook.php:308]
#3  WP_Hook->apply_filters(, Array ()) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/class-wp-hook.php:332]
#4  WP_Hook->do_action(Array ()) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-includes/plugin.php:565]
#5  do_action_ref_array(delete_expired_transients, Array ()) called at [/var/www/virtual/mbirth/apps/wordpress-6.1.1/wp-cron.php:188]
</pre> for query DELETE a, b FROM wp_options a, wp_options b
				WHERE a.option_name LIKE '_site_transient_%'
				AND a.option_name NOT LIKE '_site_transient_timeout_%'
				AND b.option_name = CONCAT( '_site_transient_timeout_', SUBSTRING( a.option_name, 17 ) )
				AND b.option_value < 1676124615 made by do_action_ref_array('delete_expired_transients'), WP_Hook->do_action, WP_Hook->apply_filters, delete_expired_transients, WP_SQLite_DB->query, WP_SQLite_DB->print_error

Steps to reproduce

1. Install WordPress + SQLite plugin
2. Disable internal cron handling and use Crontab to run wp-cron.php every 5 minutes
3. Make sure [email protected] is set in Crontab before the wp-cron entry
4. Wait for the error to appear in your mailbox

Additional Context

• PHP Version: 8.0.27 (Supports 64bit values)
• OS: Linux 3.10.0-1160.31.1.el7.x86_64 x86_64
• Plugin Version: Standalone SQLite Database Integration Version 1.0.3
• Other installed plugins:
  • Acronyms 2 Version 2.0.8
  • ActivityPub Version 0.16.2
  • Akismet Anti-Spam Version 5.0.2
  • Hum Version 1.3.0
  • Jetpack Version 11.8
  • Limit log in attempts reloaded Version 2.25.12
  • Local Google Fonts Version 0.20.0
  • Modern Footnotes Version 1.4.14
  • NodeInfo Version 1.0.8
  • Simple Alert Blocks Version 1.3
  • SiteGround Security Version 1.4.0
  • SVG Support Version 2.5.5
  • WebFinger Version 3.2.5
  • WP Extra File Types Version 0.5.2

[OllieJones]

This is one of probably many SQL dialect issues. delete_expired_transients() uses a multi-table variant of the DELETE syntax that's absent in SQLlite.

[felixarntz]

@aristath Is there any progress on this one, any idea how to fix it? Just moving it from Monday's release to the following milestone for now.

[aristath]

We can back port the SQLite rewrite from the standalone plugin, that should fix this issue... I created a PR on #677 to do that 👍

[mukeshpanchal27]

@aristath As we are nearing the 2.2.0 release and the PR #677 needs more eyes to check it before merging, I'm going to punt this to the next milestone. Thanks.

[joemcgill]

I'm removing this from the 2.3.0 milestone, since my understanding is that we are no longer planning to backport changes from the standalone SQLite plugin to this repo. See: #661 (comment).

Will wait to close this until we've confirmed what the transition plan is.

[felixarntz]

The SQLite module will be removed from Performance Lab in the upcoming release, see #737 for additional context. As such, please report this issue in the standalone plugin repository instead.