Problems Rekeying wp_postmeta

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.

@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.

Same here.

The only relevant change I made was changing meta_key (post_meta) from VARCHAR(255) to VARCHAR(191)

Thanks for the bug report. Please see my response in the next post.

• This reply was modified 3 years, 3 months ago by OllieJones.
• This reply was modified 3 years, 3 months ago by OllieJones.

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.

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 3 years, 3 months ago by underclass.

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 ??

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 3 years, 3 months ago by chrisdperry91.

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

What my plugin page looks like currently.

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://www.ads-software.com/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 3 years, 3 months ago by OllieJones.
• This reply was modified 3 years, 3 months ago by OllieJones.

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?

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.

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.

@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 3 years, 3 months ago by josem3748.

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?