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 15 replies - 1 through 15 (of 23 total)

1 2 →

Thread Starter chrisdperry91
(@chrisdperry91)

4 years, 9 months ago

So I ran the installation again. This time it only took about a minute, no site crash, and I get the success message along the lines of “High-performance keys added to 5 tables.”

The page is still showing the same error as before below in red, wondering if the wp_postmeta key was successfully updated this time and the error is from the first attempt? Or if there is still an issue. Is there a way for me to check? Thanks.

underclass
(@underclass)

4 years, 9 months ago

@chrisdperry91 I’m having the same issue, my install was clean, so not sure it’s that, there’s reports of the same issue in another support post for a different table.

josem3748
(@josem3748)

4 years, 9 months ago

Same here.

The only relevant change I made was changing meta_key (post_meta) from VARCHAR(255) to VARCHAR(191)
Plugin Author OllieJones
(@olliejones)

4 years, 9 months ago
Thanks for the bug report. Please see my response in the next post.
- This reply was modified 4 years, 9 months ago by OllieJones.
- This reply was modified 4 years, 9 months ago by OllieJones.
Plugin Author OllieJones
(@olliejones)

4 years, 9 months ago

Thanks for the bug report.

You say “while rekeying the site crashed”. Please tell me a bit more about what that crash looked like if you know.

I believe your table still functions correctly. (If not please let me know!)

Explanation

The MySQL commands used to rekey wp_postmeta are:

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

The error message you received means something timed out during the third statement (...ADD PRIMARY KEY...) the first time you attempted to rekey your large wp_postmeta table.

Fix

I’m working on a fix. In the meantime you can restore your table to its original condition with these two commands (you can put them in via phpmyadmin).

ALTER TABLE wp_postmeta ADD PRIMARY KEY (meta_id); ALTER TABLE wp_postmeta DROP KEY meta_id;

The first of those commands restores WordPress’s default primary key. The second gets rid of the meta_id created by this plugin.
underclass
(@underclass)

4 years, 9 months ago
Hi @olliejones

I’ve been trying to use this operation from your other posts, but keep running into the error:

ALTER TABLE 10_postmeta ADD UNIQUE KEY meta_id (meta_id)

MySQL said: Documentation

#1061 – Duplicate key name ‘meta_id’

My tables have a different naming convention to standard wp_, I also add in backticks around table names to get it to work up to this error. Apologies, I’m not a great user of MySQL, so am a little unsure of what to do, and appreciate your help 🙂
- This reply was modified 4 years, 9 months ago by underclass.
underclass
(@underclass)

4 years, 9 months ago
I ran the fix in MySQL:
```
ALTER TABLE wp_postmeta ADD PRIMARY KEY (meta_id);
ALTER TABLE wp_postmeta DROP KEY meta_id;
```
Then went to wp-admin and used the plugin and it’s now working, and the tables indexed 🙂
Thread Starter chrisdperry91
(@chrisdperry91)

4 years, 9 months ago
The server hung and had to reboot during the first run. Last week I was doing some major site and server maintenance to address some issues I had been constantly having with the site, timeouts, 500 errors etc… Looking much better now so I’m confident I could make whatever changes issue free.

The second run completed just fine (i think) I got a success message along the lines of “high performance keys were added”. So I’m wondering if the error message I’m seeing is left over from the first attempt when the server went down midway.

The site runs fine now, much better than it used to in-fact, so If there is still an unresolved issue from the re-key I am not seeing any effect of it, however I would like to confirm the process went smoothly (the second time at least). Is there I way I can check my database to see if it everything is where it needs to be? Should I restore to default and try again? Wait for a plugin update?

Thanks for all your help!
- This reply was modified 4 years, 9 months ago by chrisdperry91.
Thread Starter chrisdperry91
(@chrisdperry91)

4 years, 9 months ago

https://ibb.co/P6hp3kN
https://ibb.co/Z146jND

What my plugin page looks like currently.
Plugin Author OllieJones
(@olliejones)

4 years, 9 months ago
Thanks for the further investigation.

You’re right, your wp_postmeta problem is left over from the first time you tried to add high-perf keys to that table.

Your postmeta keying problem can be solved with the two MySQL commands here. https://ww.wp.xz.cn/support/topic/problems-rekeying-wp_postmeta/#post-14795734

Once you have done that you should be able to use the plugin to add high-perf keys to that table.
- This reply was modified 4 years, 9 months ago by OllieJones.
- This reply was modified 4 years, 9 months ago by OllieJones.
Thread Starter chrisdperry91
(@chrisdperry91)

4 years, 9 months ago

Turns out it actually did 504 again while altering postmeta. The table is 2.4GB Would I be able to just run the commands individually to re-key wp_postmeta from phpmyadmin?

Thread Starter chrisdperry91
(@chrisdperry91)

4 years, 9 months ago

Rebooted the server, logged back in and I have a success message “high performance keys added to 1 table” no error messages, and all tables have the options to revert. Looks like it was able to finish this time.

Plugin Author OllieJones
(@olliejones)

4 years, 9 months ago

Yes, you can do your reindexing from phpmyadmin (or any MySQL client).

I’m working on adding a wp-cli command set to do this from the command line. Because it takes so long n large installations like yours.

I’m happy you got it to work. Thanks for your patience.
josem3748
(@josem3748)

4 years, 9 months ago
@underclass having the same problem

I can’t run the operation in MySQL. How did you solve it?

ALTER TABLEwp_postmetaADD PRIMARY KEY (meta_id);
ALTER TABLE wp_postmeta DROP KEY meta_id;

#1068 – Multiple primary key defined
- This reply was modified 4 years, 9 months ago by josem3748.
josem3748
(@josem3748)

4 years, 9 months ago

Hi @olliejones

I can’t run the operation in MySQL.

ALTER TABLE wp_postmetaADD PRIMARY KEY (meta_id);
ALTER TABLE wp_postmeta DROP KEY meta_id;

#1068 – Multiple primary key defined

Any idea on what could be happening?

Viewing 15 replies - 1 through 15 (of 23 total)

1 2 →

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