有个上线单,期望将id自增列从10000开始,但执行后面这个DDL后`alter table tt AUTO_INCREMENT=10000;`,再insert发现id并没有从10000开始,这是为什么呢? * 1、数据库没有重启 * 2、期间这个表无其它DDL执行 * 3、数据库版本:5.6.23 如下是具体复现过程: mysql> CREATE TABLE `tt` ( -> `id` int(11) NOT NULL AUTO_INCREMENT, -> `name` varchar(20) NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=InnoDB ; Query OK, 0 rows affected (0.02 sec) mysql> insert into tt (name)values('a'),('b'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from tt; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> alter table tt AUTO_INCREMENT=10000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) AUTO_INCREMENT的值改成10000后,不久又变回AUTO_INCREMENT=3了。 后面重新再调整时,发现并没有改,过段时间后又还原了。 mysql> alter table tt AUTO_INCREMENT=10000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) .... mysql> select * from tt; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0.00 sec) mysql> show create table tt\G *************************** 1. row *************************** Table: tt Create Table: CREATE TABLE `tt` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) 关于auto_inrement的问题,在8.0以前的版,auto_inrement值并没有持久化,只存储在内存中。如果期间mysql重启,会重新生成auto_inrement值。 如:`select max(id) from table for update` 但是,除了重启,主动alter修改,肯定还有其它因素影响表的auto_increment的重新计算。 首先想到的是表的统计数据相关的,会不会触发了表统计信息更新,同时也重新计算了auto_increment值。于是检查配置: mysql> show variables like '%stats%'; +--------------------------------------+---------------+ | Variable_name | Value | +--------------------------------------+---------------+ | innodb_stats_auto_recalc | ON | | innodb_stats_method | nulls_equal | | innodb_stats_on_metadata | OFF | | innodb_stats_persistent | ON | | innodb_stats_persistent_sample_pages | 20 | | innodb_stats_sample_pages | 8 | | innodb_stats_transient_sample_pages | 8 | | myisam_stats_method | nulls_unequal | +--------------------------------------+---------------+ innodb_stats_on_metadata 参数是关着的,innodb_stats_persistent=ON并不会自动更新表统计数据。但还是进行了测试。 修改自增后,然后执行```select * from information_schema.tables;``` 。果然,不需要等待,AUTO_INCREMENT就还原了。 mysql> explain select * from information_schema.tables; +----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+ | 1 | SIMPLE | tables | ALL | NULL | NULL | NULL | NULL | NULL | Open_full_table; Scanned all databases | +----+-------------+--------+------+---------------+------+---------+------+------+----------------------------------------+ 1 row in set (0.02 sec) 虽然不会更新索引统计信息,但是执行都会去open\_full\_table,scanned all databases; 可能是重新load的表,导致了auto_increment的值重新设置。 后面在bugs中也找到了这个问题,在5.6.27以前的版本,会存在这个问题。[bug#77743](https://bugs.mysql.com/bug.php?id=77743) Reloading a table that was evicted while empty caused an AUTO_INCREMENT value to be reset 虽然在5.6.27已经修复了这个问题,但重启MySQL,auto_increment还是会重新设置。直到8.0版本才彻底解决。 后记如何成功设置auto_increment: * 1、执行修改auto_increment后,在自增值没有变回前写入一条数据(在有监控的情况下要反映要快,已知promethuos是会查表的meta数据,有可能会导致reload table) * 2、先手动插入或者更新一条记录且id为10000 (较为可控) * 3、升级MySQL版本到5.6.27以上版本(成本较大,不过建议升级到5.7或者8.0的GA版本)
文章最后更新时间: 2020年05月29日 19:27:12
分类文章统计
Python常见错误(3)
Python基础(10)
Django(5)
Flask(1)
Linux基础(5)
shell(11)
linux排障(4)
虚拟化(1)
Consul(3)
ProxySQL(7)
SequoiaDB(2)
TiDB(4)
Redis(2)
oracle(10)
MySQL(64)
常用软件(2)
硬件排障(2)
HTML(1)
JavaScript(1)
我们的作品(18)
windows(1)
总结(1)
按年文章统计
2013(43)
2014(19)
2015(25)
2016(6)
2017(30)
2018(7)
2019(17)
2020(4)
2021(4)
2023(1)
2024(2)
老版入口
亲,扫我吧!
友情链接
飞哥的:imbusy.me/
冰川的:www.mindg.cn
海洋的:hiaero.net
宏斌的:techindeep.com
若水的:nosa.me
段郎的:sixther.me
肥客联邦:fk68.net