重启mysql，auto_increment的值重置(InnoDB)

超、凢脫俗 2022-03-10 09:11 256阅读 0赞

前两天，mysql数据库迁移机器后遇到auto\_increment在mysql重启后值重置的问题和在用户名密码正确的情况下拒绝连接的问题，网上搜索的资料说明如下：

**a. 连接拒绝**  
在Linux环境下，运行JAVA程序，在连接字符串中配置数据库服务器地址为localhost,用户名密码正确，连接时出错：  
java.sql.SQLException: Access denied for user 'root'@'127.0.0.1' (using password: YES)'.  
通过命令行连接正常  
解决方式：通过命令行登录mysql  
GRANT ALL PRIVILEGES ON \*.\* TO 'root'@'127.0.0.1' IDENTIFIED BY 'root123' WITH GRANT OPTION;

**b. MYSQL手册(http://dev.mysql.com/doc/refman/5.1/zh/storage-engines.html\#innodb-auto-increment-column)**

#### 15.2.6.3. AUTO\_INCREMENT列在InnoDB里如何工作 ####

如果你为一个表指定AUTO\_INCREMENT列，在数据词典里的InnoDB表句柄包含一个名为自动增长计数器的计数器，它被用在为该列赋新值。**自动增长计数器仅被存储在主内存中，而不是存在磁盘上**。

InnoDB使用下列算法来为包含一个名为ai\_col的AUTO\_INCREMENT列的表T初始化自动增长计数器：服务器启动之后，当一个用户对表T做插入之时，InnoDB执行等价如下语句的动作：

SELECT MAX(ai_col) FROM T FOR UPDATE;

语句取回的值逐次加一，并被赋给列和自动增长计数器。如果表是空的，值1被赋予该列。如果自动增长计数器没有被初始化，而且用户调用为表T显示输出的SHOW TABLE STATUS语句，则计数器被初始化（但不是增加计数）并被存储以供随后的插入使用。注意，在这个初始化中，我们对表做一个正常的独占读锁定，这个锁持续到事务的结束。

InnoDB对为新创建表的初始化自动增长计数器允许同样的过程。

注意，如果用户在INSERT中为AUTO\_INCREMENT列指定NULL或者0，InnoDB处理行，就仿佛值还没有被指定，且为它生成一个新值。

自动增长计数器被初始化之后，如果用户插入一个明确指定该列值的行，而且该值大于当前计数器值，则计数器被设置为指定列值。如果没有明确指定一个值，InnoDB给计数器增加一，并且赋新值给该列。

当访问自动增长计数器之时，InnoDB使用专用的表级的AUTO-INC锁定，该锁持续到当前SQL语句的结束而不是到业务的结束。引入了专用锁释放策略，来为对一个含AUTO\_INCREMENT列的表的插入改善部署。两个事务不能同时对同一表有AUTO-INC锁定。

注意，如果你回滚从计数器获得数的事务，你可能会在赋给AUTO\_INCREMENT列的值的序列中发现间隙。

如果用户给列赋一个赋值，或者，如果值大过可被以指定整数格式存储的最大整数，自动增长机制的行为不被定义。

在CREATE TABLE和ALTER TABLE语句中，InnoDB支持AUTO\_INCREMENT = *n* 表选项来设置计数器初始值或变更当前计数器值。因在本节早先讨论的原因，这个选项的影响在服务器重启后就无效了。

**c. http://www.iteye.com/problems/64981**

最近遇到了mysql的bug，重启mysql，auto\_increment的值重置。  
情景：  
mysql 5.0，innodb模式  
  
表a的id设置为auto\_increment型  
  
先insert 表a100行，此时id最大值为100；  
  
再删除90行：  
  
出现后边两种情况：  
1.不重启mysql，继续插入表a一行记录，这行记录的id为101；  
2重启mysql，插入表a一行记录，这行记录的id为11；  
  
感觉这是mysql的bug，不知道大家有没有碰到这种情况？  
有没有高手来解决这种问题？  
  
我希望的是重启后插入表a的这行记录的id认为101，有没有办法可以办到？

\-----------------------------------------------------------------------------------------------------

你的表使用的引擎是InnoDb  
改成MyISAM就可以了(**只是MyISAM不支持事务** )  
  
具体为什么你可以看一下官方 的说法  
[http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html][http_dev.mysql.com_doc_refman_5.0_en_innodb-auto-increment-handling.html]

引用

如果你为一张使用了innodb引擎的表指定了一auto\_increment列，那么当你不指定列地插入一些数据，而这个插入失败了的话，insert是回滚了，但是当你用show create table xxx查看auto\_increment的最新值的话你会发现，auto\_increment并没有回滚。那这是怎么回事呢？  
  
在MySQL Manual中，有关于这个问题的说明：AUTO\_INCREMENT Handling in InnoDB。  
  
大概说一下。MySQL中，如果你为一张使用了innodb引擎的表指定了一auto\_increment列，那么这张表会有一个auto\_increment计数器，专门记录当前auto\_increment的相关值，用来在insert时为auto\_increment列赋值。非常重要的一点是，这个计数值是保存在内存中的，而非磁盘上。当mysql server处于运行的时候，这个计数值只会随着insert改增长，不会随着delete而减少。换句话说，auto\_increment计数值永远是处于增长状态，不会减小。假设这时数据库中有100条记录，id从1到100，auto\_increment计数值是101(计数值永远是下一条insert的值，也就是永远比当前auto\_increment列的值大1)，当我delete id=100的记录后，再insert一条语句，这条语句的id便是101，而非100.  
当mysql server重新启动后，auto\_increment的值就有可能会改变，上面刚开始我们说过，这个值是innodb存储在内存中的，而非磁盘上，因此刚刚启动时，当我们需要去查询auto\_increment计数值时，mysql便会自动执行：  
  
SELECT MAX(id) FROM t FOR UPDATE;  
语句来获得当前auto\_increment列的最大值，然后将这个值放到auto\_increment计数器中。  
接着只要用户不是显示地指定id列的值，auto\_increment计数器值会随着每次insert而自动增长，每插入一条记录，自增1.  
那么，如果我显示的指定id列的值，且指定的这个值比当前auto\_increment计数器的值大，将会怎么处理呢？mysql会将这个值自增1后赋值给auto\_increment计数器。  
如果在一个事务中，其中有insert操作，这时auto\_increment计数器随之增长，但是最后你rollback了，这时auto\_increment不会随之rollback,这一点还是要注意的。

[http_dev.mysql.com_doc_refman_5.0_en_innodb-auto-increment-handling.html]: http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-handling.html