深入探寻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
-
当req.query.filter是一个没有空格的字符串时，比如'education'······这管用。当我试图找到一个有空格的列名时，比如'this havs空格'，这个查询不起作用。我怎么才能修好这个？
-
问题内容： 我有多个结构相同的数据库，有时会在其中复制数据。为了保持数据完整性，我使用两列作为主键。一个是数据库ID，它链接到包含有关每个数据库的信息的表。另一个是表键。它不是唯一的，因为它可能有多个行，且此值相同，但database_id列中的值不同。 我打算将这两列变成一个联合主键。但是，我也想将表键设置为自动递增-但基于database_id列。 EG，使用以下数据： 如果我要添加包含dab
-
本文向大家介绍探寻Android的线程问题，包括了探寻Android的线程问题的使用技巧和注意事项，需要的朋友参考一下 什么是线程？ 线程或者线程执行本质上就是一串命令（也是程序代码），然后我们把它发送给操作系统执行。 Multithreaded_process 一般来说，我们的CPU在任何时候一个核只能处理一个线程。多核处理器（目前大多数Android设备已经都是多核）顾名思义，就是可以同时处理