自增列导致主键重复

2024-04-02 19:04:59 370人浏览八月长安

摘要

有记录进行插入时，自增列产生的值就有可能与已有的记录主键冲突，导致出错。首先想办法解决问题，通过人工调大自增列的值，保证大于表内已有的主键即可，调整后，导数据正常问题发生的前置条件：

有记录进行插入时，自增列产生的值就有可能与已有的记录主键冲突，导致出错。首先想办法解决问题，通过人工调大自增列的值，保证大于表内已有的主键即可，调整后，导数据正常

问题发生的前置条件：

1.Mysql复制基于row模式

2.innodb表

3.表含有自增主键，并且含有唯一约束

4.load data infile 采用replace into语法插入数据【遇到重复唯一约束，直接覆盖】

问题发生的原理：

1.主库遇到重复unique约束时，进行replace操作；

2.replace在主库上面实际变化为delete+insert，但binlog记录的是update；

3.备库重做update动作，更新主键，但由于update动作不会更新自增列值，导致更新后记录值大于自增列值

问题重现实验：

准备工作			Create table test_autoinc(id int auto_increment, c1 int,c2 varchar(100),primary key(id),unique key(c1)); insert into test_autoinc(c1,c2) values(1,'abc'); insert into test_autoinc(c1,c2) values(2,'abc'); insert into test_autoinc(c1,c2) values(3,'abcdd'); insert into test_autoinc(c1,c2) values(4,'abcdd'); insert into test_autoinc(c1,c2) values(5,'abcdd');
1	操作	备注	Master	slave
2	查看自增列值 Show create table test_autoinc\G	插入5条记录后，自增列值变为6	CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;	CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8
3	查看表数据		id \| c1 \| c2 ---+------+------ 1 \| 1 \| abc 2 \| 2 \| abc 3 \| 3 \| abcdd 4 \| 4 \| abcdd 5 \| 5 \| abcdd	id \| c1 \| c2 ---+------+------ 1 \| 1 \| abc 2 \| 2 \| abc 3 \| 3 \| abcdd 4 \| 4 \| abcdd 5 \| 5 \| abcdd
4	查看binlog位置 show master status\G	记录当前binlog位点，后续可以查看replace动作产生的binlog事件	mysql-bin.000038 59242888
5	replace操作 replace into test_autoinc(c1,c2) values(2,'eeee');	影响两条记录，主库replace= delete+insert	Query OK, 2 rows affected (0.00 sec)
6	查看表数据		id \| c1 \| c2 ---+------+------- 1 \| 1 \| abc 3 \| 3 \| abcdd 4 \| 4 \| abcdd 5 \| 5 \| abcdd 6 \| 2 \| eeee	id \| c1 \| c2 ---+------+------- 1 \| 1 \| abc 3 \| 3 \| abcdd 4 \| 4 \| abcdd 5 \| 5 \| abcdd 6 \| 2 \| eeee
7	查看binlog事件 show binlog events in 'mysql-bin.000038' from 59242888;	也可以通过mysqlbinlog工具分析日志，查询从库执行的update语句	Pos \| Event_type ---------+--------------- 59242888 \| Query 59242957 \| Table_map 59243013 \|Update_rows_v1 59243072 \| Xid
8	查看自增列值 Show create table test_autoinc\G;	此时master的自增列为7，而slave的自增列为6，与表内最大值相同	CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDBAUTO_INCREMENT=7	CREATE TABLE `test_autoinc` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c1` int(11) DEFAULT NULL, `c2` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `c1` (`c1`) ) ENGINE=InnoDBAUTO_INCREMENT=6
9	手工调大自增主键 Show create table test_autoinc\G;	手工调大自增id alter table test_autoinc auto_increment=12;	Show create table test_autoinc\G;	alter table test_autoinc auto_increment=12; Show create table test_autoinc\G; 发现master和slave的自增id一致