ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

现象：

MySQL 主从切换后，新的master上写入数据报主键冲突.错误如下：

ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

原因:

主从切换后只有一张表有问题，其它的表都正常。

由于使用了replace into 方式插入导致新插入数据，导致slave上表的AUTO_INCREMENT小于Master。

在slave切换为master后，新插入的数据导致主键冲突。

故障重现：

1.创建表t(id 列自增，a列为唯一索引)
mysql> CREATE TABLE `t` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`a` int(11) DEFAULT '0', 
 `b` int(11) DEFAULT '0',  
 PRIMARY KEY (`id`), 
  UNIQUE KEY `a` (`a`))
ENGINE=InnoDB  DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)


2.插入三条数据
mysql> insert into t (a,b)values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0


3.查看t表中的数据
mysql> select  * from t;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  3 |    3 |    3 |
+----+------+------+
3 rows in set (0.00 sec)


4.用replace插入两条新数据
mysql> replace into t (a,b)values(3,30);
Query OK, 2 rows affected (0.01 sec)


mysql> replace into t (a,b)values(3,300);
Query OK, 2 rows affected (0.01 sec)


mysql> select * from t;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  5 |    3 |  300 |
+----+------+------+
3 rows in set (0.00 sec)


mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


此时master上t表的AUTO_INCREMENT值为6。


假设数据已经同步到slave后，发生主从切换，会出现什么情况？

a.先检查新master上的数据是否已经同步过来
mysql> select *  from t;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  5 |    3 |  300 |
+----+------+------+
3 rows in set (0.00 sec)


b.查看t表的表结构，发现AUTO_INCREMENT值为4.与master不一致。
mysql> show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


c.replace into 插入值
mysql> replace into t (a,b)values(8,8);
Query OK, 1 row affected (0.00 sec)


d.出现主键冲突
mysql> replace into t (a,b)values(9,9);
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'


f.重试插入
mysql> replace into t (a,b)values(9,9);
Query OK, 1 row affected (0.01 sec)


mysql> show create table t \G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT '0',
  `b` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)


mysql> select *  from t;
+----+------+------+
| id | a    | b    |
+----+------+------+
|  1 |    1 |    1 |
|  2 |    2 |    2 |
|  4 |    8 |    8 |
|  5 |    3 |  300 |
|  6 |    9 |    9 |
+----+------+------+
5 rows in set (0.00 sec)



建议用ON DUPLICATE KEY UPDATE 的方式替换replace into。