MySQL AUTO_INCREMENT的故障分析

一、演示环境：

CentOS Linux release 7.9.2009 (Core)
MySQL版本:
[root@tidb05 ~]# mysql -V
mysql  Ver 14.14 Distrib 5.7.22, for linux-glibc2.12 (x86_64) using  EditLine wrapper

  • 1.
  • 2.
  • 3.
  • 4.

二、故障场景:

具体报错信息如下:

root@tidb05 17:32:  [test001]> INSERT INTO testuser_11_13(username,userpass,create_time) values('huahua', 'xiaohuahua',now());
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

  • 1.
  • 2.

查看表结构信息：

CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `class` char(10) NOT NULL DEFAULT '',
  `age` int(3) DEFAULT NULL,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'


  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.

结合 id int(11) 发现报错 Duplicate entry ‘2147483647’ for key ‘PRIMARY’ 是自增id int(11)达到了最大值2147483647 溢出导致的insert写入数据失败

三、模拟故障:

既然知道的原因，那么咱们下面来模拟下这个故障问题:

3.1 准备测试数据:

CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'

INSERT INTO testuser_11_13(username,userpass,create_time) values('php', 'xiaohuahua',now());
INSERT INTO testuser_11_13(username,userpass,create_time) values('java', 'xiaohuahua',now());
INSERT INTO testuser_11_13(username,userpass,create_time) values('lua', 'xiaohuahua',now());

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.

3.2 模拟故障：

int(11)最大值为2147483647,模拟接近生产上环境 咱们更新这个id为2147483644,然后再插入2条数据 2147483645, 2147483646, 2147483647

update testuser_11_13 set id=2147483644,username='python' where id=3;

  • 1.

查看表的自增id是否变成了2147483644：

root@tidb05 20:50:  [test001]> select * from testuser_11_13;
+------------+----------+------------+---------------------+---------------------+
| id         | username | userpass   | create_time         | update_time         |
+------------+----------+------------+---------------------+---------------------+
|          1 | php      | xiaohuahua | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          2 | java     | xiaohuahua | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
| 2147483644 | lua      | xiaohuahua | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
+------------+----------+------------+---------------------+---------------------+
3 rows in set (0.00 sec)

root@tidb05 20:50:  [test001]> show create table testuser_11_13\G
*************************** 1. row ***************************
       Table: testuser_11_13
Create Table: CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'
1 row in set (0.01 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.

再次插入一条数据验证AUTO_INCREMENT，结果是接着update之前的AUTO_INCREMENT=4自增值进行增长,并没有从2147483644 值来增长。

INSERT INTO testuser_11_13(username,userpass,create_time) values('python', '大力',now());

root@tidb05 20:50:  [test001]> INSERT INTO testuser_11_13(username,userpass,create_time) values('python', '大力',now());
Query OK, 1 row affected (0.01 sec)

root@tidb05 20:52:  [test001]> select * from testuser_11_13;
+------------+----------+------------+---------------------+---------------------+
| id         | username | userpass   | create_time         | update_time         |
+------------+----------+------------+---------------------+---------------------+
|          1 | php      | xiaohuahua | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          2 | java     | xiaohuahua | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          4 | python   | 大力       | 2021-11-14 20:52:17 | 2021-11-14 20:52:17 |
| 2147483644 | lua      | xiaohuahua | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
+------------+----------+------------+---------------------+---------------------+
4 rows in set (0.00 sec)

root@tidb05 20:52:  [test001]> show create table testuser_11_13\G
*************************** 1. row ***************************
       Table: testuser_11_13
Create Table: CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'
1 row in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

咱们对表进行DDL操作 新增字段，看下AUTO_INCREMENT的值是否会变成2147483644,结果还是接着update之前的id值进行自增。

alter table testuser_11_13 add  column age int(3) not null default '0' after userpass;
INSERT INTO testuser_11_13(username,userpass,age,create_time) values('ruby', 'abc','20',now());

root@tidb05 21:05:  [test001]> select * from testuser_11_13 ;
+------------+----------+------------+-----+---------------------+---------------------+
| id         | username | userpass   | age | create_time         | update_time         |
+------------+----------+------------+-----+---------------------+---------------------+
|          1 | php      | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          2 | java     | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          4 | python   | 大力       |   0 | 2021-11-14 20:52:17 | 2021-11-14 20:52:17 |
|          5 | ruby     | abc        |  20 | 2021-11-14 21:05:36 | 2021-11-14 21:05:36 |
| 2147483644 | lua      | xiaohuahua |   0 | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
+------------+----------+------------+-----+---------------------+---------------------+
5 rows in set (0.00 sec)

root@tidb05 21:05:  [test001]> show create table testuser_11_13\G
*************************** 1. row ***************************
       Table: testuser_11_13
Create Table: CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `age` int(3) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'
1 row in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

对表添加索引，表的AUTO_INCREMENT 也不会按照update设置的id值来自增。

alter table testuser_11_13 add index idx_age(age);

  • 1.

那咱们就重启下MySQL服务看下，会不会有变化:
重启完成MySQL服务，发现AUTO_INCREMENT=2147483645 开始计算自增id值。

root@tidb05 21:11:  [test001]> show create table testuser_11_13\G
*************************** 1. row ***************************
       Table: testuser_11_13
Create Table: CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `age` int(3) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483645 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'
1 row in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.

连续插入3条数据,前面2条可以成功，到第三条的话，自增的id值达到了int(11) (2147483647)最大值,导致溢出报错了。

root@tidb05 21:11:  [test001]> INSERT INTO testuser_11_13(username,userpass,age,create_time) values('golang', 'EFC','21',now());
Query OK, 1 row affected (0.01 sec)

root@tidb05 21:12:  [test001]> INSERT INTO testuser_11_13(username,userpass,age,create_time) values('C++', 'ABC','23',now());
Query OK, 1 row affected (0.00 sec)

root@tidb05 21:13:  [test001]> INSERT INTO testuser_11_13(username,userpass,age,create_time) values('net', '123321','24',now());
ERROR 1062 (23000): Duplicate entry '2147483647' for key 'PRIMARY'

root@tidb05 21:13:  [test001]> show create table testuser_11_13\G
*************************** 1. row ***************************
       Table: testuser_11_13
Create Table: CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `age` int(3) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2147483647 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'
1 row in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.

四、故障解决：

4.1 定位故障原因:

1.定位是那条sql语句update 更新的主键id导致的问题？
my.cnf设置如下参数
binlog_format = row
binlog_row_image=FULL
##下面的参数会让binlog文件记录原始sql.方便DBA通过binlog查找执行过的原始sql，来定位问题。但是开启参数的会导致binlog文件变大
binlog_rows_query_log_events = 1

[root@tidb05 ~]# mysqlbinlog --base64-output=decode-rows -vv mysql-bin.000043 > /root/binlog.txt
[root@tidb05 ~]# grep  'update testuser_11_13 set id=' binlog.txt 
#update testuser_11_13 set id=2147483644,username='lua' where id=3

  • 1.
  • 2.
  • 3.

4.2.解决方法

解决方法一：

此次演示的环境涉及到的记录就3条, 可以查到记录值然后删除对应的记录，重置AUTO_INCREMENT值。

root@tidb05 21:49:  [test001]> select * from testuser_11_13;
+------------+----------+------------+-----+---------------------+---------------------+
| id         | username | userpass   | age | create_time         | update_time         |
+------------+----------+------------+-----+---------------------+---------------------+
|          1 | php      | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          2 | java     | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|          4 | python   | 大力       |   0 | 2021-11-14 20:52:17 | 2021-11-14 20:52:17 |
|          5 | ruby     | abc        |  20 | 2021-11-14 21:05:36 | 2021-11-14 21:05:36 |
| 2147483644 | lua      | xiaohuahua |   0 | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
| 2147483645 | golang   | EFC        |  21 | 2021-11-14 21:12:48 | 2021-11-14 21:12:48 |
| 2147483647 | C++      | ABC        |  23 | 2021-11-14 21:13:10 | 2021-11-14 21:13:10 |
+------------+----------+------------+-----+---------------------+---------------------+
7 rows in set (0.00 sec)

root@tidb05 21:50:  [test001]> delete from  testuser_11_13 where id=2147483647;
Query OK, 1 row affected (0.00 sec)

root@tidb05 21:51:  [test001]> delete from  testuser_11_13 where id=2147483645;
Query OK, 1 row affected (0.00 sec)

root@tidb05 21:51:  [test001]> delete from  testuser_11_13 where id=2147483644;
Query OK, 1 row affected (0.01 sec)

root@tidb05 21:51:  [test001]> alter table testuser_11_13 AUTO_INCREMENT=6;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

root@tidb05 21:52:  [test001]> show create table testuser_11_13\G
*************************** 1. row ***************************
       Table: testuser_11_13
Create Table: CREATE TABLE `testuser_11_13` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `age` int(3) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'
1 row in set (0.00 sec)


INSERT INTO testuser_11_13(username,userpass,age,create_time,update_time) values('lua', 'xiaohuahua','0','2021-11-14 20:48:45','2021-11-14 20:50:11');
INSERT INTO testuser_11_13(username,userpass,age,create_time,update_time) values('golang', 'EFC','21','2021-11-14 21:12:48','2021-11-14 21:12:48');
INSERT INTO testuser_11_13(username,userpass,age,create_time,update_time) values('C++', 'ABC','23','2021-11-14 21:13:10','2021-11-14 21:13:10');

**验证恢复完成:**

root@tidb05 22:01:  [test001]> select * from testuser_11_13;
+----+----------+------------+-----+---------------------+---------------------+
| id | username | userpass   | age | create_time         | update_time         |
+----+----------+------------+-----+---------------------+---------------------+
|  1 | php      | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|  2 | java     | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|  4 | python   | 大力       |   0 | 2021-11-14 20:52:17 | 2021-11-14 20:52:17 |
|  5 | ruby     | abc        |  20 | 2021-11-14 21:05:36 | 2021-11-14 21:05:36 |
|  6 | lua      | xiaohuahua |   0 | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
|  7 | golang   | EFC        |  21 | 2021-11-14 21:12:48 | 2021-11-14 21:12:48 |
|  8 | C++      | ABC        |  23 | 2021-11-14 21:13:10 | 2021-11-14 21:13:10 |
+----+----------+------------+-----+---------------------+---------------------+
7 rows in set (0.00 sec)

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.
  • 30.
  • 31.
  • 32.
  • 33.
  • 34.
  • 35.
  • 36.
  • 37.
  • 38.
  • 39.
  • 40.
  • 41.
  • 42.
  • 43.
  • 44.
  • 45.
  • 46.
  • 47.
  • 48.
  • 49.
  • 50.
  • 51.
  • 52.
  • 53.
  • 54.
  • 55.
  • 56.
  • 57.
  • 58.
  • 59.
  • 60.
  • 61.
  • 62.
  • 63.

解决方法二:

但是一旦涉及到几万，几十万，几千万的话，只能是重建表了。

下面是重建表的方法:

对原表进行备份: rename table testuser_11_13 to testuser_11_13bak

导出原表数据:

root@tidb05 22:30:  [test001]> select username,userpass,age,create_time,update_time into outfile "/data1/mysql/tmp/11.sql"  fields terminated by "," lines terminated by "\n"  from test001.testuser_11_13bak;
Query OK, 7 rows affected (0.00 sec)
[root@tidb05 ~]# cat /data1/mysql/tmp/11.sql 
php,xiaohuahua,0,2021-11-14 20:48:44,2021-11-14 20:48:44
java,xiaohuahua,0,2021-11-14 20:48:44,2021-11-14 20:48:44
python,大力,0,2021-11-14 20:52:17,2021-11-14 20:52:17
ruby,abc,20,2021-11-14 21:05:36,2021-11-14 21:05:36
lua,xiaohuahua,0,2021-11-14 20:48:45,2021-11-14 20:50:11
golang,EFC,21,2021-11-14 21:12:48,2021-11-14 21:12:48
C++,ABC,23,2021-11-14 21:13:10,2021-11-14 21:13:10

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.

在库上新建表:

 CREATE TABLE `testuser_11_13new` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `username` varchar(20) NOT NULL COMMENT '用户名称',
  `userpass` varchar(20) NOT NULL COMMENT '用户密码',
  `age` int(3) NOT NULL DEFAULT '0',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_username` (`username`),
  KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表'

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

导入数据到新表:

echo "load data local infile '/data1/mysql/tmp/11.sql' IGNORE into table testuser_11_13new \
fields terminated by ',' optionally enclosed by '\"' \
lines terminated by '\n' \
(username,userpass,age,create_time,update_time); \
"|mysql  test001

[root@tidb05 ~]# echo "load data local infile '/data1/mysql/tmp/11.sql' IGNORE into table testuser_11_13new \
> fields terminated by ',' optionally enclosed by '\"' \
> lines terminated by '\n' \
> (username,userpass,age,create_time,update_time); \
> "|mysql  test001

  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.

上面的导入和导出的方法参考：
 https://blog.csdn.net/adparking/article/details/6676571

数据导入完成并且和备份的表一致:


root@tidb05 22:52:  [test001]> select * from testuser_11_13new;
+----+----------+------------+-----+---------------------+---------------------+
| id | username | userpass   | age | create_time         | update_time         |
+----+----------+------------+-----+---------------------+---------------------+
|  1 | php      | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|  2 | java     | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|  3 | python   | 大力       |   0 | 2021-11-14 20:52:17 | 2021-11-14 20:52:17 |
|  4 | ruby     | abc        |  20 | 2021-11-14 21:05:36 | 2021-11-14 21:05:36 |
|  5 | lua      | xiaohuahua |   0 | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
|  6 | golang   | EFC        |  21 | 2021-11-14 21:12:48 | 2021-11-14 21:12:48 |
|  7 | C++      | ABC        |  23 | 2021-11-14 21:13:10 | 2021-11-14 21:13:10 |
+----+----------+------------+-----+---------------------+---------------------+
7 rows in set (0.00 sec)

root@tidb05 22:52:  [test001]> select * from testuser_11_13bak;
+----+----------+------------+-----+---------------------+---------------------+
| id | username | userpass   | age | create_time         | update_time         |
+----+----------+------------+-----+---------------------+---------------------+
|  1 | php      | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|  2 | java     | xiaohuahua |   0 | 2021-11-14 20:48:44 | 2021-11-14 20:48:44 |
|  4 | python   | 大力       |   0 | 2021-11-14 20:52:17 | 2021-11-14 20:52:17 |
|  5 | ruby     | abc        |  20 | 2021-11-14 21:05:36 | 2021-11-14 21:05:36 |
|  6 | lua      | xiaohuahua |   0 | 2021-11-14 20:48:45 | 2021-11-14 20:50:11 |
|  7 | golang   | EFC        |  21 | 2021-11-14 21:12:48 | 2021-11-14 21:12:48 |
|  8 | C++      | ABC        |  23 | 2021-11-14 21:13:10 | 2021-11-14 21:13:10 |
+----+----------+------------+-----+---------------------+---------------------+
7 rows in set (0.00 sec)


  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  • 7.
  • 8.
  • 9.
  • 10.
  • 11.
  • 12.
  • 13.
  • 14.
  • 15.
  • 16.
  • 17.
  • 18.
  • 19.
  • 20.
  • 21.
  • 22.
  • 23.
  • 24.
  • 25.
  • 26.
  • 27.
  • 28.
  • 29.

五、总结

特别说明:

新插入的SQL的自增值无法继续分配，主键冲突，业务开始报错，这是个定时炸弹。

小结:

MySQL如果在指定id 进行插入的时候，如果这个id大于表的自增值，那么MySQL会把表的自增值修改为这个id，并加1，但是如果我们把主键更新成更大的值，MySQL并不会把表的自增值修改为更新后的值，会埋下一颗定时炸弹，
在MySQL5.7.22中测试 一旦数据库重启，就会从update设置的id=2147483644值来自增长。直到达到2147483647 int(11)最大值，insert就报错了ERROR 1062 (23000): Duplicate entry ‘2147483647’ for key ‘PRIMARY’， 此时业务开始报错，会误认为DDL或者重启导致业务表的插入故障。

避免此类问题方法：

关于DML修改主键，直接在sql审核平台禁用就行，避免研发瞎搞。
sql审核系统强制bigint,设置int有好多个溢出事件

直接定义成了bigint，有什么其他隐患？
唯一不好的是开发也会乱用，设置非常大的值比如160亿亿，体验不好

如何预防？
也对主键溢出做了巡检，超过70% 进行报警提示

replace into也会导致主从auto increment不一致，主从切换后，新主库的auto increment落后新从库的auto increment，insert就报主键冲突

网友测试percona版MySQL结果：

此问题在percona 5.6.24 和 percona 5.7.20均有出现，在MySQL 8.0.11 中表现正常。找到BUG发现2005年就有被提出，因为性能原因以及场景很少没有被修复
 https://mp.weixin.qq.com/s/j8RGJ9oz1Astg4cXg0-EBA

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

posted @ 2021-11-15 13:04  勤奋的蓝猫  阅读(12)  评论(0)  编辑  收藏  举报  来源