【原创】sqlite3数据库SQL error: database disk image is malformed问题探究
Author: chad
Mail: linczone@163.com
本文可以自由转载,但转载请务必注明出处以及本声明信息。
《附件内为有“database disk image is malformed”错误的实验用数据库》
如下所示,实际使用过程中发现有的数据库问题千奇百怪,常见有如下现象:
- 数据库文件部分损坏,表现例如:4M的数据库只剩下1M大小,数据库中大部分表已经不存在,但是一部分表扔可正常访问。
- 数据库文件大小没有明显变化,但是如果使用PRAGMA integrity_check;命令进行检查会检查出问题。
- 数据库文件大小没有明显变化,并且使用PRAGMA integrity_check;命令检测ok,但是更新损坏的表时出现错误,查询等其他操作不会出错。如下例所示:
[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(1111111,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,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,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