Background of the issue:
I recently moved my website from staging to live and since doing so, I have a lot of errors with WPML. I can't create new pages or I get fatal errors, new items can't be added to menus and other very critical similar issues. It seems like the root of the problem is with the database. I am trying to fix a bug where I get a couple of lines with an error like this: WordPress database error: [Duplicate entry '0' for key 'PRIMARY'] INSERT INTO `wp_icl_translations` (`element_type`, `language_code`, `trid`, `element_id`) VALUES ('post_nav_menu_item', 'en', '8097', '3363'). I did find some people talking about it online and telling solutions, but so far nothing worked for me. Here is what I tried: First I tried running this in mySQL: SELECT * FROM wp_icl_translations WHERE trid = 8097 AND language_code = 'en'. It returned me nothing and said this: MySQL returned an empty result set (i.e. zero rows). (Query took 0.0002 seconds.) After that I tried troubleshooting in the WordPress admin, I tried the solutions below and nothing worked: - Clear the cache in WPML - Remove ghost entries from the translation tables - Fix element_type collation - Set language information - Fix WPML tables collation - Assign translation status to duplicated content - Fix terms count - Fix post type assignment - Cleanup and optimize string tables. My website is running with these: WordPress version 6.8 Active theme: Salient Child Theme (version 1.0), parent Salient theme is up to date (version 17.1.0) Current plugin: WPML Multilingual CMS (version 4.7.3) PHP version 8.0.30
Symptoms:
Website Migration causes error (WordPress database error: [Duplicate entry '0' for key 'PRIMARY']) and now pages can't be created and menu can't be edited.
Questions:
How can I resolve the WordPress database error: [Duplicate entry '0' for key 'PRIMARY']?
What steps should I take to fix the issues with WPML after migrating my website?
I've enabled the private fields so you can share access to the staging version safely. Also, please tell me how I can reproduce the issue, so I can check it.
- If you do not see the wp-admin/FTP fields, this means your post and site login details are being made PUBLIC. DO NOT post your website details if you do not see the required wp-admin/FTP fields. If you do not, ask me to enable the private box. The private box looks like this:
I got an internal server error, when deactivating some plugins. Can you please restore the staging version? I'll create a local copy this time, so we don't run into this issue a second time. Thanks for your cooperation.
Thank you so much. I was able to fix the issue in the testing environment you sent. Please test this before on a staging version before applying it and make sure to have a full website backup before proceeding:
Access PhpMyAdmin and run the following command:
ALTER TABLE wp_icl_translations CHANGE translation_id old_translation_id BIGINT(20) UNSIGNED NOT NULL;
then:
ALTER TABLE wp_icl_translations ADD COLUMN translation_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;
and finally:
ALTER TABLE wp_icl_translations DROP COLUMN old_translation_id;
There was an issue with the auto-increment on the indexes of your translations, which sometimes happens on faulty migrations. The commands delete and recreates the translation_id column, with the auto-increment properly configured. Let me know if it works on your side.
I get an error on the second command, it just says I can't execute the command. If I only run the first and 3rd it deletes everything related to WPML, and I can't do any translation on my site
Thanks for sharing your results. I couldn't create a copy of your staging version (I tried in different ways, but always got an error building the packages). In this case, please restore the staging version, so it matches your live website (at the moment, the issue is solved on the staging version). This way, I can recheck the steps and see what might be going wrong.
Thanks for sharing. The steps didn't seem to work this time. In this case, please revert back to a previous backup, before running the SQL commands I shared. Then, update the staging version with that backup as well. Let me know.
I apologize for the back and forth, but I'll need to get a second opinion with my dev colleagues. When I shared the SQL queries, it worked flawlessly in the staging version when tested. Thanks for all the cooperation.
Okay I reverted the staging DB to last week's version and did your corrections but still got the same issue with the second query. Here is an image of the error it gives me
Thank you for sharing. I think I expressed myself poorly, I apologize. Please restore the previous version and do not run the SQL queries. My idea is to take that to our devs as it was before. Thank you for your patience.
Let me know when the staging version matches that, so I can proceed.
