MYSQL 出現 error 1602 mysql duplicate entry時, 竟是它的bug

在撰寫程式過程，突然一個錯誤後，要寫入mysql DB的資料,竟然全都出現 error 1602 mysql duplicate entry的錯誤訊息，本還以為是自己不小心去動到程式所以出了問題，但查了又查，程式沒問題，最後直接查mysql DB,結果發現靈異現象，出現了在RDBMS上不可能出現的狀況，欄位定義都沒錯，但有的資料可以輸入，有的就是不行，且這種狀況根本完全無法理解，試了一整晚就是找不出因原，還以為是農曆七月見鬼了?!




後來查了網路資料,才知道原來是mysql的BUG !!!  真相終於大白!!  解決方法:

1.直接dorp table 然後再重建即可。

2.如有資料時，可用mysqldump做DB dump 滙出,然後再滙入，即可解決(如下步驟)。

網路上找到的解答:

We encountered an interesting error on a client database this past few days, where MySQL was issuing random errors, complaining about:

Duplicate entry '1' for key 'PRIMARY'

These errors were coming up on various tables at different times in the day, but the crazy thing is - the duplicate key error is on an auto-increment field!

That's not normal.

When you have a table defined as

CREATE TABLE test (id int auto_increment, somevalue int, primary key (id));

There's no way you can generate a duplicate key error on the 'id' field, unless you manually pass the value of the auto_increment id field (which we were not)

Running a mysqlcheck reported all tables are OK. We proceeded to issue OPTIMIZE TABLEstatements (this rebuilds indexes) as well as manually running REPAIR TABLE for each table, but nothing helped.

The table indexes were obviously corrupted in some way, although MySQL tools reported all is well...

How to fix this problem

The only way we could fix it, is by rebuilding the entire database from scratch.

Dropping all tables and reloading them from a backup.

Step 1

Create a backup of the database by issuing:

mysqldump -uUSER -pPASSWORD DBNAME > /tmp/my_backup.sql

Replace USER with your MySQL username, PASSWORD with your MySQL password and DBNAME with the databasename you are looking to repair.

Step 2

Drop and recreate the database

drop database DBNAME; create database DBNAME;

Step 3

Reload the import

mysql -uUSER -pPASSWORD DBNAME < /tmp/my_backup.sql

出處 : How to fix: MySQL Duplicate entry for key PRIMARY on Auto_Increment