【原创】sqlite3数据库“SQL error: database disk image is malformed”问题探究（2）

Author: chad
Mail: [e-mail]

本文可以自由转载，但转载请务必注明出处以及本声明信息。

在《sqlite3数据库SQL error: database disk image is malformed问题探究<1>》中对“SQL error: database disk image is malformed”问题已经进行了详细的实验分析，本以为事情已经处理的差不多，但是，对本附件的数据库进行分析后才发现，问题远没有那么简单！对本数据库的实验记录如下：

1. 数据库总体检测：

[root@Chad: data]#sqlite3 terminal.dbSQLite version 3.6.1Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite> PRAGMA integrity_check;                                             *** in database main ***Page 7927: sqlite3BtreeInitPage() returns error code 11On tree page 8366 cell 15: Child page depth differsOn tree page 8366 cell 16: Child page depth differsPage 7928: sqlite3BtreeInitPage() returns error code 11On tree page 8366 cell 20: Child page depth differsOn tree page 8366 cell 21: Child page depth differsPage 2684: sqlite3BtreeInitPage() returns error code 11On tree page 56 cell 40: Child page depth differsOn tree page 56 cell 41: Child page depth differs。。。。。。省去n多行On tree page 636 cell 55: Child page depth differsOn tree page 660 cell 22: Child page depth differsOn tree page 660 cell 23: Child page depth differssqlite> .tablesCurveDataTable       event_tmp            task1mark          DayFreezeTable       groupparam           task2data          MonthFreezeTable     keyuser              task2mark          SysMaintenance       localparam           terminalactive     TerRMStateInfoTable  logininfo            terminalcascade    capacitor            measuringcufe        terminalgroup      carrier              measuringlimit       terminalparam      。。。。。sqlite> select count(*) from DayFreezeTable where id>0;                             SQL error: database disk image is malformed。。。。。省略N多推理尝试sqlite> select count(*) from DayFreezeTable where id<19163;19162sqlite> select count(*) from DayFreezeTable where id=19163;1sqlite> select count(*) from DayFreezeTable where id=19164;SQL error: database disk image is malformed。。。。。。再省去N多尝试sqlite> select count(*) from DayFreezeTable where id=19163;1sqlite> select count(*) from DayFreezeTable where id=19164; SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id=19165;SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id=19166;SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id=19167;1sqlite> select count(*) from DayFreezeTable where id>19167;SQL error: database disk image is malformed。。。。。在经过N多尝试sqlite> select count(*) from DayFreezeTable where id>19167;SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id>29167;SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id>39167;SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id>49167;14103。。。。。。sqlite> select count(*) from DayFreezeTable where id>45167;SQL error: database disk image is malformed。。。。。。sqlite> select count(*) from DayFreezeTable where id>46967;16303sqlite> select count(*) from DayFreezeTable where id>46467;SQL error: database disk image is malformedsqlite> select count(*) from DayFreezeTable where id>46567;16703

测试其他数据表：

sqlite> select count(*) from MonthFreezeTable where id>0;                                         24491sqlite> select count(*) from CurveDataTable where id>0;  SQL error: database disk image is malformedsqlite> select count(*) from terminalparam;            2042sqlite> select count(*) from system;       149sqlite> select count(*) from event; 520sqlite> select count(*) from event_tmp;0

如上所示，这个数据库可以说是千疮百孔，DayFreezeTable 表中分了很多不连续的错误段，CurveDataTable 这个从不使用的表也出现了同样的错误！继续对Cur表进行试验发现如下一个有趣的现象：

sqlite> select count(*) from CurveDataTable where id<19128;                                                                                                    13126sqlite> select count(*) from CurveDataTable where id<23128;13126sqlite> select count(*) from CurveDataTable where id>23128;38304sqlite> select count(*) from CurveDataTable where id<31500;SQL error: database disk image is malformed

即不论是 id<19128 还是 id<23128，返回的统计结果都是13126！同时，id> 23128查询没有出错，返回结果为38304，即23128后面还有38304行记录，38304+ 23128=61432，但是执行id<31500却出错！！！
继续分析如下：

sqlite> select count(*) from CurveDataTable where id<25150;13144sqlite> select count(*) from CurveDataTable where id<25160;13154sqlite> select count(*) from CurveDataTable where id<25170;SQL error: database disk image is malformedsqlite> select count(*) from CurveDataTable where id<25165;13159sqlite> select count(*) from CurveDataTable where id<25168;SQL error: database disk image is malformedsqlite> select count(*) from CurveDataTable where id<25167;13161sqlite> select count(*) from CurveDataTable where id=25167;1sqlite> select count(*) from CurveDataTable where id=25168; 1sqlite> select count(*) from CurveDataTable where id=25169;1sqlite> select count(*) from CurveDataTable where id=25170;1sqlite> select count(*) from CurveDataTable where id=25171;1sqlite> select count(*) from CurveDataTable where id<25171;SQL error: database disk image is malformedsqlite> select count(*) from CurveDataTable where id>25171;36261

可以看到，上面的逻辑已经完全混乱了，可以说该数据表已经烂的不行了！使用shell命令行已经不能够完成该数据库的测试工作了，必需写一个专用的程序对数据库进行完整的测试。

根据前面的经验我们可以明确4点：

1. PRAGMA integrity_check; 测试OK的数据库不一定OK，但是，PRAGMA integrity_check; 测试报错的一定存在错误！

2. 使用SELECT COUNT(*)命令读取表的行数，如果出现“SQL error: database disk image is malformed”错误，说明该表肯定存在问题!但是，如果统计过程没有报错并不能保证数据表不存在问题。

3. 使用区域分解这种2分法进行数据表统计的方法可以快速找到出错的区域，但是，如上文所示的情况下，如果数据库损坏太过严重，则该方法会出现逻辑错误。

4. 使用“select ”（注意：一定要是“select ”，因为如果select colname这种只查询一列的情况可能会正确执行！）一条一条的进行测试肯定能够找到每一条错误数据。