[总结] [原创]主题帖子数值暴跳的背后——略谈MySQL数据库异常对自增字段AUTO_INCREMENT影响

horseluke

文章可能有错误，请各位指正。

题录
本文：
A、揭示538976288，8224等这些数值的含义，略谈MySQL数据库异常对自增字段的影响
B、猜测自增字段最大已编号数值（下称：自增字段最大值）的确定方式，猜测不同的异常修复时对自增字段最大值的影响

/*＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝正文分割线＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝*/
前几天去学校本部，跟一个对Discuz!论坛进行二次开发的朋友聊天，他说他的论坛在08年12月份（当时还是dz6，09年1月份升到dz7）出现一个异常状况，标识主题帖子的tid值无故从1,xxxx一下子暴跳到210,5xxx。也就是说a月b日本来最新的帖子还是：
http://www.xxx.com/viewthread.php?tid=1xxxx
结果下一张帖突然变成了
http://www.xxx.com/viewthread.php?tid=2105xxx
中间空缺了一大段tid值（图1为“案发现场”）。这不太可能啊，tid这个值在Discuz中是自增的，怎么自增自增会自增成这样？


（图1）

/*＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝预备知识＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝*/
A，关于MySQL自增字段【3】：在mysql中，整数型的字段可以配置一个额外的属性就是 AUTO_INCREMENT,当你试图向一个被索引的AUTO_INCREMENT字段中插入一个null或者0值时候。实际字段取值会被设置成为下一个‘序列’值. 也就是说是value+1，而其中的value值为这个字段在表中的当前最大值。每个表中只能存在一个AUTO_INCREMENT的字段。同时这个字段必须被索引，而且也不能为这个字段设置一个默认值（在MYISAM存储类型中，可设置初始值【4】。注意，初始值和默认值不是一个概念）。
B，Mysql中：
int(10) unsigned类型最大值十进制为4294967295，十六进制ffffffff;
mediumint(8) unsigned类型最大值十进制为16777215，十六进制ffffff；
smallint(6) unsigned类型最大值十进制为65535，十六进制ffff；
tinyint(3) unsigned类型最大值十进制为255，十六进制ff
C，十六进制20的十进制为32，二进制表示为00100000，在ASCII中【1】，表示为1个空格。
D，Discuz!的主题是存储在cdb_threads中，dz6的结构为（SQL表示，为方便后文详述，仅摘取几个有关该文章的字段）【2】：

CREATE TABLE `cdb_threads` (
`tid` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`fid` smallint(6) unsigned NOT NULL DEFAULT '0',
`readperm` tinyint(3) unsigned NOT NULL DEFAULT '0',
`authorid` mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` char(80) NOT NULL DEFAULT '',
`dateline` int(10) unsigned NOT NULL DEFAULT '0',
`lastposter` char(15) NOT NULL DEFAULT '',
......
PRIMARY KEY (`tid`),
......
) ENGINE=MyISAM AUTO_INCREMENT=190 DEFAULT CHARSET=gbk

由上面可以看到，tid值在cdb_threads中是自增的。
/*＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝预备知识＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝*/


有的人可能会说，是不是某人直接进入数据库，直接insert了一条tid非常大的数值，根据Mysql的说明：“当插入记录时，如果为AUTO_INCREMENT数据列明确指定了一个数值，并且这个值大于已编号的值，则会把该插入到数据列中，并使在下一个编号将从这个新值开始递增。也就是说，可以跳过一些编号。”这也可以解释主题帖子数值暴跳啊。
然而是否就这么简单呢？要了解这个问题的确切情况，我们还是先让这个表按tid排序（图2）。


（图2）
细心的读者可能会发现，红色标识的记录出现了异常（有乱码），而且这几条数据的dataline，lastpost字段值惊人的一致，都是538976288。
538976288这个值想必经常去摄影网的网友都非常熟悉，特别是全球最大的照片分享网flickr，在google上以关键词“538976288 site:flickr.com”搜索，会得到很多结果。538976288的十六进制表示为20202020。
也许有的人恍然大悟了——4个空格【5】！对！当MYSQL数据库的某个表异常时，会对该表进行自我修复，然而由于已有数据的丢失，为了保持数据的完整性，它只能以0x20（也就是一个空格）写入磁盘来完成修复。因而我们可以看到，在修复过后，数值型int(10) unsigned的dateline及lastpost字段会显示为538976288（0x20202020），smallint(6) unsigned的iconid，typeid，readperm会显示8224（0x2020）......而作为字符型char的subject，lastposter等出现了乱码或者为空（空格）。
（备注：sortid字段为dz6升级到dz7时增加的字段，而这是在数据库发生异常的一个月后了，因此没有受到影响。）
所以，修复过后，出现了修复好的无用数据，这些数据tid起始值为2105376（0x202020），而这个值则影响了自增字段tid的最大值，因而就出现了主题帖子数值暴跳的现象。


/*＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝正文分割线＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝*/

当然，另外一个问题产生了，数据库修复会立刻影响到自增字段最大值吗？


（图3）
图3是用dateline进行排序的结果。留意tid:9294和tid:9377，这是两条有问题的数据，但是事实上在discuz中显示正常（07年数据）。另外，tid:2105376这条修复好的无用记录，经过对subject保留的信息进行分析后，朋友告诉我，这张帖是在07年的时候就删除了（排除了使用回收站功能），但是没想到原来还留在数据库。然而根据图1的信息，这次数据库修复时间是发生在2008年12月5日。因此一个问题产生了：既然在2007年数据库都已经有异常了，为什么只有2008年12月5日的修复才导致这样的结果？
带着这个疑问，我在本地做了几个实验。方法主要是用不同的方式破坏数据库（包括物理文件或者进程，或者两者均有），然后观察不同的修复方式对自增起始值的影响。结果得出两个结论：
结论一（关于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暴增的问题。


/*＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝正文分割线＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝*/

参考文献
【1】Ascii. http://baike.baidu.com/view/15482.htm. 检索日期：2009-2-28
【2】习明. 《Discuz!6.0数据字典》非官方不完美版. 2007-11-19. http://www.freediscuz.net/bbs/vi ... =3168&extra=&page=1
【3】leader_bin. 关于mysql的自增 auto_increament 的阅读笔记. 2008-09-21.  17:10http://hi.baidu.com/leader_bin/b ... af3e15b17ec509.html
【4】中国自学编程网. MySQL的AUTO_INCREMENT. 2008-10-10. http://soft.ccw.com.cn/programin ... 081010_517910.shtml
【5】在DLL中弹出对话框出错的问题. http://social.microsoft.com/Foru ... -9514-2af03f7d084b/ . 检索日期：2009-2-28

[ 本帖最后由 horseluke 于 2009-2-28 19:00 编辑 ]

horseluke

对这个不太明白的话可看看这个网站的实例：
http://fzlkj.5d6d.com/viewthread.php?tid=1130

会员数据是不是乱了套呢？

yafeikf

先留脚印。。

ironcrane

坐下来，弄杯茶，慢慢看

阿辛

那如果遇到这样的情况，解决方案是什么呢？
就这样任由它按暴跳后的id开始自增吗？

horseluke

原帖由 阿辛 于 2009-2-28 21:26 发表
那如果遇到这样的情况，解决方案是什么呢？
就这样任由它按暴跳后的id开始自增吗？

参考文献中的[4]提到用

alter table __TABLE_NAME__ AUTO_INCREMENT=n

来解决，但是尝试后发现不行
所以，我也不知道如何处理..........

Deman

写得很详细

阿辛

原帖由 horseluke 于 2009-2-28 22:03 发表


参考文献中的[4]提到用

alter table __TABLE_NAME__ AUTO_INCREMENT=n

来解决，但是尝试后发现不行
所以，我也不知道如何处理..........

好的，谢谢分享，文章很棒！

coolstr

非常有心写的很精彩的一篇文章,

已加精华.

horseluke

http://topic.csdn.net/t/20041024/21/3486709.html
http://social.technet.microsoft. ... -be57-d8655b60e07c/

微软的SQL SERVER（似乎）比较明确的指出，若在SQL Server 出现含有“538976288”的错误提示，一般为page cross link问题。修复方法请参考上面两个链接