Problems Rekeying wp_postmeta

Resolved

(@chrisdperry91)

Hello, I have a site running woocommerce with products in the 10s of thousands with variations, custom post meta, etc… so I am excited to try this plugin and see if it can help speed up the admin.

While installing the plugin the database was able to upgrade to InnoDB just fine, but while rekeying the site crashed and upon re-opening the plugin settings I received this error.

We cannot rekey some tables.
This often means they have already been rekeyed by some other plugin or workflow.

wp_postmeta	
wp_postmeta has unexpected keys, so we cannot rekey it.
  Cannot find the expected key PRIMARY KEY.
  The key meta_id exists when it should not.
  Found an unexpected definition for key meta_id. It should be , but is ADD UNIQUE KEY meta_id (meta_id).
  Cannot find the expected key PRIMARY KEY.
  Found an unexpected definition for key meta_key. It should be ADD KEY meta_key (meta_key, post_id), but is ADD KEY meta_key (meta_key(191)).

Let me know if you need anything from me, like I said really looking forward to this plugin finally making a difference. Thanks!

Viewing 8 replies - 16 through 23 (of 23 total)

← 1 2

Plugin Author OllieJones
(@olliejones)

3 years ago
@josem3784 It looks to me like your key situation on wp_postmeta is different from the one presented at the top of this ticket.

If you use phpmyadmin to give the SQL command SHOW CREATE TABLE wp_postmeta and paste that information here I will be able to figure out what’s wrong and offer you some commands to fix it.

An update to the plugin designed to straighten out these issues is coming soon.
- This reply was modified 3 years ago by OllieJones.
josem3748
(@josem3748)

3 years ago

@olliejones

Table

wp_postmeta

Create Table

CREATE TABLE wp_postmeta (
meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
post_id bigint(20) unsigned NOT NULL DEFAULT 0,
meta_key varchar(191) COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
meta_value longtext COLLATE utf8mb4_unicode_520_ci DEFAULT NULL,
PRIMARY KEY (meta_id),
KEY post_id (post_id),
KEY meta_key (meta_key)
) ENGINE=InnoDB AUTO_INCREMENT=1784730 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci
Plugin Author OllieJones
(@olliejones)

3 years ago
Your table’s definition varies from the WP standard in two ways. Neither change was made by this plugin. Your PRIMARY KEY and post_id key both match the WordPress standard.

As you mentioned above, the standard definition has meta_key VARCHAR(250), not 191. This plugin makes no changes to tables’ data definitions, only to keys (a/k/a indexes).

And it has its meta_key index defined without the (191) prefix.

This plugin did not make those changes. Still, you can change your keys back to the WordPress standard definition with these commands.
```
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));
```
I think it’s necessary to give this warning. Avoid using the ALTER TABLE command in MySQL unless you really know what you’re trying to do. And, always always test those commands on a test database you don’t mind wrecking. We, this plugin’s developers, run lots of WordPress instances and MySQL databases on virtual machines that cost nothing to create or destroy. If we make mistakes in development or test, it costs us nothing.
- This reply was modified 3 years ago by OllieJones.
Plugin Author OllieJones
(@olliejones)

3 years ago

What’s up with those prefix keys like ADD KEY meta_key (meta_key(191))?

In the MyISAM-only days of MySQL (the days when WordPress became wildly popular) there was a limit of 767 bytes (not characters) on indexes. Because of the use of utfmb4, that means the index length was limited to 191 characters (up to four bytes/character). A prefix index is still a useful index: queries can use it to get partial matches, then check their matches against the entire column.

But they’re not useful in covering indexes, where the query engine actually gets its result from the index, not the table itself.

The first releases of InnoDB had the same limitation. But, starting with MySQL 5.7, and also in some later point releases of 5.6 the new InnoDB Barracuda engine removed the limitation. This plugin checks carefully for the presence of the Barracuda engine before it rewrites indexes without prefixes. Indexes without prefixes *can* be used in covering indexes, so we add some covering indexes for common queries.
josem3748
(@josem3748)

3 years ago
Hi @olliejones thanks for the reply.

Just to be clear, if I make a database backup and then run the commands you gave me:
```
ALTER TABLE wp_postmeta DROP KEY meta_key;
ALTER TABLE wp_postmeta ADD KEY meta_key (meta_key(191));
```
Then I will be able to add keys to postmeta from the plugin, Is that correct? Does your plugin will work with meta_key VARCHAR 191?

We changed the meta_key VARCHAR in order to improve performance following the advice of this page:

https://www.spectodesign.com/thoughts/ideas/fixing-slow-woocommerce/
Plugin Author OllieJones
(@olliejones)

3 years ago

Thanks for that pointer. I don’t believe that’s good advice from SpectoDesign in 2021. I added to the Core ticket about the issue here. https://core.trac.wordpress.org/ticket/33885#comment:86

If your MySQL / MariaDB version is 5.7 or beyond, our plugin will replace the prefix indexes meta_key(meta_key(191)) with non-prefix indexes meta_key(meta_key).

I think our plugin will work on your system if you make the index changes I suggested.

underclass
(@underclass)

3 years ago

@olliejones you need to make sure your backticks are in the right places, it’s not possible to post them here correctly, as the rich editor uses them to style the post.

Brian Brown, Ph.D.
(@brianbrown)

2 years, 10 months ago

Hi Ollie & all!
The 191 character limit is being caused by the WP-Optimize (https://wordpress.org/plugins/wp-optimize/) “Power Tweaks” setting:

By default, searches on the WordPress “post meta” database table are significantly slower than necessary because the table permits very rarely-used key sizes of longer than 191 characters, which prevents indexing the table. This tweak checks if anything in your database uses such long keys, and if not, creates an index by lowering the limit down to 191 characters.

Changes the table scheme for your postmeta table by reducing the maximum length of the meta_key field down to 191 characters (if nothing already exists longer than that).

Regards,
Brian

Viewing 8 replies - 16 through 23 (of 23 total)

← 1 2

The topic ‘Problems Rekeying wp_postmeta’ is closed to new replies.