MySQL:5.7 自增字段的3个主键冲突的场景

小半 • 2022年6月8日下午11:03 • 数据库 • 阅读 339

注意：这些问题在8.0修复了。

1、主从replace语句，主键冲突

更新（update）主键自增值不会增加，导致主键冲突。这个问题同样存在于replace到从库上的更新因为row格式的binlog在从库应用的时候使用的是update，导致主键更新丢失自增，replace在主库是delete insert。https://bugs.mysql.com/bug.php?id=73563

2、重启问题，主键冲突

重启数据库后innodb没有持久化，自增从表的最大记录开始，从而丢失自增值。并且5.7.17（第一次修复）/5.7.27（第二次修复）之前，可能导致重启后主键冲突的问题（auto_increment_increment大于1，）这个问题主要在于重启后初始化的时候自增值在Innodb 体现为auto_increment+1,自增值的获取主要分为2个部分

ha_innobase::innobase_get_autoinc 这个操作主要是在innodb层获取自增值，每个自增值保存在各自表的数据字典中，从获取到更改然后回写到表结构中增加的mutex保护，用于控制并发访问的问题。重启后初始化的值为auto_increment+1，那么第一次获取的是一个奇数值。第二次获取为偶数值（修复为在本函数中考虑auto_increment_increment的值）
handler::update_auto_increment mysql层拿到这个值后会根据自己的算法再次进行自增值的计算（某些引擎可能不会计算自增值），调用为compute_next_insert_id，这个地方考虑auto_increment_increment，因此在Innodb层获取的一个奇数值和一个偶数值（比如5和6）都会返回6，因此造成了主键冲突。
ha_innobase::innobase_lock_autoinc 这个函数就是我们的innodb_autoinc_lock_mode参数实现。

https://bugs.mysql.com/bug.php?id=76872 https://bugs.mysql.com/bug.php?id=67526

3、修改auto_increment_increment，主键冲突

在线修改auto_increment_increment可能导致BUG，如下测试（5.7.27之前），同时修复也是在5.7.27中。

drop table t;
CREATE TABLE t(
  a INT AUTO_INCREMENT PRIMARY KEY,
  b VARCHAR(200)) ENGINE=InnoDB;
SET SESSION auto_increment_increment=100;
INSERT INTO t(b) VALUES('S0');
INSERT INTO t(b) VALUES('S1');
-- SELECT a,b FROM t;
-- SHOW CREATE TABLE t;
INSERT INTO t(a,b) VALUES(28,'S2');
-- SELECT a,b FROM t;
SET SESSION auto_increment_increment=1;
-- result primary error
INSERT INTO t(b) VALUES('S3');