Page 2 of 3
Re: FIX for The database disk image is malformed
Posted: Saturday 05 January 2019 16:43
by 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?
Re: FIX for The database disk image is malformed
Posted: Saturday 12 January 2019 15:05
by 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.
Re: FIX for The database disk image is malformed
Posted: Saturday 12 January 2019 17:33
by 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?
Re: FIX for The database disk image is malformed
Posted: Saturday 12 January 2019 18:59
by waaren
brommetje wrote: ↑Saturday 12 January 2019 17:33
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
Re: FIX for The database disk image is malformed
Posted: Friday 18 January 2019 17:23
by 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?
Re: FIX for The database disk image is malformed
Posted: Wednesday 10 April 2019 20:12
by 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...
Re: FIX for The database disk image is malformed
Posted: Wednesday 08 May 2019 21:36
by 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
Re: FIX for The database disk image is malformed
Posted: Thursday 12 September 2019 20:45
by 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.
Re: FIX for The database disk image is malformed
Posted: Thursday 20 February 2020 17:13
by 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
Re: FIX for The database disk image is malformed
Posted: Thursday 20 February 2020 21:46
by EdwinK
Flopp wrote: ↑Thursday 20 February 2020 17:13
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
Re: FIX for The database disk image is malformed
Posted: Monday 24 February 2020 20:32
by PeJeWe
Flopp wrote: ↑Thursday 20 February 2020 17:13
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
Re: FIX for The database disk image is malformed
Posted: Tuesday 25 February 2020 15:08
by waaren
PeJeWe wrote: ↑Monday 24 February 2020 20:32
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
Re: FIX for The database disk image is malformed
Posted: Tuesday 25 February 2020 19:44
by PeJeWe
waaren wrote: ↑Tuesday 25 February 2020 15:08
PeJeWe wrote: ↑Monday 24 February 2020 20:32
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
Re: FIX for The database disk image is malformed
Posted: Tuesday 25 February 2020 20:12
by waaren
PeJeWe wrote: ↑Tuesday 25 February 2020 19:44
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
Re: FIX for The database disk image is malformed
Posted: Tuesday 25 February 2020 20:17
by PeJeWe
waaren wrote: ↑Tuesday 25 February 2020 20:12
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
Re: FIX for The database disk image is malformed
Posted: Tuesday 25 February 2020 20:24
by waaren
PeJeWe wrote: ↑Tuesday 25 February 2020 20:17
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.
Re: FIX for The database disk image is malformed
Posted: Thursday 27 February 2020 20:50
by 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
Re: FIX for The database disk image is malformed
Posted: Tuesday 21 April 2020 19:41
by 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.
Re: FIX for The database disk image is malformed
Posted: Tuesday 21 April 2020 20:09
by waaren
jake wrote: ↑Tuesday 21 April 2020 19:41
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 ?
Re: FIX for The database disk image is malformed
Posted: Tuesday 21 April 2020 21:13
by jake
waaren wrote: ↑Tuesday 21 April 2020 20:09
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