ALTER TABLE ADD COLUMN语句时，报了Duplicate entry的错误

 

ALTER TABLE ADD COLUMN语句时，报了Duplicate entry的错误

原因：

When running an online DDL operation, the thread that runs the ALTER TABLE statement applies an online log of DML operations that were run concurrently on the same table from other connection threads. When the DML operations are applied, it is possible to encounter a duplicate key entry error (ERROR 1062 (23000): Duplicate entry), even if the duplicate entry is only temporary and would be reverted by a later entry in the online log. This is similar to the idea of a foreign key constraint check in InnoDB in which constraints must hold during a transaction. 
 解释一下就是当执行Oline DDL操作时，MySQL实际上是将DML缓存（该缓存大小由变量 innodb_online_alter_log_max_size控制，默认128M）起来，等DDL执行完成后再将缓存中的DML重新应用到表上。 如果有别的线程执行了DML操作，在DDL完成后，应用DML时，可能会出现duplicate entry错误。

 

解决办法

LOCK TABLES my_table WRITE;

ALTER TABLE my_table 
ADD COLUMN `ts` DATETIME NULL AFTER `id`;

UNLOCK TABLES;

 

https://stackoverflow.com/questions/22580529/mysql-duplicate-entry-error-when-trying-to-add-new-column

http://blog.itpub.net/28218939/viewspace-2654121/