记一次MySQL AUTO_INCREMENT的故障-阿里云开发者社区

开发者社区> 江大白> 正文

记一次MySQL AUTO_INCREMENT的故障

简介: MySQL如果在指定id 进行插入的时候,如果这个id大于表的自增值,那么MySQL会把表的自增值修改为这个id,并加1,但是如果我们把主键更新成更大的值,MySQL并不会把表的自增值修改为更新后的值,会埋下一颗定时炸弹,在某些情况下,如DDL,重启等之后,业务开始报错,会误认为DDL或者重启导致业务表的插入故障。
+关注继续查看

转载自For DBA


一、前言

开发突然紧急的过来说,他们记录无法插入了,有报重复键错误

ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

表名和数据都是采用测试数据,结果和生产的现象是一致的

二、分析

测试环境为percona server 5.7.20。首先查看表数据和表结构,结果如下

mysql> select  * from t2 order by id desc limit 3;
+------------+------+------+
| id         | c1   | c2   |
+------------+------+------+
| 2147483647 |  101 |  101 |
|        100 |  100 |  100 |
|          4 |    4 |    4 |
+------------+------+------+
3 rows in set (0.00 sec)

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c1` int(11) DEFAULT NULL,
  `c2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_c1` (`c1`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

我们可以发现,其中id 是有符号的int,已经达到了int的最大值,但是这个表没有相应的时间字段来记录这个id=2147483647 的记录是何时插入或者更新的。和开发联系问有没有手动执行插入指定ID字段的,开发回复没有。拿到发送报错的时间点,把时间点之前的binlog 捞了下,都没找到有id=2147483647的插入记录。查找DDL变更记录,只找到了该表其他字段的变更,刚好在这个报错的时间点之前,但是没有修改auto_increment的值,一时间陷入了懵逼状态。

为了找到原因,豁出去了。用了九牛二虎之力,使用二分查找恢复了好多份备份,同时结合binlog ,终于确认这条记录的具体插入时间。意外的是,我发现这条记录插入的binlog是这样的

insert into t2(id,c1,c2) values(101,101,101)

也就是说,插入的时候的id 是101,并不是 2147483647,那又是为啥呢?

继续解析binlog,我发现了新大陆

update t2 set id=4147483647,c1=101,c2=101 where id=101;

通过dml平台的日志审计功能,我们找到了对应的开发,发现是开发误操作把主键更新了,然后溢出,id变成了2147483647

此时,表的ddl 的 auto_increment 还是等于101,并没有变成2147483647。后面的正常业务SQL进行insert产生的id正常产生,因此可以执行成功,直到我们做了一次DDL,加了以个字段,MySQL重新计算了auto_incremnt的值,变成了2147483647,新插入的SQL的自增值无法继续分配,主键冲突,业务开始报错,才发现了这个定时炸弹。

三、小结

MySQL如果在指定id 进行插入的时候,如果这个id大于表的自增值,那么MySQL会把表的自增值修改为这个id,并加1,但是如果我们把主键更新成更大的值,MySQL并不会把表的自增值修改为更新后的值,会埋下一颗定时炸弹,在某些情况下,如DDL,重启等之后,业务开始报错,会误认为DDL或者重启导致业务表的插入故障。

该问题在percona 5.6.24 和 percona 5.7.20均有出现,在MySQL 8.0.11 中表现正常。找到BUG发现2005年就有被提出,因为性能原因以及场景很少没有被修复

参考链接:
1.https://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
2.https://bugs.mysql.com/bug.php?id=12434


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9494 0
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
2462 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13176 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
9052 0
阿里云ECS云服务器初始化设置教程方法
阿里云ECS云服务器初始化是指将云服务器系统恢复到最初状态的过程,阿里云的服务器初始化是通过更换系统盘来实现的,是免费的,阿里云百科网分享服务器初始化教程: 服务器初始化教程方法 本文的服务器初始化是指将ECS云服务器系统恢复到最初状态,服务器中的数据也会被清空,所以初始化之前一定要先备份好。
6890 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
21904 0
阿里云服务器ECS登录用户名是什么?系统不同默认账号也不同
阿里云服务器Windows系统默认用户名administrator,Linux镜像服务器用户名root
4012 0
+关注
江大白
哈哈哈哈哈哈哈哈哈哈
5
文章
1
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载