MySQL - Error 1062 duplicate entry For Primary

Error 1062 duplicate entry For Primary

Sometimes if you work with MySQL/MariaDB database, you’ve got "suprises". One day ago I’ve got MySQL Error when I de-activated primay key on one table, and then re-activate. I’ve got an error like the image above.

When I check the record that show on error message, I found that record is already unique. It shouldn't be cause an error. 

Unique Record

Then, how I did solve those problem.

Well, maybe Solution Number one below solution is not quite practical or good enought but it's work for me.

Solution Number One

        Step 1. First  Backup your Database, if something goes wrong.

        Step 2. run SQL : SELECT DISTINCT * FROM sourcetable

        Step 3. Export or Copy Paste the query result to Excel File

        Step 4. Create SQL Script to inserting data

        Step 5. DELETE source table 

        Step 6. Run SQL Script that we have created on Excel to inserting data.

Re-Active Primary Key

        Step 7. Re-Activate Primary Key on source table

Solution Number Two:

This is quite similiar with solution number one, but this is more "advanced" and  using with pure SQL.

        Step 1. "Clone" table from source table with SQL to clone table

                    INSERT TNTO clone_table SELECT * FROM source_table

        Step 2. DELETE source table 

        Step 3. Insert table source from clone table using SELECT DISTINCT

                    INSERT TINO source_table SELECT DISTINCT * FROM clone_table

        Step 4.  Re-Activate Primary key on source table

Re-Active Primary Key

Maybe it's a MariaDB bug or MariaDB/MySQL still keeps primary key records when we deactivate the primary key on another table/system that we don't know yet if we are not cleaning the data ( delete all). Then when we re-activate the primary key, it causes the error message above.

Good Luck. Hope this is useful.

Software :

MySQL / MariaDB ver 10.4.25

SQLyog Community