Duplicate key name ‘meta_value’

I’m sorry to hear you’re having trouble. To help me diagnose the problem, please upload your database metadata. In your dashboard, visit Tools > Index MySQL then look at the About tab. There’s a button to upload your data. Please let me know your upload id.

Hi Ollie,
sure – here you go: QQzXcjw9

Thanks
Markus

It looks like you have been experimenting with various keys on your WordPress tables. Could you please try adding the high-perf keys to your tables one at a time? The error message you showed me could come from postmeta, usermeta, or termmeta.

If you can do this with WPCLI, that would be even better. The commands you need are:

wp index-mysql enable co1uw_postmeta
wp index-mysql enable co1uw_usermeta
wp index-mysql enable co1uw_termmeta

If you get errors from any of these please let me know.

Hi Ollie,

here you go:

[22-08-23 9:32]xxx:~% wp index-mysql enable co1uw_postmeta
Index WP MySQL For Speed 1.4.6
Versions  Plugin:1.4.6 MySQL:10.4.26-MariaDB-1:10.4.26+maria~ubu1804 WordPress:6.0.1 WordPress database:53496 php:7.4.30
WordPress-Datenbank-Fehler Duplicate key name 'meta_value' für Abfrage ALTER TABLE co1uw_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/**imfs-query-tag*514074386*/ von include('phar:///usr/bin/wp-cli.phar/php/boot-phar.php'), include('phar:///usr/bin/wp-cli.phar/vendor/wp-cli/wp-cli/php/wp-cli.php'), WP_CLI\bootstrap, WP_CLI\Bootstrap\LaunchRunner->process, WP_CLI\Runner->start, WP_CLI\Runner->run_command_and_exit, WP_CLI\Runner->run_command, WP_CLI\Dispatcher\Subcommand->invoke, call_user_func, WP_CLI\Dispatcher\CommandFactory::WP_CLI\Dispatcher\{closure}, call_user_func, ImsfCli->enable, ImsfCli->doRekeying, ImfsDb->rekeyTables, ImfsDb->rekeyTable, ImfsDb->query
PHP Fatal error:  Uncaught ImfsException: [0]: Duplicate key name 'meta_value' in ALTER TABLE co1uw_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/**imfs-query-tag*514074386*/

  thrown in /home/wp/disk/wordpress/wp-content/plugins/index-wp-mysql-for-speed/code/imsfdb.php on line 339
Fatal error: Uncaught ImfsException: [0]: Duplicate key name 'meta_value' in ALTER TABLE co1uw_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/**imfs-query-tag*514074386*/

  thrown in /home/wp/disk/wordpress/wp-content/plugins/index-wp-mysql-for-speed/code/imsfdb.php on line 339
Error: Es gab einen kritischen Fehler auf deiner Website.Erfahre mehr über die Problembehandlung in WordPress. Es gab einen kritischen Fehler auf deiner Website.

[22-08-23 9:32]xxx:~% wp index-mysql enable co1uw_usermeta
Index WP MySQL For Speed 1.4.6
Versions  Plugin:1.4.6 MySQL:10.4.26-MariaDB-1:10.4.26+maria~ubu1804 WordPress:6.0.1 WordPress database:53496 php:7.4.30
Error: These tables are not found or not eligible to enable: co1uw_usermeta.

[22-08-23 9:32]xxx:~% wp index-mysql enable co1uw_termmeta
Index WP MySQL For Speed 1.4.6
Versions  Plugin:1.4.6 MySQL:10.4.26-MariaDB-1:10.4.26+maria~ubu1804 WordPress:6.0.1 WordPress database:53496 php:7.4.30
Error: These tables are not found or not eligible to enable: co1uw_termmeta.

I also tried to create the indexes one by one and only commentmeta and postmeta are still the old ones.

Does this help?
Markus

PS: the only thing I might have done is activating and deactivating the high performance keys a few months ago with the plugin… I actually to not know how to manually add keys and indexes to MySQL

I can continue to investigate if you like.

Please issue the commands wp index-mysql tables and `wp db query “SHOW CREATE TABLE co1uw_postmeta;” and post the output here or send it to me.

Hi Ollie,

yes, that would be great… I am very keen to setup these indexes.
I sent you the output to your gmail account.

Thanks a lot!!!
Markus

Aha. Markisu72’s postmeta table already has this key:

FULLTEXT KEY meta_value (meta_value)

But the plugin uses the key name for its

KEY meta_value (meta_value(32))

so the two names collide.

The workaround is to drop and recreate the FULLTEXT key

ALTER TABLE wp_postmeta DROP KEY meta_value, ADD FULLTEXT KEY fulltext_meta_value (meta_value);

and then use the plugin to add the high-performance keys to that table. (Note: markisu72’s site uses a different prefix from wp_.)

I’m leaving this tix open in hopes of making the plugin smarter so it can detect this situation.

Hi Ollie,

I did it (and the same for commentmeta which caused the same issue) and now creation was possible.

Thanks a lot
Markus

Glad to hear of your success!

You probably already know this: There’s no gain from creating FULLTEXT indexes unless you have queries that use them explicitly. WordPress core doesn’t have that kind of query in it. Some plugins do, but they manage their own FULLTEXT indexes.

And, INSERTs and UPDATEs into FULLTEXT-indexed columns take significant time.

Hi Ollie,

thanks for the info!
I have no clue, where the indexes came from, though.
Are you saying that the both indexes which interfered with the plugin are basically useless (usually)?

Thanks
Markus

Hi Olie i have the same problem. I cant add to high-performance keys to wp_usermeta
8eQXJ4P2
Thx for plugin is life saver.

• This reply was modified 2 years, 2 months ago by Zlato.

I guess you have a FULLTEXT index on wp_postmeta called meta_value. But, your uploaded metadata doesn’t describe it.

The meta_value index I create isn’t used very often. And, the other indexes you have on wp_postmeta are the high-performance ones. So, you can probably ignore this particular issue.

Thx Ollie ??