SQLite plugin not authenticating on SQLite 3.34.1

[sejas]

When using this plugin with SQLite 3.34.1 driver and WP_SQLITE_AST_DRIVER and WP_SQLITE_UNSAFE_ENABLE_UNSUPPORTED_VERSIONS enabled I cannot login in wp-admin. Here are the errors thrown in debug.log

<li>Executing: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) SELECT CAST(`column1` AS TEXT), CAST(`column2` AS TEXT), CAST(`column3` AS TEXT) FROM (VALUES ( &#039;_transient_doing_cron&#039; , &#039;1765558549.9779899120330810546875&#039; , &#039;on&#039; )) WHERE true ON CONFLICT DO UPDATE SET  `option_name` = CAST(`excluded`.`option_name` AS TEXT), `option_value` = CAST(`excluded`.`option_value` AS TEXT), `autoload` = CAST(`excluded`.`autoload` AS TEXT) | (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;">
SQLSTATE[HY000]: General error: 1 near "UPDATE": syntax error
</div>
<p>Backtrace:</p>
<pre>#0 /srv/htdocs/wp-content/mu-plugins/sqlite-database-integration/wp-includes/sqlite-ast/class-wp-sqlite-driver.php(832): WP_SQLite_Driver->new_driver_exception('SQL...', 'HY0...', Object(PDOException))
#1 /srv/htdocs/wp-content/mu-plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php(537): WP_SQLite_Driver->query('INS...')
#2 /srv/htdocs/wp-content/mu-plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php(449): WP_SQLite_DB->_do_query('INS...')
#3 /srv/htdocs/__wp__/wp-includes/option.php(1143): WP_SQLite_DB->query('INS...')
#4 /srv/htdocs/__wp__/wp-includes/option.php(1554): add_option('_tr...', '176...', '', 'on')
#5 /srv/htdocs/__wp__/wp-includes/cron.php(931): set_transient('doi...', '176...')
#6 /srv/htdocs/__wp__/wp-includes/cron.php(1052): spawn_cron(1765558549.978)
#7 /srv/htdocs/__wp__/wp-includes/class-wp-hook.php(341): _wp_cron('')
#8 /srv/htdocs/__wp__/wp-includes/class-wp-hook.php(365): WP_Hook->apply_filters(NULL, Array)
#9 /srv/htdocs/__wp__/wp-includes/plugin.php(522): WP_Hook->do_action(Array)
#10 /srv/htdocs/__wp__/wp-includes/load.php(1308): do_action('shu...')
#11 [internal function]: shutdown_action_hook()
#12 {main}</pre>
 for query INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES ('_transient_doing_cron', '1765558549.9779899120330810546875', 'on') ON DUPLICATE KEY UPDATE `option_name` = VALUES(`option_name`), `option_value` = VALUES(`option_value`), `autoload` = VALUES(`autoload`) /*  antoniosejas-jaqpx-studio.wp.build/wp-login.php?redirect_to=https%3A%2F%2Fantoniosejas-jaqpx-studio.wp.build%2Fwp-admin%2F&reauth=1 request_id: 5c56fd6603d3fc5b3618e9ca9f29ce0b */ made by shutdown_action_hook, do_action('shutdown'), 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 

@JanJakes , do you know if there is a way to fix the issue?

[JanJakes]

Hi @sejas, thanks for reporting this!

After some debugging and searching, I think I found the issue:

---

It seems that older SQLite versions don't support ON CONFLICT, but require ON CONFLICT (columns) instead. That's not great, because the original MySQL query doesn't specify the columns, but I suppose we could try to fix such cases using an information schema lookup. That said, it may still be unsolvable for cases when the table contains multiple unique columns 🤔

[sejas]

@JanJakes , thanks for taking a look to the issue. If it's possible it would be great to fix that case to fix the login of preview sites.
That being said, I think we need to move away from using SQLite on cloud servers (preview sites).