略谈MySQL数据库异常对自增字段AUTO_INCREMENT影响

完整标题:主题帖子数值暴跳的背后——略谈MySQL数据库异常对自增字段AUTO_INCREMENT影响

讨论链接:http://www.phpchina.com/bbs/thread-106637-1-1.html

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

题录:本文:
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中是自增的,怎么自增自增会自增成这样?

==================预备知识==================
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

Leave a Reply

Your email address will not be published.