Query execution was interrupted in ALTER TABLE

I forgot… Upload ID is Xdh7tZ2M

Thanks for the report. Your error is generated by MariaDB. Your hosting service has configured their MariaDB server with a limit on how long a SQL statement can run. Specifically, it’s controlled by the max_statement_time variable and is set to 30 seconds on your system (according to your uploaded metadata).

Sometimes the ALTER TABLE statement to add high-performance keys can run for a long time, depending on the size of the table and the load on the server. Unfortunately your ALTER query for your wp_postmeta table took longer than the limit, so MySQL stopped the statement. No damage was done; the old indexes were unchanged.

You can try this sequence of shell commands if you’re on Linux and have access to wp-cli. This temporarily sets the statement time limit to an hour, then runs the re-keying operation.

echo "SET max_statement_time = 3600;" >/tmp/sql$$
wp index-mysql enable wp_postmeta --dryrun >>/tmp/sql$$
wp db query </tmp/sql$$

If that doesn’t work you could ask your hosting provider to increase the limit for you. The limit increase can be temporary. Or, maybe one of their database engineers can run the statement for you from a privileged MySQL account. It is this:

ALTER TABLE wp_postmeta
ADD UNIQUE KEY meta_id (meta_id),
DROP PRIMARY KEY,
ADD PRIMARY KEY (post_id, meta_key, meta_id),
DROP KEY meta_key,
ADD KEY meta_key (meta_key, meta_value(32), post_id, meta_id),
ADD KEY meta_value (meta_value(32), meta_id),
DROP KEY post_id;

rocket.net’s market position is high performance, so they should be willing to work this out for you. But this is not the kind of thing you can expect a front-line support person to understand; you’ll need to get them to escalate the trouble ticket to a database engineer.

• This reply was modified 1 year, 9 months ago by OllieJones.
• This reply was modified 1 year, 9 months ago by OllieJones.
• This reply was modified 1 year, 9 months ago by OllieJones.

This will be fixed in the next release. See https://github.com/OllieJones/index-wp-mysql-for-speed/issues/56