mysql自增字段异常
时间：2009-05-19 来源：uranusllj
今天mysql出了个比较奇怪地问题，一张mail表里的自增字段直接出现一个值为4亿的数据(4294967295,16进制值为ffffffff)，表中不能再插入新的数据。
从网上看到一篇文章，mysql在检测到表中有损坏的记录时，会自动修复，为了保证数据的完整性，mysql会以空格（0x20)写进磁盘来完成修复。根据字段的类型，自增字段的长度不同，所允许的最大值也不同。见下： int(10) unsigned类型最大值十进制为4294967295，十六进制ffffffff;
mediumint(8) unsigned类型最大值十进制为16777215，十六进制ffffff；
smallint(6) unsigned类型最大值十进制为65535，十六进制ffff；
tinyint(3) unsigned类型最大值十进制为255，十六进制ff mail中的自增字段是int(10），也就是0x20202020,转换成10进制就是538976288，超过了4294967295这个值，此时，系统会以最大值显示。问了下具体的操作人员，确实做了数据库修复的操作。
摘自后附资料 结论一（关于MYSQL确定和更新某个自增字段最大值的方式）：
第一次对某个表创建并填充好结构文件.frm，索引文件.MYI和数据文件.MYD的数据后，首先读取索引文件.MYI中关于这个自增字段的最大值，并且放入内存进行计数，以后所有的自增字段最大值都来源于这个内存计数器；隔一段时间重新读取数据文件中对应字段的确切最大值，并和内存的值做比较，然后用两者中的最大值更新内存的数值和索引文件的数值。
结论二（关于不同的异常修复时对自增字段最大值的影响）：
在MYISAM的存储结构中，数据库数据的异常不一定会导致立刻启动自我修复；并且这种修复不是全面的修复，大多数情况下，它仅仅对数据文件.MYD进行修复，而这种修复并不对索引产生即时的影响（换句话说，自增字段也不会受到即时的影响）；只有当用户要求进行修复的时候，才会全面更新索引（比如输入SQL语句：REPAIR TABLE __TABLE_NAME__）。而一旦更新了索引（无论是手动还是自动更新），对自增字段的影响有可能是爆发性的呈现。 结论一的解释：
在试验时，我发现假如终止MYSQL进程后再重新启动，tid这个自增键值的最大值是直接读取索引文件cdb_threads.MYI确定，而不管是否和数据文件cdb_threads.MYD中的tid最大值是否一致。并且发现，1，只要索引文件损坏并且无法自我修复，这个表就有可能无法读取甚至导致MYSQL进程当掉。2，索引文件并不经常更新，而是有规律的隔段时间写入。
结论二的解释：
我的测试方法很简单，在MYSQL运行时直接修改数据文件的尾部。用PHPMYADMIN打开表的前几页数据，正常；我直接跳到最后一页，这时候MYSQL提示出错，要修复这个表，刷新了两下，自我修复完毕，此时出现一条tid:2105376的无用记录，然而插入新记录时，tid赋予的下一个值仍然是3201；只有当我显式的命令修复过后，才赋予tid下一个值为2105377。
所以，朋友的那个论坛数据库，07年已经出现了异常，然而直到08年12月，才执行了自我修复，然而这种修复在当时没有影响到索引，而是根据前面的结论，继续潜伏一段时间后，才显出tid暴增的问题。
参见 http://blog.sina.com.cn/s/blog_56b798f80100cej6.html~type=v5_one&label=rela_prevarticle
从网上看到一篇文章，mysql在检测到表中有损坏的记录时，会自动修复，为了保证数据的完整性，mysql会以空格（0x20)写进磁盘来完成修复。根据字段的类型，自增字段的长度不同，所允许的最大值也不同。见下： int(10) unsigned类型最大值十进制为4294967295，十六进制ffffffff;
mediumint(8) unsigned类型最大值十进制为16777215，十六进制ffffff；
smallint(6) unsigned类型最大值十进制为65535，十六进制ffff；
tinyint(3) unsigned类型最大值十进制为255，十六进制ff mail中的自增字段是int(10），也就是0x20202020,转换成10进制就是538976288，超过了4294967295这个值，此时，系统会以最大值显示。问了下具体的操作人员，确实做了数据库修复的操作。
摘自后附资料 结论一（关于MYSQL确定和更新某个自增字段最大值的方式）：
第一次对某个表创建并填充好结构文件.frm，索引文件.MYI和数据文件.MYD的数据后，首先读取索引文件.MYI中关于这个自增字段的最大值，并且放入内存进行计数，以后所有的自增字段最大值都来源于这个内存计数器；隔一段时间重新读取数据文件中对应字段的确切最大值，并和内存的值做比较，然后用两者中的最大值更新内存的数值和索引文件的数值。
结论二（关于不同的异常修复时对自增字段最大值的影响）：
在MYISAM的存储结构中，数据库数据的异常不一定会导致立刻启动自我修复；并且这种修复不是全面的修复，大多数情况下，它仅仅对数据文件.MYD进行修复，而这种修复并不对索引产生即时的影响（换句话说，自增字段也不会受到即时的影响）；只有当用户要求进行修复的时候，才会全面更新索引（比如输入SQL语句：REPAIR TABLE __TABLE_NAME__）。而一旦更新了索引（无论是手动还是自动更新），对自增字段的影响有可能是爆发性的呈现。 结论一的解释：
在试验时，我发现假如终止MYSQL进程后再重新启动，tid这个自增键值的最大值是直接读取索引文件cdb_threads.MYI确定，而不管是否和数据文件cdb_threads.MYD中的tid最大值是否一致。并且发现，1，只要索引文件损坏并且无法自我修复，这个表就有可能无法读取甚至导致MYSQL进程当掉。2，索引文件并不经常更新，而是有规律的隔段时间写入。
结论二的解释：
我的测试方法很简单，在MYSQL运行时直接修改数据文件的尾部。用PHPMYADMIN打开表的前几页数据，正常；我直接跳到最后一页，这时候MYSQL提示出错，要修复这个表，刷新了两下，自我修复完毕，此时出现一条tid:2105376的无用记录，然而插入新记录时，tid赋予的下一个值仍然是3201；只有当我显式的命令修复过后，才赋予tid下一个值为2105377。
所以，朋友的那个论坛数据库，07年已经出现了异常，然而直到08年12月，才执行了自我修复，然而这种修复在当时没有影响到索引，而是根据前面的结论，继续潜伏一段时间后，才显出tid暴增的问题。
参见 http://blog.sina.com.cn/s/blog_56b798f80100cej6.html~type=v5_one&label=rela_prevarticle
相关阅读 更多 +
