no such column: wp_options

[morganwdavis]

Version 2.1.9

I'm continuing to see these entries (and similar) appear in the PHP error log many times per minute while accessing the site. The no such column: wp_options seems to happen when the ON DUPLICATE translator messes up quotation marks by using the wrong ones (at least in my experience with this in the past). I thought this was fixed in recent versions of the plugin but apparently not.

Is this something the code needs to fix, or is there something in the database on my end that I need to fix/clear out?

[30-Apr-2024 19:45:37 UTC] WordPress database error <div style="clear:both">&nbsp;</div>
<div class="queries" style="clear:both;margin-bottom:2px;border:red dotted thin;">
<p>MySQL query:</p>
<p>INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1714506337.4271841049194335937500', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`)</p>
<p>Queries made or created this session were:</p>
<ol>
<li>Executing: BEGIN | (no parameters)</li>
<li>Executing: SELECT * FROM pragma_table_info(:table_name) as l WHERE l.pk &gt; 0; | (no parameters)</li>
<li>Executing: SELECT * FROM pragma_index_list(&quot;wp_options&quot;) as l; | (no parameters)</li>
<li>Executing: ROLLBACK | (no parameters)</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 3545 in Function <code>handle_error</code>. Error message was: SQLSTATE[HY000]: General error: 1 no such column: wp_options.
</div>
<p>Backtrace:</p>
<pre>#0 /path/to/site/blog/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php(287): WP_SQLite_Translator->get_error_message()
#1 /path/to/site/blog/wp-includes/option.php(1006): WP_SQLite_DB->query('INSERT INTO `wp...')
#2 /path/to/site/blog/wp-includes/option.php(1316): add_option('_transient_doin...', '1714506337.4271...', '', 'yes')
#3 /path/to/site/blog/wp-includes/cron.php(910): set_transient('doing_cron', '1714506337.4271...')
#4 /path/to/site/blog/wp-includes/cron.php(1028): spawn_cron(1714506337.4272)
#5 /path/to/site/blog/wp-includes/class-wp-hook.php(324): _wp_cron('')
#6 /path/to/site/blog/wp-includes/class-wp-hook.php(348): WP_Hook->apply_filters(NULL, Array)
#7 /path/to/site/blog/wp-includes/plugin.php(517): WP_Hook->do_action(Array)
#8 /path/to/site/blog/wp-settings.php(717): do_action('wp_loaded')
#9 /path/to/site/blog/wp-config.php(78): require_once('/data/web/m/d/m...')
#10 /path/to/site/blog/wp-load.php(50): require_once('/data/web/m/d/m...')
#11 /path/to/site/blog/wp-blog-header.php(13): require_once('/data/web/m/d/m...')
#12 /path/to/site/blog/index.php(17): require('/data/web/m/d/m...')
#13 {main}</pre>
 for query INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1714506337.4271841049194335937500', 'yes') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`) made by require('wp-blog-header.php'), require_once('wp-load.php'), require_once('wp-config.php'), require_once('wp-settings.php'), do_action('wp_loaded'), WP_Hook->do_action, WP_Hook->apply_filters, _wp_cron, spawn_cron, set_transient, add_option, WP_SQLite_DB->query, WP_SQLite_DB->print_error

[aristath]

I can replicate the issue, but so far I've been unable to find a reliable solution. As a workaround, installing the sqlite-object-cache plugin along with this one, fixes the issue - and also improves performance.

@OllieJones do you have any idea on how to address this? Can you see why installing the sqlite-object-cache plugin would fix the issue? 🤔

[morganwdavis]

installing the sqlite-object-cache plugin along with this one, fixes the issue

This made no difference for me. Errors persist still on every site interaction. In case it matters, this site is (unfortunately) still running PHP 7.4.33 due to legacy PHP apps. All other aspects of WordPress 6.5.3 are functional, however.

[OllieJones]

If you use a persistent object cache (presumably any of them, not just the SQLite one) transients are stored there, and not in the main database. That accounts for this problem being concealed when such a cache is active. This code path runs a cronjob, and so sets the "doing_cron" transient.

And, the offending query here is INSERT ... ON DUPLICATE KEY UPDATE... I can't see from the traceback whether that gets translated to SQLite's version of that syntax.

[OllieJones]

Is it possible the SQLite version on @morganwdavis 's server/php installation is prior to 3.24? If so, it will gack on SQLite's UPSERT (ON CONFLICT) syntax. https://www.plumislandmedia.net/reference/sqlite3-in-php-some-notes/#upsert Just a thought.

[morganwdavis]

Is it possible the SQLite version on @morganwdavis 's server/php installation is prior to 3.24?

No. sqlite3 —version reports 3.45.1

With the PHP base version being 7.4.33 (same for all the PHP extensions). I might try lifting the site over to a newer server with the latest of everything and see if the errors persist.

[morganwdavis]

I found the fix for this. Closing this and creating a new issue.