Quick summary
We have a recent report of a user who is unable to sync their Studio site to WordPress.com: 9548655-zd-a8c
The issue is that when installing WooCommerce from Studio, it does not create table indexes correctly. In this specific case, the wp_wc_download_log table gets created with the following schema:
CREATE TABLE IF NOT EXISTS "wp_wc_download_log" (
"download_log_id" integer PRIMARY KEY AUTOINCREMENT NOT NULL ON CONFLICT REPLACE DEFAULT 0,
"timestamp" text NOT NULL ON CONFLICT REPLACE DEFAULT '' COLLATE NOCASE,
"permission_id" integer NOT NULL ON CONFLICT REPLACE DEFAULT 0,
"user_id" integer,
"user_ip_address" text DEFAULT '' COLLATE NOCASE,
"KEY" text COLLATE NOCASE);
CREATE INDEX "wp_wc_download_log__permission_id" ON "wp_wc_download_log" ("permission_id");
Note that it's creating a field as "KEY" when there should be an index for the timestamp field as shown here:
CREATE TABLE wp_wc_download_log (
download_log_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
timestamp datetime NOT NULL,
permission_id bigint(20) unsigned NOT NULL DEFAULT 0,
user_id bigint(20) unsigned DEFAULT NULL,
user_ip_address varchar(100) DEFAULT '',
PRIMARY KEY (download_log_id),
KEY timestamp (timestamp),
KEY permission_id (permission_id)
);
We believe this may be due to some differences between MySQL/MariaDB and SQLite but are unsure. It could be an issue with WooCommerce directly when it creates the tables. We were able to reproduce in Studio directly.
If syncing an existing site from WordPress.com that has WooCommerce already installed, the schema appears correct. There is a P2 here as well: p9F6qB-hpT-p2
Steps to reproduce
- Start Studio app
- Add a new site
- Install WooCommerce
- Check the schema in SQLite with
.schema wp_wc_download_log and see it's incorrect
- Attempt to sync the site to WordPress.com and get failures for the following (can likely only check this in logs):
error: Error: Unable to stream /tmp/jp-restore-2074643-main-5jncc6/vp-restore-sql/wp_wc_download_log.sql.ndjson : Error: Error while streaming NDJSON SQL in uploadSqlFromStream(): [DB] Error while streaming: Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'timestamp),
PRIMARY KEY (`download_log_id`),
KEY `permission_id` (`permissi...' at line 7
Exporting the database also shows the statement is incorrect like this:
CREATE TABLE `wp_wc_download_log` (
`download_log_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`timestamp` datetime NOT NULL,
`permission_id` bigint(20) unsigned NOT NULL DEFAULT 0,
`user_id` bigint(20) unsigned,
`user_ip_address` varchar(100) DEFAULT '',
`KEY` timestamp(timestamp),
PRIMARY KEY (`download_log_id`),
KEY `permission_id` (`permission_id`)
);
What you expected to happen
We expect the WooCommerce tables to be created with proper indexes.
What actually happened
Indexes for at least wp_wc_download_log are created incorrectly, causing sync failures.
Impact
Some (< 50%)
Available workarounds?
Yes, easy to implement
Platform
No response
Logs or notes
Workarounds may involve modifying the table in SQLite directly prior to syncing, but this has not been tested yet to see if a sync is successful after.
Quick summary
We have a recent report of a user who is unable to sync their Studio site to WordPress.com: 9548655-zd-a8c
The issue is that when installing WooCommerce from Studio, it does not create table indexes correctly. In this specific case, the
wp_wc_download_logtable gets created with the following schema:Note that it's creating a field as "KEY" when there should be an index for the
timestampfield as shown here:We believe this may be due to some differences between MySQL/MariaDB and SQLite but are unsure. It could be an issue with WooCommerce directly when it creates the tables. We were able to reproduce in Studio directly.
If syncing an existing site from WordPress.com that has WooCommerce already installed, the schema appears correct. There is a P2 here as well: p9F6qB-hpT-p2
Steps to reproduce
.schema wp_wc_download_logand see it's incorrectExporting the database also shows the statement is incorrect like this:
What you expected to happen
We expect the WooCommerce tables to be created with proper indexes.
What actually happened
Indexes for at least
wp_wc_download_logare created incorrectly, causing sync failures.Impact
Some (< 50%)
Available workarounds?
Yes, easy to implement
Platform
No response
Logs or notes
Workarounds may involve modifying the table in SQLite directly prior to syncing, but this has not been tested yet to see if a sync is successful after.