深入探寻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 ,大家可以看看。

时间: 2014-08-15

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

JavaScript中无法通过div.style.left获取值的解决方法

一.问题总结: 样式必须直接写在元素内部才能通过div.style.left直接获取属性值(也就是必须是内联样式才行),定义在css中的样式不能通过这种方式获取. 让元素移动到200停止 setTimeout ( function () { var div = document.getElementById("div4"); //var left = parseInt(div.style.left) + 5; var left = div.offsetLeft + 5; div.sty

MySQL中的主键以及设置其自增的用法教程

1.声明主键的方法: 您可以在创建表的时候就为表加上主键,如: CREATE TABLE tbl_name ([字段描述省略...], PRIMARY KEY(index_col_name)); 也可以更新表结构时为表加上主键,如: ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,-); /* 创建一个qq表,将qq_id设为主键,且没有对其进行NOT NULl约束 */ create table qq( qq_id int(10), n

MyBatis在insert插入操作时返回主键ID的配置(推荐)

很多时候,在向数据库插入数据时,需要保留插入数据的id,以便进行后续的update操作或者将id存入其他表作为外键. 但是,在默认情况下,insert操作返回的是一个int值,并且不是表示主键id,而是表示当前SQL语句影响的行数... 接下来,我们看看MyBatis如何在使用MySQL和Oracle做insert插入操作时将返回的id绑定到对象中. MySQL用法: <insert id="insert" parameterType="com.test.User&qu

如何区分SQL数据库中的主键与外键

一.什么是主键.外键: 关系型数据库中的一条记录中有若干个属性,若其中某一个属性组(注意是组)能唯一标识一条记录,该属性组就可以成为一个主键比如 : 学生表(学号,姓名,性别,班级) 其中每个学生的学号是唯一的,学号就是一个主键 用户表(用户名.密码.登录级别) 其中用户名是唯一的, 用户名就是一个主键 上机记录表(卡号,学号,姓名.序列号) 上机记录表中单一一个属性无法唯一标识一条记录,学号和姓名的组合才可以唯一标识一条记录,所以 学号和姓名的属性组是一个主键 上机记录表中的序列号不是成绩表的

python中利用h5py模块读取h5文件中的主键方法

如下所示: import h5py import numpy as np #HDF5的写入: imgData = np.zeros((2,4)) f = h5py.File('HDF5_FILE.h5','w') #创建一个h5文件,文件指针是f f['data'] = imgData #将数据写入文件的主键data下面 f['labels'] = np.array([1,2,3,4,5]) #将数据写入文件的主键labels下面 f.close() #关闭文件 #HDF5的读取: f = h5

PHP+MySQL统计该库中每个表的记录数并按递减顺序排列的方法

本文实例讲述了PHP+MySQL统计该库中每个表的记录数并按递减顺序排列的方法.分享给大家供大家参考,具体如下: 这是一段简单的代码,可实现统计该数据库中每个表的记录数,并按递减顺序排列的功能 $host = '127.0.0.1'; $port = 3306; $dbname = 'test'; $username = 'root'; $password = ''; function ee($p) { if(PHP_SAPI == 'cli') { echo "\n"; }else{

OpenCV中C++函数imread读取图片的问题及解决方法

今天在用OpenCV实验Image Pyramid的时候发现一个奇怪的问题,就是利用C++函数imread读取图片的时候返回的结果总是空,而利用C函数cvLoadImage时却能读取到图像.代码如下: //环境:VS2010 + OpenCV 2.3.1 #include "stdafx.h" #include <cv.h> #include <highgui.h> #include <math.h> #include <stdlib.h>

mysql报错:MySQL server version for the right syntax to use near type=InnoDB的解决方法

本文实例讲述了mysql报错:MySQL server version for the right syntax to use near type=InnoDB的解决方法.分享给大家供大家参考,具体如下: 一.问题: 工作中使用sql语句建表时,mysql报了如下错误: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right

centos6.5中rpm包安装mysql5.7初始化出错的解决方法

1.rz上传到服务器,解压缩 rz [root@mini2 upload]# tar -xvf mysql-5.7.19-1.el6.i686.rpm-bundle.tar 2.安装rpm包 rpm -ivh mysql-community-common-5.7.19-1.el6.i686.rpm rpm -ivh mysql-community-libs-5.7.19-1.el6.i686.rpm rpm -ivh mysql-community-libs-compat-5.7.19-1.el