FIX for The database disk image is malformed Topic is solved

[MaffeMuis]

Don;t know where to put this:

I had a crash with my Raspberry Pi

After I have replaced the flashdisk and a restore of the config of domoticz I had the following error in the log:

The database disk image is malformed

Because I could not found any fix and the only thing I found on this forum is that you need to Check if you have an class10 SD card, I was looking for an solution.

The FIX::

You need to have sqlite3 installed on your Pi

Code: Select all

sudo apt-get install sqlite3 

If you have it you can do the following commands:

Code: Select all

cd ~
cd domoticz
sqlite3 domoticz.db
.mode insert
.output dump.sql
.dump
.exit

Now you need to download "dump.sql" to your PC. You can find it in your domoticz dir.
You open this file with notepad on yout PC and remove only the first line of the file and save this.

If you have done that, you need to open FireFox and install the next plugin:

Code: Select all

https://addons.mozilla.org/en-us/firefox/addon/sqlite-manager/

You open the plugin in FF "sqlite-manager" and create an new database
Now you import "dump.sql" (if everything is oke, you don't get an error).
Now you close the database and go to the location where you create the database file.

Rename that file that ends with .sqlite to domoticz.db

Now you can go back to your raspberry Pi and rename domoticz.db

Code: Select all

mv domoticz.db domoticz1.db

Upload the new file "domoticz.db to domoticz location. Check If the file have the user Pi rights
And restart you domoticz

Code: Select all

cd scripts
./restart_domoticz

Hope if someone have this problem this can help.

[Mozart]

I just used this procedure to fix an error on my domoticz.db and it worked!

Thanks

[woody4165]

Hi

I'm trying this method since I get the malformed error.

When importing the dump.sql I get this error:

Code: Select all

SQLiteManager: 
BEGIN TRANSACTION; [ cannot start a transaction within a transaction ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageStatement.execute]

I've tried to import the dump into the new db with and without the option "BEGIN TRANSACTION/COMMIT" but I get the same error.

What should I check?

Thanks

[ben53252642]

SQLiteManager:
BEGIN TRANSACTION; [ cannot start a transaction within a transaction ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageStatement.execute]

I got the same error message, solution for me was:

apt-get install sqlite3

cd ~
cd domoticz
sqlite3 domoticz.db
.mode insert
.output dump.sql
.dump
.exit

sqlite3 -init dump.sql repaired.db

You can then move the domoticz.db and replace it with repaired.db (rename using: mv repaired.db domoticz.db)

Separately if you want to check a database to see if it's corrupt you can use these commands:

sqlite3 domoticz.db
pragma integrity_check;
.exit

Output for the corrupt database in my case was:

Screen Shot 2016-11-19 at 5.21.50 pm.png (120.87 KiB) Viewed 40738 times

And for my new repaired database the output looks like this:

Screen Shot 2016-11-19 at 5.23.56 pm.png (39.93 KiB) Viewed 40738 times

[ahattink]

Guys,

I think I need a bit of help here.
A few weeks ago i had the same issue described in this thread an could solve it folowing the steps. But now i am getting the folowing errors, which I cannot resolve whith the described steps.
All devices are lost and no data available in Domoticz, when the " repaired" database is active. Looks like an clean install.
At the moment i went back to the defect database.

Any ideas on how I can fix this and not lose all my data?

Code: Select all

 2017-05-01 16:05:01.625 Error: SQL Query("DELETE FROM Temperature WHERE strftime('%s',datetime('now','localtime')) - strftime('%s',Date) > (SELECT p.nValue * 86400 From Preferences AS p WHERE p.Key='5MinuteHistoryDays')") : database disk image is malformed
2017-05-01 16:10:01.723 Error: SQL Query("DELETE FROM Temperature WHERE strftime('%s',datetime('now','localtime')) - strftime('%s',Date) > (SELECT p.nValue * 86400 From Preferences AS p WHERE p.Key='5MinuteHistoryDays')") : database disk image is malformed
2017-05-01 16:15:00.828 Error: SQL Query("DELETE FROM Temperature WHERE strftime('%s',datetime('now','localtime')) - strftime('%s',Date) > (SELECT p.nValue * 86400 From Preferences AS p WHERE p.Key='5MinuteHistoryDays')") : database disk image is malformed
2017-05-01 16:20:01.000 Error: SQL Query("DELETE FROM Temperature WHERE strftime('%s',datetime('now','localtime')) - strftime('%s',Date) > (SELECT p.nValue * 86400 From Preferences AS p WHERE p.Key='5MinuteHistoryDays')") : database disk image is malformed
2017-05-01 16:25:01.328 Error: SQL Query("DELETE FROM Temperature WHERE strftime('%s',datetime('now','localtime')) - strftime('%s',Date) > (SELECT p.nValue * 86400 From Preferences AS p WHERE p.Key='5MinuteHistoryDays')") : database disk image is malformed
2017-05-01 16:30:01.581 Error: SQL Query("DELETE FROM Temperature WHERE strftime('%s',datetime('now','localtime')) - strftime('%s',Date) > (SELECT p.nValue * 86400 From Preferences AS p WHERE p.Key='5MinuteHistoryDays')") : database disk image is malformed 

Domoticz V 3.7013 running on a raspberry.
Hardwware present:
[16 Kodi Yes Kodi Media Server Setup Disabled
15 SmartMeter Yes P1 Smart Meter USB /dev/ttyUSB2 Disabled
13 Anna Yes Plugwise Anna Thermostat via LAN interface 192.168.2.4 80 Disabled
12 Weer Yes Weather Underground WWW Disabled
11 EnOcean Yes EnOcean USB (ESP3) /dev/ttyAMA0 Disabled
10 WOL Yes Wake-on-LAN Setup 192.168.2.255 9 Disabled
9 Philips Hue Bridge 1.0 Yes Philips Hue Bridge 192.168.2.29 80 Disabled
6 Motherboard Yes Motherboard sensors Disabled
5 Dummy Yes Dummy (Does nothing, use for virtual switches only) Create Virtual Sensors Disabled
4 ZWAVE Yes OpenZWave USB
Version: 1.4-2435-gc61d20d-dirty Setup /dev/ttyUSB0 Disabled
3 RFXtrx Yes RFXCOM - RFXtrx433 USB 433.92MHz Transceiver
Version: Ext/1015 Set Mode /dev/ttyUSB1 Disabled[/code]


Thanks.

Arthur
Yes, there are erros in the database:

Code: Select all

*** in database main ***
Page 5443: btreeInitPage() returns error code 11
On tree page 438 cell 43: Child page depth differs
On tree page 438 cell 44: Child page depth differs
Page 5085: btreeInitPage() returns error code 11
On tree page 44 cell 36: Child page depth differs
On tree page 5086 cell 8: Rowid 830 out of order (previous was 831)
On tree page 5086 cell 9: Rowid 32 out of order (previous was 830)
On tree page 5086 cell 12: Rowid 832 out of order (previous was 833)
On tree page 5086 cell 15: Rowid 837 out of order (previous was 2884)
On tree page 5086 cell 16: Rowid 582 out of order (previous was 837)
Multiple uses for byte 638 of page 5086
Fragmentation of 45 bytes reported as 0 on page 5086
On tree page 44 cell 37: Child page depth differs
On tree page 5087 cell 3: Rowid 846 out of order (previous was 1869)
On tree page 5087 cell 4: Rowid 6 out of order (previous was 846)
On tree page 5087 cell 7: Rowid 850 out of order (previous was 881)
On tree page 5087 cell 10: Rowid 2 out of order (previous was 852)
On tree page 5087 cell 14: Rowid 48 out of order (previous was 856)
On tree page 5087 cell 20: Rowid 863 out of order (previous was 2910)
Corruption detected in cell 14 on page 5087
Corruption detected in cell 20 on page 5087
Multiple uses for byte 17 of page 5087
On tree page 5093 cell 8: Rowid 977 out of order (previous was 978)
On tree page 5093 cell 9: Rowid 45 out of order (previous was 977)
On tree page 5093 cell 11: Rowid 51 out of order (previous was 979)
On tree page 5093 cell 15: Rowid 6 out of order (previous was 983)
On tree page 5093 cell 18: Rowid 7 out of order (previous was 986)
On tree page 5093 cell 20: Rowid 989 out of order (previous was 5084)
Corruption detected in cell 3 on page 5093
Corruption detected in cell 15 on page 5093
Multiple uses for byte 529 of page 5093
Fragmentation of 102 bytes reported as 0 on page 5093
On tree page 5100 cell 5: Rowid 51 out of order (previous was 1120)
On tree page 5100 cell 6: Rowid 45 out of order (previous was 51)
On tree page 5100 cell 11: Rowid 6 out of order (previous was 1126)
On tree page 5100 cell 16: Rowid 64 out of order (previous was 1131)
On tree page 5100 cell 16: invalid page number 2100441137
Corruption detected in cell 9 on page 5100
Corruption detected in cell 14 on page 5100
Multiple uses for byte 529 of page 5100
Fragmentation of 93 bytes reported as 0 on page 5100
On tree page 4329 cell 8: Rowid 54 out of order (previous was 98)
On tree page 4329 cell 20: Rowid 20 out of order (previous was 110)
On tree page 4329 cell 21: Rowid 5 out of order (previous was 20)
On tree page 4329 cell 21: invalid page number 808531501
On tree page 4329 cell 26: Rowid 33 out of order (previous was 116)
On tree page 4329 cell 43: Rowid 134 out of order (previous was 4229)
Page 4329: Rowid 8327 out of order (max larger than parent max of 135)
Corruption detected in cell 1 on page 4329
Corruption detected in cell 10 on page 4329
Corruption detected in cell 18 on page 4329
Corruption detected in cell 27 on page 4329
Multiple uses for byte 619 of page 4329
Fragmentation of 57 bytes reported as 0 on page 4329
On tree page 4330 cell 4: Rowid 132 out of order (previous was 139)
On tree page 4330 cell 21: Rowid 157 out of order (previous was 668)
On tree page 4330 cell 32: Rowid 48 out of order (previous was 167)
Page 4330: Rowid 136 out of order (min less than parent min of 8327)
Corruption detected in cell 18 on page 4330
Corruption detected in cell 22 on page 4330
Multiple uses for byte 821 of page 4330
Fragmentation of 66 bytes reported as 0 on page 4330
On tree page 4331 cell 2: Rowid 148 out of order (previous was 179)
On tree page 4331 cell 3: Rowid 5 out of order (previous was 148)
On tree page 4331 cell 7: Rowid 185 out of order (previous was 440)
On tree page 4331 cell 8: Rowid 102 out of order (previous was 185)
On tree page 4331 cell 31: Rowid 209 out of order (previous was 8400)
On tree page 4331 cell 37: Rowid 151 out of order (previous was 214)
On tree page 4331 cell 41: Rowid 219 out of order (previous was 4314)
Corruption detected in cell 0 on page 4331
Corruption detected in cell 3 on page 4331
Corruption detected in cell 14 on page 4331
Corruption detected in cell 32 on page 4331
Multiple uses for byte 149 of page 4331
Page 4350: btreeInitPage() returns error code 11
On tree page 20 cell 23: Child page depth differs
On tree page 20 cell 24: Child page depth differs
Page 4353: btreeInitPage() returns error code 11
On tree page 20 cell 26: Child page depth differs
On tree page 20 cell 27: Child page depth differs
On tree page 4356 cell 1: Rowid 1222 out of order (previous was 5317)
On tree page 4356 cell 3: Rowid 48 out of order (previous was 5319)
On tree page 4356 cell 12: invalid page number 554814724
On tree page 4356 cell 13: Rowid 1234 out of order (previous was 11908)
On tree page 4356 cell 16: Rowid 1237 out of order (previous was 1238)
On tree page 4356 cell 18: Rowid 50 out of order (previous was 1238)
On tree page 4356 cell 21: Rowid 55 out of order (previous was 1241)
On tree page 4356 cell 25: Rowid 52 out of order (previous was 1245)
On tree page 4356 cell 28: Rowid 5 out of order (previous was 1248)
On tree page 4356 cell 28: invalid page number 758133037
On tree page 4356 cell 34: Rowid 1255 out of order (previous was 9446)
Multiple uses for byte 390 of page 4356
Fragmentation of 17 bytes reported as 0 on page 4356
On tree page 4357 cell 2: Rowid 9 out of order (previous was 1263)
On tree page 4357 cell 4: Rowid 9 out of order (previous was 1265)
On tree page 4357 cell 9: Rowid 2 out of order (previous was 1270)
On tree page 4357 cell 26: Rowid 1288 out of order (previous was 1301)
On tree page 4357 cell 27: Rowid 8 out of order (previous was 1288)
On tree page 4357 cell 32: Rowid 10 out of order (previous was 1293)
On tree page 4357 cell 35: Rowid 1297 out of order (previous was 1297)

[ahattink]

An update from me.
I was able to fix the database with DB Browser for SQlite.
Basically open your database with DB Browser for SQlite, safe as an SQL file, create new database and import the SQL file. Takes a while, but result is there. Detailed explanation on the following site: http://wordpress.semnaitik.com/2017/02/ ... -database/

Yet to solve a new issue. When i press the button of waking an device through Wake on Lan, the Domoticz process hangs and after a while it will be restarted by Monit. No errors what so ever. (Just Monit informing you the proces does not exist and has been restarted)

[lrybak]

You open the plugin in FF "sqlite-manager" and create an new database
Now you import "dump.sql" (if everything is oke, you don't get an error).
Now you close the database and go to the location where you create the database file.

Rename that file that ends with .sqlite to domoticz.db

Now you can go back to your raspberry Pi and rename domoticz.db

Code: Select all

mv domoticz.db domoticz1.db

You don't have to use Firefox addon to create new sqlite db. Use following instead

Code: Select all

sqlite3 domoticz1.db < dump.sql

[MikeyP]

And even quicker, staying on the RPI prompt, without copying to pc or manually editing:

Code: Select all

# Remove first line of dumped database:
tail dump.sql -n +2 > dump1.sql
# Make back-up of original just in case:
mv domoticz.db domoticz.bak.db
# Import into fresh database:
sqlite3 domoticz.db < dump1.sql
# Clean-up the temporary files:
rm dump*.sql

Matter of seconds, and the error messages are gone

[brommetje]

Hi all, have a problem with my lets say production DB what I noticed that I do not have any 24 hourly graphs any more.
So I decided to check my DB with "pragma integrity_check;".

Code: Select all

pi@snoepie:~/domoticz $ sqlite3 domoticz.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> pragma integrity_check;
*** in database main ***
On tree page 46898 cell 13: 2nd reference to page 38523
On tree page 42907 cell 2: 2nd reference to page 38521
On tree page 48821 cell 21: 2nd reference to page 38524
On tree page 43283 cell 36: 2nd reference to page 38525
On tree page 49870 cell 10: 2nd reference to page 38526
On tree page 37221 cell 0: 2nd reference to page 38527
On tree page 35683 cell 0: 2nd reference to page 38522
Page 35478 is never used
Page 35479 is never used
Page 36383 is never used
Page 37016 is never used
Page 37017 is never used
row 191 missing from index ds_hduts_idx
row 320 missing from index ds_hduts_idx
row 324 missing from index ds_hduts_idx
wrong # of entries in index ds_hduts_idx
wrong # of entries in index sqlite_autoindex_UserSessions_2
Error: database disk image is malformed
sqlite>

After that I dumped my DB without any errors and restored it to a new repaired domoticz.db again.
Check with "pragma integrity_check;" again and every thing is OK now.

But if I start Domoticz again I lost my complete configuration no devices, switches etc.

How can I resolve my DB (consistence) again without losing my configuration?

Help.

[waaren]

Hi all, have a problem with my lets say production DB what I noticed that I do not have any 24 hourly graphs any more.
So I decided to check my DB with "pragma integrity_check;".

Code: Select all

pi@snoepie:~/domoticz $ sqlite3 domoticz.db
SQLite version 3.16.2 2017-01-06 16:32:41
Enter ".help" for usage hints.
sqlite> pragma integrity_check;
*** in database main ***
On tree page 46898 cell 13: 2nd reference to page 38523
On tree page 42907 cell 2: 2nd reference to page 38521
On tree page 48821 cell 21: 2nd reference to page 38524
On tree page 43283 cell 36: 2nd reference to page 38525
On tree page 49870 cell 10: 2nd reference to page 38526
On tree page 37221 cell 0: 2nd reference to page 38527
On tree page 35683 cell 0: 2nd reference to page 38522
Page 35478 is never used
Page 35479 is never used
Page 36383 is never used
Page 37016 is never used
Page 37017 is never used
row 191 missing from index ds_hduts_idx
row 320 missing from index ds_hduts_idx
row 324 missing from index ds_hduts_idx
wrong # of entries in index ds_hduts_idx
wrong # of entries in index sqlite_autoindex_UserSessions_2
Error: database disk image is malformed
sqlite>

After that I dumped my DB without any errors and restored it to a new repaired domoticz.db again.
Check with "pragma integrity_check;" again and every thing is OK now.

But if I start Domoticz again I lost my complete configuration no devices, switches etc.

How can I resolve my DB (consistence) again without losing my configuration?

Help.

Can you please describe in more detail the action you did for the steps "dumped my DB without any errors and restored it to a new repaired domoticz.db again"

what do you see if if you do

Code: Select all

sudo sqlite3 new database
sqlite> .tables
sqlite> select * from devicestatus;
sqlite> select * from hardware;
sqlite> .schema

[brommetje]

@waaren, what I did till now is (p.s. my malformed DB is still running)
$ /usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > /SYNDS/backup/dump.db
$ sqlite3 dump.db

Code: Select all

sqlite> select count(*) from hardware; 17
sqlite> select * from hardware;
2|WeatherUnderground|1|14||1||ad80610de1e7671e|herwijnen_netherlands||0|0|0|0|0|0|0
3|RFXtrx433|1|1||0|/dev/ttyUSB-RFX433||||83|25|10|8|47|0|0
5|DummySwitch|1|15||1|||||0|0|0|0|0|0|0
...
sqlite> select count(*) from DeviceStatus; 471
sqlite> select * from DeviceStatus;
6|2|1|0|Wind|1|86|4|0|1|12|255|0|198.00;SSW;27;41;6.8;5.0|2019-01-05 13:59:26|6|0.0|1.0|0.0|1.0|||0|0|0|||
7|2|1|0|UV index|1|87|1|0|0|12|255|0|0.0;0.0|2019-01-05 13:59:26|7|0.0|1.0|0.0|1.0|||0|0|0|||
8|2|1|0|Regen|1|85|112|0|1|12|255|0|0;0.0|2019-01-05 13:59:26|8|0.0|1.0|0.0|1.0|||0|0|0|||
9|2|0|1|Zicht|1|243|1|0|0|12|255|0|9.0|2019-01-05 13:59:26|9|0.0|1.0|0.0|1.0|||0|0|0|||
..
sqlite> .schema
CREATE TABLE [LightingLog] ([DeviceRowID] BIGINT(10) NOT NULL, [nValue] INTEGER DEFAULT 0, [sValue] VARCHAR(200), [Date] DATETIME DEFAULT (datetime('now','localtime')), [User] VARCHAR(100) DEFAULT (''));
...
CREATE TRIGGER floorplanordertrigger AFTER INSERT ON Floorplans
BEGIN
        UPDATE Floorplans SET [Order] = (SELECT MAX([Order]) FROM Floorplans)+1 WHERE Floorplans.ID = NEW.ID;
END;

So everything looks OK
I can resolv the indexen with the reindex commands

Code: Select all

sqlite> reindex ds_hduts_idx;
sqlite> reindex sqlite_autoindex_UserSessions_2;

then I only have the following messages but the the question how can I resolve this?

Code: Select all

sqlite> pragma integrity_check;
*** in database main ***
On tree page 46898 cell 13: 2nd reference to page 38523
On tree page 42907 cell  2: 2nd reference to page 38521
On tree page 48821 cell 21: 2nd reference to page 38524
On tree page 43283 cell 36: 2nd reference to page 38525
On tree page 49870 cell 10: 2nd reference to page 38526
On tree page 37221 cell  0: 2nd reference to page 38527
On tree page 35683 cell  0: 2nd reference to page 38522
Page 35478 is never used
Page 35479 is never used
Page 36383 is never used
Page 37016 is never used
Page 37017 is never used					 No more index messages !!!
Error: database disk image is malformed

the silly thing is when I check the counts again if have 473 entries instead of 471 before in my DeviceStatus table

Code: Select all

sqlite> select count(*) from DeviceStatus; 473
sqlite> select count(*) from hardware; 17

How can I solve the other problems?

[jvdz]

You could try this approach by dumping the current database and loading the dumpfile into a new database like this bash file:

Code: Select all

#! /bin/sh
sudo service domoticz stop
cd /home/pi/domoticz
rm -f dump.sql
echo Dump domoticz.db to dump.sql
sudo sqlite3 domoticz.db  <<EOF
.mode insert
.output dump.sql
.dump
.exit
EOF

rm -f domoticz.fixed.db
echo create new domoticz.fixed.db loading dump.sql
sudo sqlite3 domoticz.fixed.db  <<EOF
.read dump.sql
.exit
EOF

After this you only need to rename the old domoticz.db file to something else and the new domoticz.fixed.db to domoticz.db.
Worth try?

Jos

[brommetje]

@jvdz I tried your bash file and same problem as before "no devices".

No devices.gif (60.34 KiB) Viewed 30154 times

if I then run "sqlite> pragma integrity_check;" everything is OK but no devices, so no configuration.

[waaren]

@waaren, what I did till now is (p.s. my malformed DB is still running)
I can resolv the indexen with the reindex commands
then I only have the following messages....
How can I solve the other problems?

Code: Select all

 sudo service domoticz stop
sudo cp domoticz.db domoticz.2repair
sudo service domoticz start

sudo sqlite3 domoticz.2repair
sqlite>.out dump.sql
sqlite>.dump
sqlite>.quit
sudo sqlite3 domoticz.repaired < dump.sql 
sudo sqlite3 domoticz.repaired
sqlite> pragma integrity_check;
sqlite> other checks you want to do
sqlite> .quit
sudo service domoticz stop
sudo cp domoticz.db domoticz.just2beSure
sudo cp domoticz.repaired domoticz.db
sudo service domoticz start

[jvdz]

@jvdz I tried your bash file and same problem as before "no devices".
No devices.gif if I then run "sqlite> pragma integrity_check;" everything is OK but no devices, so no configuration.

You did rename the original and new database manually before testing?

[brommetje]

@jvdz Yes I did. Same problem after dump and create new DB @waarens procedure same as from you @jvdz.

What kind of checks are there I only used "pragma integrity_check;"?

How can I find out what is coursing the "On tree page 46898 cell 13: 2nd reference to page 38523" or "Page 35478 is never used"?
Or what is using this pages, I'm not a database guru only a beginner.

[jvdz]

Guess that means the essential records are corrupted. You could check whether you have the autosave switched on and check in the /home/pi/domoticz/backups for a version of the database that isn't corrupted yet.

Jos

[EdwinK]

You could try this approach by dumping the current database and loading the dumpfile into a new database like this bash file:

Code: Select all

#! /bin/sh
sudo service domoticz stop
cd /home/pi/domoticz
rm -f dump.sql
echo Dump domoticz.db to dump.sql
sudo sqlite3 domoticz.db  <<EOF
.mode insert
.output dump.sql
.dump
.exit
EOF

rm -f domoticz.fixed.db
echo create new domoticz.fixed.db loading dump.sql
sudo sqlite3 domoticz.fixed.db  <<EOF
.read dump.sql
.exit
EOF

After this you only need to rename the old domoticz.db file to something else and the new domoticz.fixed.db to domoticz.db.
Worth try?

Jos

Great. I am always afraid of making a typo in one of those commands and then screwing things up. Where is that 'thumbs up' icon

[brommetje]

@jvdz what do you mean with "autosave switched on" is that a database option? Or do mean "automatic backup in Domoticz, yes that is enabled.

[jvdz]

@jvdz what do you mean with "autosave switched on" is that a database option? Or do mean "automatic backup in Domoticz, yes that is enabled.

I meant the Automatic Backup. Did you check the directory I provided? There should be a Monthly/Daily/Hourly directory which contain a backup of the database with that frequency.

Jos