Skip to content

WooCommerce indexes created as fields when installing WooCommerce from Studio, causing push failures for syntax errors #1103

Description

@mgozdis

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

  1. Start Studio app
  2. Add a new site
  3. Install WooCommerce
  4. Check the schema in SQLite with .schema wp_wc_download_log and see it's incorrect
  5. 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.

Metadata

Metadata

Assignees

Labels

Type

Projects

Status
Done

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions