尝试添加新列时重复输入错误

I have a MySQL database with a table that has 2 million rows using innodb engine. I want to add another column, but I keep getting the following error:

我有一个MySQL数据库，其中的表使用innodb引擎有200万行。我想再加一列，但我总是得到以下错误:

Error 1062: Duplicate entry '' for key 'PRIMARY' SQL Statement: ALTER TABLE `mydb`.`table`  ADD COLUMN `country` VARCHAR(35) NULL DEFAULT NULL AFTER `email`

How can I add the column without getting this error?

如何添加列而不会得到这个错误?

EDIT: Table definition

编辑:表定义

id int(11) NOT NULL AUTO_INCREMENT,   
user_id varchar(45) NOT NULL,   
first_name varchar(150) DEFAULT NULL,   
last_name varchar(150) DEFAULT NULL,   
gender varchar(10) DEFAULT NULL,   
email varchar(100) DEFAULT NULL,   
created_at bigint(20) DEFAULT NULL,   
updated_at bigint(20) DEFAULT NULL,  
PRIMARY KEY (`id`,`user_id`),   
UNIQUE KEY `user_id_UNIQUE` (`user_id`),   
KEY `first_name` (`first_name`),   
KEY `last_name` (`last_name`)

EDIT #2: SHOW INDEXES output

编辑#2:显示索引输出

Table       Non_unique  Key_name        Seq_in_index  Column_name     Collation  Cardinality Index_type
table       0           PRIMARY         1             id              A          3516446     BTREE      
table       0           PRIMARY         2             user_id         A          3516446     BTREE
table       0           user_id_UNIQUE  1             user_id         A          3516446     BTREE
table       1           first_name      1             first_name      A          390716      BTREE  
table       1           last_name       1             last_name       A          439555      BTREE

6 个解决方案

#1

If it's not something a program needs to do (altering tables dynamically), then just wait a moment and then try again! It worked for me. :) I guess there are some InnoDB-specific processes/states (maybe still processing another ALTER queried just a moment ago?), during which the ALTER command would fail, and you just need to catch a moment when it succeeds.

如果这不是程序需要做的事情(动态地修改表)，那么请稍等片刻，然后再试一次!它为我工作。:)我猜有一些特定于innodb的进程/状态(可能还在处理刚才查询的另一个ALTER)，在这个过程中，ALTER命令将会失败，您只需要抓住它成功的那一刻。

#2

it solution will lock table on write, but often suitable for solving the problem if table is not very big

it解决方案会在写时锁定表，但如果表不是很大，通常适用于解决问题

LOCK TABLES my_table WRITE;

锁表my_table写;

ALTER TABLE my_table ADD COLUMN ts DATETIME NULL AFTER id;

ALTER TABLE my_table添加了id后的DATETIME空值;

UNLOCK TABLES;

打开表;

#3

When running an online ALTER TABLE operation, the thread that runs the ALTER TABLE operation will apply 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.

当运行一个在线ALTER TABLE操作时，运行ALTER TABLE操作的线程将应用一个“在线日志”DML操作，这些操作将在来自其他连接线程的同一个表上并发运行。当应用DML操作时，可能会遇到重复的密钥输入错误(错误1062(23000):重复的条目)，即使重复的条目只是临时的，并且会被“在线日志”中稍后的条目返回。这类似于InnoDB中的外键约束检查，在这个过程中约束必须在事务中保持。

#4

I've runned the same code and it works fine. http://sqlfiddle.com/#!2/1937e

我运行了相同的代码，它运行得很好。http://sqlfiddle.com/ ! 2/1937e

As a solution, I would try to recreate the table , copy the data into it, and then switch tables using rename.

作为一种解决方案，我将尝试重新创建表，将数据复制到其中，然后使用rename交换表。

If that doesn't work then it's clear that it's a bug with your current mysql configuration, and we'll need more details to figure it out or at least reproduce it (mysql version, mysql config, database settings, the actual data, etc.).
如果这不起作用，那么很明显，这是当前mysql配置的一个bug，我们需要更多的细节来解决它，或者至少重新生成它(mysql版本、mysql配置、数据库设置、实际数据等等)。
If it works then it probably was a problem with tables or indexes and here are a few things you can check (you can also start with these if you don't wish to recreate the table):
1. Check that you don't have any triggers that are causing other inserts
2. 检查是否没有引发其他插入的触发器
3. Check that you are just creating a column and not adding other keys/indexes
4. 检查您是否正在创建一个列，而不添加其他键/索引。
5. Check that the auto_increment value is not overflowing (for int it's over 2,000,000,000)
6. 检查auto_increment值没有溢出(对于int，它超过200000,000,000)
如果它工作,那么它可能是一个表或索引的问题,这里有几件事你可以检查(你也可以从这些如果你不希望重新创建表):检查你没有任何诱因导致其他插入检查你只是创建一个列和不添加其他键/索引检查auto_increment值不是溢出(int / 2000000000)

If none of the above, then you probably have a some corrupt data, or you missed to share some details.

如果上面都没有，那么您可能有一些损坏的数据，或者您错过了一些细节。

#5

I guess there are some other ALTERs or INSERTs which are still processing.

我想还有其他的修改或插入仍在处理中。

First, to check the processing triggers with this following queries:

首先，使用以下查询检查处理触发器:

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.INNODB_TRX\G

Then, kill the locked query by trx_mysql_thread_id: 132092 for example:

然后，通过trx_mysql_thread_id: 132092(例如:

KILL 132092;

Finally, here‘s a solution - OnlineSchemaChange (OSC). It built by Facebook supports online alter MySQL table schema with minimal impact.

最后，这里有一个解决方案——在线化学变化(OSC)。它由Facebook构建，支持在线修改MySQL表模式，影响最小。

#6

use change column

利用变化列

ALTER TABLE database.table_name CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (id);

改变表数据库。表_name更改列id id INT(11)不为NULL AUTO_INCREMENT，添加主键(id);

#1

#2