FIX for The database disk image is malformed Topic is solved

[brommetje]

I check it it now and the oldest backup what is working "pragma integrity_check'" is from December 15 2018 .

Code: Select all

root@snoepie:/SYNDS/backup# du -k backup-day-15-Domoticz.db  	= 38328 KB
root@snoepie:/SYNDS/backup# du -k /home/pi/domoticz/domoticz.db	= 51612 KB

So a lot of changes because of the holidays and free time to play, some changes in ZWave, with Domoticz.
Is there a way to merge some how?
Because if I have to go back then the difficult question what is changes?
The most important thing is to backup the device configuration I think is there a way to backup/restore that, dump some tables?

[brommetje]

This afternoon after a week I followed the procedure of @jvdz and @waaren again and it is working now strangely enough but the result is OK.
So thanks @waaren and @jvdz for your help.

Script/procedure @jvdz works fine.

[brommetje]

Sorry I have to correct my self.

Script of @jvdz is working pragma integrity_check = OK

But when I start and stop Domoticz and then check the database again Error: database disk image is malformed
So I copied the repaired database to another PI start and stop domoticz same problem Error: database disk image is malformed

What is corrupting my database? any idea's?

[waaren]

Sorry I have to correct my self.

Script of @jvdz is working pragma integrity_check = OK

But when I start and stop Domoticz and then check the database again Error: database disk image is malformed
So I copied the repaired database to another PI start and stop domoticz same problem Error: database disk image is malformed

What is corrupting my database? any idea's?

My approach would be

• Update to latest Beta and check if database image gets malformed. If not then it was caused by a bad binary
• replace SD card with a good fresh one and check if database image gets malformed. If not then it was caused by a bad card
• stop all plugins and scripts and test again if database image gets malformed. If not then it was cause by one of these
• if yes then stop all hardware in domoticz and test again if database image gets malformed. If not then it was in one of these

[brommetje]

Add the moment still running on my old SD card (ordered new one) but the database is OK now, just repairing the db and put it back wasn't the solution for me because most of the time the NO devices or NO configuration and a corrupt db after start and stopping domoticz.

I solved it this way I stopped domoticz, repaired the db script @jvdz/waaren and then start domoticz without a database.
Then domoticz create a new database and throught the web gui I restored my repaired database, that works perfectly this way (for me).
I do not know why just copying the repaired database back doesn't work.

Code: Select all

-rw-r-----  1 root root  52236288 Jan 18 17:10 domoticz.db

I only have one problem the "automatic backup" is not running any more.
If I use the backupdatabase.php script it is running fine so I scheduled (cron) this as a work-around.

Code: Select all

/usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > /SYNDS/backup/domoticz.db

My question is now where can I find this backupdatabase.php script because if I search for it (find) then it is not there on file level?
Is it somewhere in the database, how is it working through domoticz? I can see there is a table with the schedule in the database?
Is it hard coded somewhere?
Disable and enable "automatic backup" doesn't help.

Any idea?

[EdwinK]

Code: Select all

sudo apt-get install sqlite3 
Reading package lists... Error!
E: flAbsPath on /var/lib/dpkg/status failed - realpath (2: No such file or directory)
E: Could not open file  - open (2: No such file or directory)
E: Problem opening 
E: The package lists or status file could not be parsed or opened.
pi@domo-server:~ $ 

Clueless at this. I see there is no directory, but for the rest...

[PeJeWe]

running latest beta: Version: 4.10706 on a RPi3+

my system is running just fine, i only have this error when i run the backup procedure

this is what i get:

Status: Starting automatic database backup procedure...
Error: SQL Query("VACUUM") : database disk image is malformed
Status: Ending automatic database backup procedure...

pragma integrity_check = database disk image is malformed

database rebuild with: DB Browser (SQLite)
pragma integrity_check = OK

after that

When i start making a backup i only download a empty backupdatabase.php file and not the domoticz.db

[PeJeWe]

Has someone already found a working solution?
I stil have this error:
2019-09-12 01:00:00.644 Error: SQL Query("VACUUM") : database disk image is malformed
2019-09-12 02:00:00.763 Error: SQL Query("VACUUM") : database disk image is malformed
2019-09-12 03:00:01.987 Error: SQL Query("VACUUM") : database disk image is malformed
2019-09-12 04:00:00.758 Error: SQL Query("VACUUM") : database disk image is malformed
2019-09-12 05:00:11.000 Error: SQL Query("VACUUM") : database disk image is malformed
etc.
my domoticz is working fine, i only have this errors.

[Flopp]

When I moved my domoticz.db to another Debian I got "database disk image is malformed"
Tried many of the other solutions but it didn't help.

Finally I found a solution for me.

using sqlite3 to create a dump file, then open it and changed the bottom line from ROLLBACK to COMMIT, then type

Code: Select all

cat dump.sql | sqlite3 domoticz.db

[EdwinK]

using sqlite3 to create a dump file, then open it and changed the bottom line from ROLLBACK to COMMIT, then type

Code: Select all

cat dump.sql | sqlite3 domoticz.db

Thanks. This one saved me big time. No more malformed database errors at the moment. I think this (and @jvz's script) should be in the WIKI

[PeJeWe]

When I moved my domoticz.db to another Debian I got "database disk image is malformed"
Tried many of the other solutions but it didn't help.

Finally I found a solution for me.

using sqlite3 to create a dump file, then open it and changed the bottom line from ROLLBACK to COMMIT, then type

Code: Select all

cat dump.sql | sqlite3 domoticz.db

it isnt working for me

a manual/auto backup results in a download of the/a file called "backupdatabase.php" it is a empty file

and my auto backup script gives an error at the line : /usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > /tmp/backup/$BACKUPFILE

[waaren]

it isnt working for me
a manual/auto backup results in a download of the/a file called "backupdatabase.php" it is a empty file
and my auto backup script gives an error at the line : /usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > /tmp/backup/$BACKUPFILE

Some questions to identify possible reason why you have this issue..

What is error message when executing the script
is domoticz on the same system as the script and is it using port 8080 and is it active when the script is executed
Can you share the complete backup script
Is this script executed by root or by another user
Does the directory /tmp/backup exists
What do see when you try to do
sudo cp <domoticzdir>/domoticz.db /tmp/backup/domoticz.backup

[PeJeWe]

it isnt working for me
a manual/auto backup results in a download of the/a file called "backupdatabase.php" it is a empty file
and my auto backup script gives an error at the line : /usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > /tmp/backup/$BACKUPFILE

Some questions to identify possible reason why you have this issue..

What is error message when executing the script
is domoticz on the same system as the script and is it using port 8080 and is it active when the script is executed
Can you share the complete backup script
Is this script executed by root or by another user
Does the directory /tmp/backup exists
What do see when you try to do
sudo cp <domoticzdir>/domoticz.db /tmp/backup/domoticz.backup

Some questions to identify possible reason why you have this issue..

Q. What is error message when executing the script A. no error, only a empty file domoticz.db (0 bytes)
when i use the malformed database, the backup procedure (manual backup and autoscript) runs well, it results in a good domoticz.db file.

Q. is domoticz on the same system as the script and is it using port 8080 and is it active when the script is executed A. yes
Q. Can you share the complete backup script A. yes i can pm it
Q. Is this script executed by root or by another user A. it is executed as root
Q. Does the directory /tmp/backup exists A. yes
What do see when you try to do
sudo cp <domoticzdir>/domoticz.db /tmp/backup/domoticz.backup >

Code: Select all

-bash: domoticzdir: Bestand of map bestaat niet

[waaren]

What do see when you try to do
sudo cp <domoticzdir>/domoticz.db /tmp/backup/domoticz.backup >

Code: Select all

-bash: domoticzdir: Bestand of map bestaat niet

Sorry for not being clear enough but when I write <domoticzdir> I mean replace <domoticzdir> with the actual path of your domoticz installation.
My domoticz is in /opt/domoticz and therefore I use

Code: Select all

sudo cp /opt/domoticz/domoticz.db   /tmp/backup/domoticz.backup

but if your domoticz is in /home/pi/domoticz you should use

Code: Select all

sudo cp /home/pi/domoticz/domoticz.db   /tmp/backup/domoticz.backup

[PeJeWe]

but if your domoticz is in /home/pi/domoticz you should use

Code: Select all

sudo cp /home/pi/domoticz/domoticz.db   /tmp/backup/domoticz.backup

i had done that and i dont get any errors the domoticz.db file gets saved in the /tmp/backup directory as domoticz.backup

[waaren]

I had done that and i dont get any errors the domoticz.db file gets saved in the /tmp/backup directory as domoticz.backup

OK the created /tmp/backup/domoticz.backup has the same content as the result of a working

Code: Select all

/usr/bin/curl -s http://127.0.0.1:8080/backupdatabase.php > /tmp/backup/$BACKUPFILE

command and so you have a working backup if you use that command in your script but let us continue via PM to get to the bottom why the curl command does not give the expected result.

[PeJeWe]

All credits go to waaren , together we have repaired my database with the following method:

open shell window putty or other shell capable program

cd <domoticzdir> ( example: /home/pi/domoticz/)
sudo service domoticz stop
sudo cp domoticz.db domoticz.keep
sudo sqlite3 domoticz.db
.mode insert
.output dump.sql
.dump
.exit

edit dump.sql and change the last line from:

ROLLBACK;

in to

COMMIT;


sudo rm -f domoticz.fixedDB
sudo sqlite3 domoticz.fixedDB
.read dump.sql
.exit

sudo cp domoticz.fixedDB domoticz.db
sudo service domoticz start

[jake]

@waaren, I followed the procedure above, except that I forgot to use sudo to manipulate the database. In hindsight weird that this even worked. Anyway, after restart of Domoticz, my log file was only red text and no devices anymore. I fixed that by deleting the 2 domticz.db temp files and restarting Domoticz.

A few hours later I discovered that the hourly (and later daily) backups are not done, this is mentioned in the log file. I than checked the backup folder and discovered that all log files are owned by 'root'. I therefore repeated the whole procedure again and with sudo. Up front I deleted the 2 temp files again. Restart of domoticz gave 1 error line about an incrorrect device state, but other than that everything runs fine. I checked the database again with 'pragma' and no errors shown.

Unfortunately the backups are still not made. The domoticz.db has the same file permissions as all the backup files: -rw-r--r-- Only the original domoticz.keep has these rights -rwxr--r--

A restart of domoticz doesn't help, I have not yet restarted the rpi completely.

ONE difference I found from the above procedure and my setup: While opening the output.sql , the last line was already 'COMMIT', so I didn't have to change that.

[waaren]

A few hours later I discovered that the hourly (and later daily) backups are not done, this is mentioned in the log file. I than checked the backup folder and discovered that all log files are owned by 'root'. I therefore repeated the whole procedure again and with sudo.

The domoticz.keep is a copy of the original domoticz.db it should have -rw-r--r-- access rights

Ii is not completely clear from your post which database you used for the last recovery attempt. If it was done with a database that was the result of a previous recovery attempt, the best thing you can do now is to redo the procedure but starting with your last working backup.

Make sure the domoticz.db-wal and domoticz.db-shm files are no longer in the domoticz dir after you stopped domoticz and before you start the sqlite dump process. If they are stil there it could result in an automatic recovery with the write ahead log file (wal) and it's index file (shm) against a database they do not belong to. Bad things might happen..
I have no clue why the automatic backup fails if owner and access right of all files / directories are the same as before.
Does the manual backup from the GUI work ?

[jake]

Does the manual backup from the GUI work ?

That's a good one to try. Yes, this backup runs fine. I copied it back to the RPI and checked it with sqlite3 pragma integrity_check and it says 'OK'.
Both the actual domoticz.db as all backup files have the correct access rights: -rw-r--r--
Also, the backup folder has the same permissions and owner as the other folders like dzVents, scripts and www.

I only found in the last 24 hr 1 backup file with pi:root ownership. This was about 12 hours before I manually touched this whole database thing, so how this could have happened, I don't know. I have deleted that one now to be sure this is not causing issues.

Code: Select all

-rw-r----- 1 root root 21240832 Apr 18 21:00 backup-hour-21-Domoticz.db
-rw-r----- 1 pi   root 21259264 Apr 18 22:00 backup-hour-22-Domoticz.db
-rw-r----- 1 root root 21269504 Apr 18 23:00 backup-hour-23-Domoticz.db

I will wait until the next hour to see what happens. Would it be worthwhile to mv the whole backup folder to another folder name? Maybe this gives domoticz the possibiliy to build the data set again.

This is the hourly log records:

Code: Select all

2020-04-21 21:00:01.384 Status: Starting automatic database backup procedure...
2020-04-21 21:00:03.541 Error: Error writing automatic hourly backup file
2020-04-21 21:00:05.790 Status: Ending automatic database backup procedure...
2020-04-21 21:00:05.790 Error: Error writing automatic daily backup file