Issue with WPML

[DevHos]

I installed the plugin and all tables worked fine, except wp_options.

I am getting this error when i enable it using CLI

WordPress database error Duplicate entry 'wpml.WPML\TM\Settings\ProcessNewTranslatableFields.lock' for key 'wp_options.PRIMARY' for query ALTER TABLEwp_optionsADD UNIQUE KEY option_id (option_id), DROP PRIMARY KEY, ADD PRIMARY KEY (option_name), DROP KEY option_name/**imfs-query-tag*570128779*/ made by include('phar:///usr/local/bin/wp/php/boot-phar.php'), include('phar:///usr/local/bin/wp/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 entry 'wpml.WPML\TM\Settings\ProcessNewTranslatableFields.lock' for key 'wp_options.PRIMARY' in ALTER TABLEwp_optionsADD UNIQUE KEY option_id (option_id), DROP PRIMARY KEY, ADD PRIMARY KEY (option_name), DROP KEY option_name/**imfs-query-tag*570128779*/

[OllieJones]

This error message means your wp_options table contains more than one row with the same option_name. That is due to some table corruption; it happens sometimes when the UNIQUE KEY on option_name somehow gets dropped from the database.

To fix it by removing the duplicates this query will do the trick.

DELETE b
  FROM wp_options a
  JOIN wp_options b ON a.option_name = b.option_name AND b.option_id < a.option_id;

This deletes all the duplicates except the "most recent" one, that is the one with the largest option_id value.

You can do this with wpcli like so.

wp db query "DELETE b FROM wp_options a JOIN wp_options b ON a.option_name = b.option_name AND b.option_id < a.option_id"

Then try adding the high-perf key to the table again.

wp index-mysql enable wp_options

[DevHos]

I run the query you sent with 0 rows affected, still having the same problem when i try to run wp index-mysql enable wp_options

Also I searched for the option name which is causing the issue in the database and there is only one.

I am running my website behind a load balancer and 4 servers. is there anything that may cause this behavior?

[rjasdf]

Could you provide SHOW CREATE TABLE wp_options; SHOW TABLE STATUS LIKE 'wp_options';