当前位置: 首页 > 编程笔记 >

深入探寻mysql自增列导致主键重复问题的原因

韦星文
2023-03-14
本文向大家介绍深入探寻mysql自增列导致主键重复问题的原因,包括了深入探寻mysql自增列导致主键重复问题的原因的使用技巧和注意事项,需要的朋友参考一下

废话少说,进入正题。

     拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错。首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常。问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢?

      这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象:记录最大值比自增主键值大。但开发反馈说这张表仅仅存在load data infile操作,不会进行更新主键操作,所以这个解释行不通。继续分析,表中含有唯一约束,会不会和唯一约束有关,线下实验模拟没有重现。后来想想会不会和主备切换有关系,因为前两天做过一次主备切换。于是乎,配合主备环境作了测试,果然和主备切换有关系,一切问题的来源都清晰了。

问题发生的前置条件:

       1.mysql复制基于row模式

       2.innodb表

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

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

问题发生的html" target="_blank">原理

        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=InnoDBAUTO_INCREMENT=6DEFAULT 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=InnoDBAUTO_INCREMENT=6DEFAULT 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

此时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

经过第8步操作后,若发生主备切换,slave提供服务,此时通过自增列插入主键6的记录,就会发生主键冲突。

     如何解决这个bug?对于replace操作,生成binlog时也生成delete和insert两个事件而非一个update事件;或者在执行update更新主键的同时也更新自增列值。当然了,这个只是纯原理分析,具体采用什么方法解这个问题,要根据mysql内部的实现,避免引入新的问题。这个bug我同事已经提交到社区,http://bugs.mysql.com/73563 ,大家可以看看。

 类似资料:
  • 主要内容:指定自增字段初始值,自增字段值不连续在 MySQL 中,当主键定义为自增长后,这个主键的值就不再需要用户输入数据了,而由数据库系统根据定义自动赋值。每增加一条记录,主键会自动以相同的步长进行增长。 通过给字段添加 AUTO_INCREMENT 属性来实现主键自增长。语法格式如下: 字段名 数据类型 AUTO_INCREMENT 默认情况下,AUTO_INCREMENT 的初始值是 1,每新增一条记录,字段值自动加 1。 一个表中只能

  • Jboss 7.2.0、ejb3、oracle项目中的所有实体都定义了像@sequenceGenerator这样的序列生成器(Name=“xx”,sequenceName=“yy”allocationsize=“10”) 对于一对多关系,有时我会遇到重复主键的问题。不确定这里有什么问题,但是在事务回滚之后,序列被重置,并且我一直有约束冲突错误,我已经将allocationSize更改为1,这似乎是

  • 本文向大家介绍深入探寻javascript定时器,包括了深入探寻javascript定时器的使用技巧和注意事项,需要的朋友参考一下 javascript单线程 JavaScript的单线程,与它的用途有关。作为浏览器脚本语言,JavaScript的主要用途是与用户互动,以及操作DOM。这决定了它只能是单线程,否则会带来很复杂的同步问题。比如,假定JavaScript同时有两个线程,一个线程在某个D

  • 问题内容: 我有多个结构相同的数据库,有时会在其中复制数据。为了保持数据完整性,我使用两列作为主键。一个是数据库ID,它链接到包含有关每个数据库的信息的表。另一个是表键。它不是唯一的,因为它可能有多个行,且此值相同,但database_id列中的值不同。 我打算将这两列变成一个联合主键。但是,我也想将表键设置为自动递增-但基于database_id列。 EG,使用以下数据: 如果我要添加包含dab

  • 当req.query.filter是一个没有空格的字符串时,比如'education'······这管用。当我试图找到一个有空格的列名时,比如'this havs空格',这个查询不起作用。我怎么才能修好这个?

  • 本文向大家介绍探寻Android的线程问题,包括了探寻Android的线程问题的使用技巧和注意事项,需要的朋友参考一下 什么是线程? 线程或者线程执行本质上就是一串命令(也是程序代码),然后我们把它发送给操作系统执行。 Multithreaded_process 一般来说,我们的CPU在任何时候一个核只能处理一个线程。多核处理器(目前大多数Android设备已经都是多核)顾名思义,就是可以同时处理