【原创】sqlite3数据库SQL error: database disk image is malfo
【原创】sqlite3数据库SQL error: database disk image is malformed问题探究
本文可以自由转载,但转载请务必注明出处以及本声明信息。 《附件内为有“database disk image is malformed”错误的实验用数据库》
[root@Chad: /home]#sqlite3 /tiandao/data/terminal.db
SQLite version 3.6.1
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> PRAGMA integrity_check;
ok
sqlite> .tables
CurveDataTable event task1data
DayFreezeTable event_tmp task1mark
MeterEventData groupparam task2data
MonthFreezeTable keyuser task2mark
SysMaintenance localparam terminalactive
TerRMStateInfoTable logininfo terminalcascade
capacitor measuringcufe terminalgroup
carrier measuringlimit terminalparam
comportinfo measuringparam terminalpulse
data1config measuringpoint terminalrate
data1value mlogininfo terminalvi
data2config rate totalgroup
data2value readingmeter vpac
dcanalog readtime vpacvalue
dlogininfo system
sqlite> select * from DayFreezeTable where id=222;
222|0|||||||||||||||||||0
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3331;
SQL error: database disk image is malformed
sqlite> .exit
如上所示,更新某一条记录时才会提示数据结构损坏。但是插入新的数据条目却正常,如下所示: sqlite> .schema DayFreezeTable
CREATE TABLE DayFreezeTable(
id INTEGER PRIMARY KEY,TimeScale date,ForwardPowerInd blob,ReactivePowerInd_1 blob,ReversePowerInd blob,ReactivePowerInd_2 blob,OneQuadrantReactivePower blob,TwoQuadrantReactivePower blob,ThreeQuadrantReactivePower blob,FourQuadrantReactivePower blob,ForwardPower blob,ReactivePower_1 blob,ReversePower blob,ReactivePower_2 blob,ForwardActiveDemand blob,ReverseActiveDemand blob,ForwardReactiveDemand blob,ReverseReactiveDemand blob,PhasePowerTime blob,PhaseDemandTime blob,cmdbitmap integer default(0));
sqlite> insert into DayFreezeTable values(2222211,22);
SQL error: table DayFreezeTable has 21 columns but 2 values were supplied
sqlite> select * from DayFreezeTable limit 2;
1|0|
.L*!*|
铑顋
|
|||||||||||||||0
2|0|||||||||||||||||||0
sqlite> insert into DayFreezeTable values(0,0,0);
sqlite> select * from DayFreezeTable limit 2;
0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
1|0|
.L*!*|
铑顋
|
|||||||||||||||0
sqlite> update DayFreezeTable set TimeScale=1 where id=0;
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3331;
SQL error: database disk image is malformed
再一步试验如下: sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=331;
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=332;
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>100;
SQL error: database disk image is malformed
sqlite> select * from DayFreezeTable where TimeScale<>'2013-03-03' limit 3;
0|1|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
1|0|
.L*!*|
铑顋
|
|||||||||||||||0
2|0|||||||||||||||||||0
sqlite> select * from DayFreezeTable where TimeScale<>'2013-03-03' and id > 331 limit 3;
332|2013-03-02|||||||||||||||||||0
333|0|||||||||||||||||||0
334|0|||||||||||||||||||0
sqlite> select * from DayFreezeTable where TimeScale<>'2013-03-03' and id > 100 limit 3;
101|0| )铑铑铑铑铑铑铑铑铑铑铑铑顋 )铑铑铑铑铑铑铑铑铑铑| )铑铑铑铑铑铑铑铑铑铑铑铑顋 )铑铑铑铑铑铑铑铑铑铑|||||||||||||||0
102|0| )铑铑铑铑铑铑铑铑铑铑铑铑顋 )铑铑铑铑铑铑铑铑铑铑| )铑铑铑铑铑铑铑铑铑铑铑铑顋 )铑铑铑铑铑铑铑铑铑铑|||||||||||||||0
103|0| )铑铑铑铑铑铑铑铑铑铑铑铑顋 )铑铑铑铑铑铑铑铑铑铑| )铑铑铑铑铑铑铑铑铑铑铑铑顋 )铑铑铑铑铑铑铑铑铑铑|||||||||||||||0
上面的试验说明了2个问题:
上面的试验中说明第3331行数据存在问题,如下: sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3331;
SQL error: database disk image is malformed
再次基础上继续试验如下: sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>100 and id<3331;
sqlite> select * from DayFreezeTable where id > 3300 limit 3;
3301|2013-03-02|F|F|F|F|||||||||F|F|||||0
3302|2013-03-02|V|V|W|W|||||||||W|W|||||0
3303|2013-03-02|F|F|F|F|||||||||F|F|||||0
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>3331 and id<3500;
SQL error: database disk image is malformed
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>3332 and id<3500;
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3332;
SQL error: database disk image is malformed
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3333;
以上试验更进一步的证实了,即便出现database disk image is malformed错误,可能只是个别条目问题,本数据库的问题目前看来仅存在与id=3331与id=3332两个条目。 sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3332;
SQL error: database disk image is malformed
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id=3331;
SQL error: database disk image is malformed
sqlite> select * from DayFreezeTable where id>3330 limit 5;
SQL error: database disk image is malformed
sqlite> select * from DayFreezeTable where id=3331;
SQL error: database disk image is malformed
sqlite> select * from DayFreezeTable where id=3332;
SQL error: database disk image is malformed
sqlite> select * from DayFreezeTable where id=3333;
3333|201-01-17||||||||||||||||||
sqlite> select count(*) from DayFreezeTable;
63270
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>3500;
sqlite> select * from DayFreezeTable where id>63266;
63267|2013-03-02|||||||||||||||||||0
63268|2013-03-02|||||||||||||||||||0
63269|2013-03-02|||||||||||||||||||0
63270|2013-03-02|||||||||||||||||||0
sqlite> select count(*) from MonthFreezeTable;
24491
sqlite> UPDATE MonthFreezeTable SET TimeScale='2013-03-02' where id>0;
sqlite> UPDATE CurveFreezeTable SET TimeScale='2013-03-02' where id>0;
SQL error: no such table: CurveFreezeTable
sqlite> UPDATE CurveDataTable SET TimeScale='2013-03-02' where id>0;
sqlite> alter table DayFreezeTable add column cmdbitmap integer default(0);
sqlite> .schema DayFreezeTable
CREATE TABLE DayFreezeTable(
id INTEGER PRIMARY KEY,cmdbitmap integer default(0));
如上所示,证实本数据库确实只有DayFreezeTable 表中的两个条目出现问题!而其他常用的数据表并不存在问题。同时,上述实验还揭示了一个重要问题,即出现“database disk image is malformed”类型错误的数据条目查询也会报错!但执行select count(*)种查询表整体信息或alter table这种改变表整体结构的操作没有问题。只有操作问题条目时才出错,如下: sqlite> select id,TimeScale,ForwardPowerInd from DayFreezeTable where id=3332;
3332|2013-01-16|
sqlite> select id,ForwardPowerInd,ReactivePowerInd_1 from DayFreezeTable where id=3332;
3332|2013-01-16||0
。。。。。。
sqlite> select id,PhasePowerTime from DayFreezeTable where id=3332;
3332|2013-01-16|
sqlite> select id,PhaseDemandTime from DayFreezeTable where id=3332;
SQL error: database disk image is malformed
如上所示,出错的仅仅是最后一列,对前面的列进行查询是不会报错的。那么id=3331是什么情况呢?试验如下: sqlite> select id,PhasePowerTime from DayFreezeTable where id=3331;
3331|201-01-16|
sqlite> select id,PhaseDemandTime from DayFreezeTable where id=3331;
SQL error: database disk image is malformed
出错的同样是最后一列!但是不是所有出错的都只可能是从后面开始呢?在《sqlite3数据库SQL error: database disk image is malformed问题探究<2>》中,有一个损坏更严重的数据库,此处先将《2》中数据库本部分相关的试验总结如下: sqlite> select id,PhasePowerTime from DayFreezeTable where id=19164;
SQL error: database disk image is malformed
。。。。
sqlite> select id,TimeScale from DayFreezeTable where id=19164;
SQL error: database disk image is malformed
sqlite> select id from DayFreezeTable where id=19164;
SQL error: database disk image is malformed
以上是试验摘要,结果是id=19164行的所有数据均出错!并且,经测试发现同一区段的其他坏行现象一致。对其他损坏段的坏行进行测试,发现也是全部损坏。并且,测试中发现《2》中数据库的损坏行多达连续数百行! 修复方法如下所示: sqlite> delete from DayFreezeTable where id=3331;
sqlite> delete from DayFreezeTable where id=3332;
sqlite> insert into DayFreezeTable values(3331,>
sqlite> insert into DayFreezeTable values(3332,>
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>3330 and id<3333;
sqlite> select * from DayFreezeTable where id > 3330 limit 5;
3331|2013-03-02|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
3332|2013-03-02|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
3333|2013-03-02|||||||||||||||||||0
3334|2013-03-02|||||||||||||||||||0
3335|2013-03-02|||||||||||||||||||0
如上所示,修复方法就是将出错的条码删除,然后重建即可! 总结,通过上面的实验证实如下结论:
sqlite> delete from DayFreezeTable where id=3331;
sqlite> delete from DayFreezeTable where id=3332;
sqlite> insert into DayFreezeTable values(3331,>
sqlite> UPDATE DayFreezeTable SET TimeScale='2013-03-02' where id>3330 and id<3333;
sqlite> select * from DayFreezeTable where id > 3330 limit 5;
3331|2013-03-02|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
3332|2013-03-02|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0
3333|2013-03-02|||||||||||||||||||0
3334|2013-03-02|||||||||||||||||||0
3335|2013-03-02|||||||||||||||||||0
如上所示,修复方法就是将出错的条目删除,然后重建即可! 待决问题:
(编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |