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

废话少说，进入正题。

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

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

问题发生的前置条件：

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=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 ，大家可以看看。

赞 (0)

相关推荐

  • MySQL 处理插入过程中的主键唯一键重复值的解决方法

    MySQL 处理插入过程中的主键唯一键重复值的解决方法

    本篇文章主要介绍在插入数据到表中遇到键重复避免插入重复值的处理方法,主要涉及到IGNORE,ON DUPLICATE KEY UPDATE,REPLACE:接下来就分别看看这三种方式的处理办法. IGNORE 使用ignore当插入的值遇到主键(PRIMARY KEY)或者唯一键(UNIQUE KEY)重复时自动忽略重复的记录行,不影响后面的记录行的插入, 创建测试表 CREATE TABLE Tignore (ID INT NOT NULL PRIMARY KEY , NAME1 INT )d

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

    废话少说,进入正题. 拿到问题后,首先查看现场,发现问题表的中记录的最大值比自增列的值要大,那么很明显,当有记录进行插入时,自增列产生的值就有可能与已有的记录主键冲突,导致出错.首先想办法解决问题,通过人工调大自增列的值,保证大于表内已有的主键即可,调整后,导数据正常.问题是解决了,接下来要搞清楚问题原因,什么操作导致了这种现象的发生呢? 这里有一种可能,即业务逻辑包含更新自增主键的代码,由于mysql的update动作不会同时更新自增列值,若更新主键值比自增列大,也会导致上述现象:记录最大值比

  • Oracle判断表、列、主键是否存在的方法

    在编写程序时,数据库结构会经常变化,所以经常需要编写一些数据库脚本,编写完成后需发往现场执行,如果已经存在或者重复执行,有些脚本会报错,所以需要判断其是否存在,现在我就把经常用到的一些判断方法和大家分享下: 一.判断Oracle表是否存在的方法 declare tableExistedCount number; --声明变量存储要查询的表是否存在 begin select count(1) into tableExistedCount from user_tables t where t.tab

  • 当Mysql行锁遇到复合主键与多列索引详解

    背景 今天在配合其他项目组做系统压测,过程中出现了偶发的死锁问题.分析代码后发现有复合主键的update情况,更新复合主键表时只使用了一个字段更新,同时在事务内又有对该表的insert操作,结果出现了偶发的死锁问题. 比如表t_lock_test中有两个主键都为primary key(a,b) ,但是更新时却通过update t_lock_test .. where a = ? ,然后该事务内又有insert into t_lock_test values(...) InnoDB中的锁算法是Ne

  • MySQL自增列插入0值的解决方案

    在将数据库从MSSQL迁移到MySQL的过程中,基于业务逻辑的要求,需要在MySQL的自增列插入0值.在MSSQL中是这样完成的: 复制代码 代码如下: string sql;sql = " set identity_insert dbo.AppUsers on " + " insert dbo.AppUsers (Id, IsLocked, IsMustChangeLocalPassword, IsAvailable, Name, Sequence, CreatedBy,

  • mysql 实现设置多个主键的操作

    mysql 实现设置多个主键的操作

    user表,身份证号码要唯一,手机号码,邮箱要唯一 实现方式: 表结构不用动.一个主键Id 加索引实现 如图类型设置索引类型为Unique 唯一 选择栏位,命个名就行.索引方式btree 就好.ok啦~ 补充:mysql实现多表主键不重复 同一个数据库中有两张表,里面字段都是一样,只是因为存的数据要区分开.但是主键不能重复.具体实现如下: 新建数据库 mytest 新建user表和admin表 CREATE TABLE `user` ( `user_id` INT(11) NOT NULL, `

  • DataSet 添加数据集、行、列、主键和外键等操作示例

    DataSet 添加数据集、行、列、主键和外键等操作示例

    前台代码:html <%@ Page language="c#" Codebehind="CodeUse.aspx.cs" AutoEventWireup="false" Inherits="DsAndXML.CodeUse" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" ><HTML>  

  • MySQL的自增ID(主键) 用完了的解决方法

    在 MySQL 中用很多类型的自增 ID,每个自增 ID 都设置了初始值.一般情况下初始值都是从 0 开始,然后按照一定的步长增加(一般是自增 1).一般情况下,我们都是用int(11)来作为数据表的自增 ID,在 MySQL 中只要定义了这个数的字节长度,那么就会有上限. MySQL的自增ID(主键) 用完了,怎么办? 如果用 int unsigned (int,4个字节 ), 我们可以算下最大当前声明的自增ID最大是多少,由于这里定义的是 int unsigned,所以最大可以达到2的32幂

  • Mysql主键UUID和自增主键的区别及优劣分析

    Mysql主键UUID和自增主键的区别及优劣分析

    引言 之前有段时间用postgresql 数据库,在上云之后,从自增主键变为uuid,感觉uuid全球唯一,很方便. 最近用mysql,发现mysql主键都是选择自增主键,仔细比较一下,为什么mysql选择自增主键,有什么不同. 在mysql5.0之前,如果是多个master复制的环境,无法用自增主键,因为可能重复.在5.0以及之后的版本通过配置自增偏移量解决了整个问题. 什么情况下我们希望用uuid 1. 避免重复,便于scale,这就是我们做cloud service的时候选择uuid的主要

  • 深入谈谈MySQL中的自增主键

    深入谈谈MySQL中的自增主键

    MySQL的主键可以是自增的,那么如果在断电重启后新增的值还会延续断电前的自增值吗?自增值默认为1,那么可不可以改变呢?下面就说一下 MySQL的自增值. 特点 保存策略 1.如果存储引擎是 MyISAM,那么这个自增值是存储在数据文件中的: 2.如果是 InnoDB引擎,1)在 5.6之前是存储在内存中,没有持久化,在重启后会去找最大的键值,举个例子,如果一个表当前数据行里最大 id是10,AUTO_INCREMENT=11.这时候,我们删除 id=10 的行,AUTO_INCREMENT 还

  • mysql主键的缺少导致备库hang住

    最近线上频繁的出现slave延时的情况,经排查发现为用户在删除数据的时候,由于表主键的主键的缺少,同时删除条件没有索引,或或者删除的条件过滤性极差,导致slave出现hang住,严重的影响了生产环境的稳定性,也希望通过这篇博客,来加深主键在innodb引擎中的重要性,希望用户在使用RDS,设计自己的表的时候,一定要为表加上主键,主键可以认为是innodb存储引擎的生命,下面我们就来分析一下这个案例(本案例的生产环境的binlog为row模式,对于myisam存储引擎也有同样的问题): (1).现

随机推荐