sqlite-database-integration Errors running WooCommerce

I've been testing new implementation with WooCommerce, as it's both useful to me and quite elaborate when it comes to database operations.

So far, so good!

I've stumbled upon some minor errors, which seeming doesn't affect overall user experience, but this may lead to further problems.

At first, after WooCommerce activation I've been welcomed (already with SQLite database installed) by white screen with error. I truncated the output a bit, as it's illegible in its full form, but attached it also as gist)

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 (***loads of values***) 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: SELECT * FROM pragma_index_info(&quot;wp_options__option_name&quot;) as l; | (no parameters)</li> <li>Executing: INSERT INTO `wp_options` (`option_name`, `option_value`, `autoload`) VALUES (:param0 , :param1 , :param2 ) ON CONFLICT (&quot;option_name&quot;) DO UPDATE SET `option_name` = excluded.`option_name`, `option_value` = excluded.`option_value`, `autoload` = excluded.`autoload` | parameters: ***previous values as 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 2881 in Function <code>handle_error</code>. Error message was: Problem preparing the PDO SQL Statement.** Error was: SQLSTATE[HY000]: General error: 5 database is locked.** trace: #0 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(2992): PDOStatement->execute(Array) #1 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(1511): WP_SQLite_Translator->execute_sqlite_query('INSERT INTO `wp...', Array) #2 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(649): WP_SQLite_Translator->execute_insert_or_replace() #3 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(502): WP_SQLite_Translator->execute_mysql_query('INSERT INTO `wp...') #4 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php(282): WP_SQLite_Translator->query('INSERT INTO `wp...') #5 /var/www/html/wp-includes/option.php(664): WP_SQLite_DB->query('INSERT INTO `wp...') #6 /var/www/html/wp-includes/option.php(972): add_option('_transient_wooc...', Array, '', 'no') #7 /var/www/html/wp-content/plugins/woocommerce/src/Admin/DataSourcePoller.php(143): set_transient('woocommerce_adm...', Array, 604800) #8 /var/www/html/wp-content/plugins/woocommerce/src/Admin/DataSourcePoller.php(112): Automattic\WooCommerce\Admin\DataSourcePoller->read_specs_from_data_sources() #9 /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/RemoteFreeExtensions/Init.php(72): Automattic\WooCommerce\Admin\DataSourcePoller->get_specs_from_data_sources() #10 /var/www/html/wp-content/plugins/woocommerce/src/Internal/Admin/RemoteFreeExtensions/Init.php(33): Automattic\WooCommerce\Internal\Admin\RemoteFreeExtensions\Init::get_specs() #11 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/Tasks/Marketing.php(84): Automattic\WooCommerce\Internal\Admin\RemoteFreeExtensions\Init::get_extensions(Array) #12 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/Tasks/Marketing.php(73): Automattic\WooCommerce\Admin\Features\OnboardingTasks\Tasks\Marketing::get_plugins() #13 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/TaskList.php(304): Automattic\WooCommerce\Admin\Features\OnboardingTasks\Tasks\Marketing->can_view() #14 [internal function]: Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskList->Automattic\WooCommerce\Admin\Features\OnboardingTasks\{closure}(Object(Automattic\WooCommerce\Admin\Features\OnboardingTasks\Tasks\Marketing)) #15 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/TaskList.php(305): array_filter(Array, Object(Closure)) #16 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/TaskList.php(239): Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskList->get_viewable_tasks() #17 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/TaskLists.php(445): Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskList->is_complete() #18 /var/www/html/wp-content/plugins/woocommerce/src/Admin/Features/OnboardingTasks/TaskLists.php(467): Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskLists::setup_tasks_remaining() #19 /var/www/html/wp-includes/class-wp-hook.php(308): Automattic\WooCommerce\Admin\Features\OnboardingTasks\TaskLists::menu_task_count('') #20 /var/www/html/wp-includes/class-wp-hook.php(332): WP_Hook->apply_filters(NULL, Array) #21 /var/www/html/wp-includes/plugin.php(517): WP_Hook->do_action(Array) #22 /var/www/html/wp-admin/includes/menu.php(155): do_action('admin_menu', '') #23 /var/www/html/wp-admin/menu.php(428): require_once('/var/www/html/w...') #24 /var/www/html/wp-admin/admin.php(158): require('/var/www/html/w...') #25 /var/www/html/wp-admin/plugins.php(10): require_once('/var/www/html/w...') #26 {main}. </div>***truncated backtrace***

Another error found at order edit page (after successful submission):

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>SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT BETWEEN '_' AND '_z' HAVING meta_key NOT LIKE '_%' ORDER BY meta_key LIMIT 30</p> <p>Queries made or created this session were:</p> <ol> <li>Executing: BEGIN | (no parameters)</li> <li>Executing: SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT BETWEEN :param0 AND :param1 HAVING meta_key NOT LIKE :param2 ORDER BY meta_key LIMIT 30 | parameters: _, _z, _%</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 2881 in Function <code>handle_error</code>. Error message was: Problem preparing the PDO SQL Statement. Error was: SQLSTATE[HY000]: General error: 1 a GROUP BY clause is required before HAVING. trace: #0 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(2991): PDO->prepare('SELECT DISTINCT...') #1 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(1320): WP_SQLite_Translator->execute_sqlite_query('SELECT DISTINCT...', Array) #2 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(644): WP_SQLite_Translator->execute_select() #3 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-translator.php(502): WP_SQLite_Translator->execute_mysql_query('SELECT DISTINCT...') #4 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php(282): WP_SQLite_Translator->query('SELECT DISTINCT...') #5 /var/www/html/wp-includes/class-wpdb.php(2816): WP_SQLite_DB->query('SELECT DISTINCT...') #6 /var/www/html/wp-admin/includes/template.php(701): wpdb->get_col('SELECT DISTINCT...') #7 /var/www/html/wp-admin/includes/meta-boxes.php(776): meta_form(Object(WP_Post)) #8 /var/www/html/wp-admin/includes/template.php(1409): post_custom_meta_box(Object(WP_Post), Array) #9 /var/www/html/wp-admin/edit-form-advanced.php(688): do_meta_boxes(Object(WP_Screen), 'normal', Object(WP_Post)) #10 /var/www/html/wp-admin/post.php(206): require('/var/www/html/w...') #11 {main}. </div> <p>Backtrace:</p> <pre>#0 /var/www/html/wp-content/plugins/sqlite-database-integration/wp-includes/sqlite/class-wp-sqlite-db.php(289): WP_SQLite_Translator-&gt;get_error_message() #1 /var/www/html/wp-includes/class-wpdb.php(2816): WP_SQLite_DB-&gt;query('SELECT DISTINCT...') #2 /var/www/html/wp-admin/includes/template.php(701): wpdb-&gt;get_col('SELECT DISTINCT...') #3 /var/www/html/wp-admin/includes/meta-boxes.php(776): meta_form(Object(WP_Post)) #4 /var/www/html/wp-admin/includes/template.php(1409): post_custom_meta_box(Object(WP_Post), Array) #5 /var/www/html/wp-admin/edit-form-advanced.php(688): do_meta_boxes(Object(WP_Screen), 'normal', Object(WP_Post)) #6 /var/www/html/wp-admin/post.php(206): require('/var/www/html/w...') #7 {main}</pre> ] SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT BETWEEN '_' AND '_z' HAVING meta_key NOT LIKE '_%' ORDER BY meta_key LIMIT 30

Mar 03 '23 08:03 bart-jaskulski

Notes: UPSERT a/k/a ON DUPLICATE KEY UPDATE

This has different syntax on SQLite, and doesn't exist at all prior to version 3.24.

SELECT DISTINCT meta_key FROM wp_postmeta WHERE meta_key NOT BETWEEN '_' AND 'z' HAVING meta_key NOT LIKE '%' ORDER BY meta_key LIMIT 30

SQLlite's complaint for this is " a GROUP BY clause is required before HAVING"

Mar 04 '23 22:03 OllieJones

It's `NOT LIKE '_%' . That is, it's NOT LIKE any string that begins with an underscore.

Mar 13 '23 21:03 OllieJones

It's `NOT LIKE '_%' . That is, it's NOT LIKE any string that begins with an underscore.

We've discussed this on WP.org slack, here's the summary:

translate_expression sounds like a good place to make this change
translate_regexp_function could be a good boilerplate
Check if the current token matches LIKE as SQL keyword. $this->last_reserved_keyword could be useful to process expressions more complex than just a string literal.
There is no single function to process an expression and there is no syntax tree. The code operates on a stream of tokens.
Correctly processing all possible syntaxes, e.g. subqueries, would require introducing a tree parser after all.
The best we can do is to solve the easy case of a string or a function call and hope the difficult case with a subquery won’t come up. So far that was enough.

Mar 14 '23 16:03 adamziel

I believe this has now been fixed? 🤔

Jul 19 '23 09:07 aristath

woocommerce payment not work

Oct 25 '23 12:10 I-O-x-O-I

Are there any plans to support versions prior to sqlite3.24? In version 3.16, there seem to be many problems with the operations of the wp_options table.

MySQL Query：

INSERT INTO wp_options (option_name, option_value, autoload) VALUES ('widget_links', 'a:1:{s:12:"_multiwidget";i:1;}', 'yes') ON DUPLICATE KEY UPDATE option_name = VALUES(option_name), option_value = VALUES(option_value), autoload = VALUES(autoload)

SQLite Query：

INSERT INTO wp_options (option_name, option_value, autoload) VALUES (:param0 , :param1 , :param2 ) ON CONFLICT ("option_name") DO UPDATE SET option_name = excluded.option_name, option_value = excluded.option_value, autoload = excluded.autoload | parameters: widget_links, a:1:{s:12:"_multiwidget";i:1;}, yes

Error Message

SQLSTATE[HY000]: General error: 1 near "ON": syntax error.

Jan 20 '24 03:01 ianzhi

@ianzhi tell me more about your use-case – is there anything blocking you from upgrading to SQLite 3.24? Also, what would that query would look like on SQLite 3.16?

Jan 26 '24 10:01 adamziel

@ianzhi tell me more about your use-case – is there anything blocking you from upgrading to SQLite 3.24? Also, what would that query would look like on SQLite 3.16?

https://www.sqlite.org/lang_upsert.html I don't know what should be provided, I checked the documentation of SQLite and found that the INSERT INTO ... ON CONFLICT ... syntax seems to be part of UPSERT and is only provided after SQLite version 3.24.0, so if using SQLite version earlier than 3.24.0, should all encounter this problem.

Jan 30 '24 03:01 ianzhi

Thank you for this additional context @ianzhi! This could be refactored into an UPDATE query and an INSERT query on earlier SQLite versions. However, before this plugin may work with multiple SQLite versions, it needs to work reliably with a single version – and there are still missing parts that will take time to implement. Is there anything stopping you from upgrading to SQLite 3.24?

Jan 30 '24 08:01 adamziel

Thank you for this additional context @ianzhi! This could be refactored into an UPDATE query and an INSERT query on earlier SQLite versions. However, before this plugin may work with multiple SQLite versions, it needs to work reliably with a single version – and there are still missing parts that will take time to implement. Is there anything stopping you from upgrading to SQLite 3.24?

Thank you very much for your reply. I'm running WordPress using an environment like Amazon Lambda. If I want to update the version of SQLite, I may have to use a self-built image. I suspect this will affect the cold start time. Of course, it is not a big problem. I just want to know if there is Plans to support older versions of SQLite. I used Google Translate for my reply. I'm not sure it expresses my meaning correctly. Please don't mind if it offends you.

Feb 03 '24 03:02 ianzhi

Of course, it is not a big problem. I just want to know if there is Plans to support older versions of SQLite.

I'm not aware of such plans at the moment, perhaps @aristath would have more context to offer.

Mar 04 '24 14:03 adamziel

Upgrading the version of SQLite used in the php extension is difficult or impossible for the customers of must hosting providers. The most likely way to make it happen is to push forward to a more recent php version.

A BEGIN / UPDATE / if ( 0 === $sqlite->changes() ) { INSERT } / COMMIT sequence is surprisingly efficient for the older versions, for what it's worth.

Mar 05 '24 13:03 OllieJones