Error: SQL Query database disk image is malformed

[megamarco83]

hi, since a couple of days i receive this type of messages every 5minutes:

Code: Select all

2020-07-18 19:10:00.459 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

maybe because some days ago my temperature sensor dht22 was broken so i can't receive data from it?
now i repaired the sensor and the measuere is ok, how i can solve the database issue? (if it's linked to temperature sensor that was broken)
thanks

[megamarco83]

p.s.
i also notice that in every temperature sensor if look the log hystory i'm not able to see the today log, but i'm able to see the last month log and last year log.
the log values related to last month are evry day correct
for other type of devices like energy device or sitch device i can see every log.
it is the issue reported linked?
thanks

Screenshot_2020-07-18 Domoticz.png (132.54 KiB) Viewed 10853 times

[waaren]

hi, since a couple of days i receive this type of messages every 5minutes:

Code: Select all

2020-07-18 19:10:00.459 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

maybe because some days ago my temperature sensor dht22 was broken so i can't receive data from it?
now i repaired the sensor and the measuere is ok, how i can solve the database issue? (if it's linked to temperature sensor that was broken)
thanks

Seems your database got corrupted.

please try the check and repair action as in this post

[megamarco83]

hey Waaren thanks for help
i performed the commands you suggeste:

Code: Select all

pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.db "PRAGMA integrity_check(10000);"
row 6 missing from index t_id_date_idx
row 3504 missing from index t_id_date_idx
row 4458 missing from index t_id_date_idx
row 4478 missing from index t_id_date_idx
row 4488 missing from index t_id_date_idx
row 4498 missing from index t_id_date_idx
row 4508 missing from index t_id_date_idx
row 4518 missing from index t_id_date_idx
row 4528 missing from index t_id_date_idx
row 4538 missing from index t_id_date_idx
row 4548 missing from index t_id_date_idx
row 5683 missing from index t_id_date_idx
row 5772 missing from index t_id_date_idx
row 5782 missing from index t_id_date_idx
row 5792 missing from index t_id_date_idx
wrong # of entries in index t_id_date_idx

do you know what is it?

i also performed the commands that you suggested to fix, and after that it works very well since 10minutes.
also the logs about dayly temperature sensors are now visible.

last question what is this command that i do like in your guide?

Code: Select all

pi@raspberrypi:~/domoticz $ sudo sed -i '$ s/ROLLBACK;/COMMIT;/g' dump.sql

and do you know any reason why the database get malformed? i was some days out of my home, and i didn't nothing...except the temperature sensor (dht22) that broken a couple of days ago,could be that the reason?
thanks!
marco

[waaren]

do you know what is it?

some records in the database are not accessible via the index. Could be caused by a incomplete delete statement caused by a crash or poweroutage.

Code: Select all

pi@raspberrypi:~/domoticz $ sudo sed -i '$ s/ROLLBACK;/COMMIT;/g' dump.sql

This command change the word ROLLBACK on the last line of the file dump.sql (if it exist) to COMMIT. It ensures the recovery will be performed even if some records are considered bad.

and do you know any reason why the database get malformed? i was some days out of my home, and i didn't nothing...except the temperature sensor (dht22) that broken a couple of days ago,could be that the reason?

sqlite database corruption can be caused by.

hardware (Power, disk, sd card, memory, network)
software (bugs in sqlite / domoticz / utilities)
user (writing directly to the domoticz.db, wrong restore procedure, accessing data of in use database)

[megamarco83]

hi, i find this error early today morning:

Code: Select all

2020-07-19 04:01:02.287 Error: SQL Query("VACUUM") : database disk image is malformed

at 04:01 i have cron that eecute a backup.sh script that perform a backup of my system:

01 04 * * * root /var/www/MyScripts/ftpbackup.sh

here the script:

Code: Select all

#!/bin/bash
# Simple backup script, edit your crontab and add the line
# 0 2 * * * /home/ftpbackup.sh o sistemd   # every day at 02:00 
# 08/01/2020 aggiunto controllo domoticz per verificare la riuscita del backup e mandare un avviso

HOST="XXXXXXXXXXXX.XXXXXXXXXXX.XX"  # web server remoto ftp
USER="XXXXXXXXXX"   # nominativo accesso
FTPPASS="XXXXXXXXX" # passwd

LCD="/var/www/" # percorso server locale dei files da salvare, sostituire il percorso se il server è /var/www/ 
RCD="/mirror/backup/" # creare una cartella da utilizzare su server remoto in questo caso "plugbox" 
DOMOTIC_BK="/var/www/domoticz_backup" #cartella per domoticz backup

domoticzIP=192.168.0.105
domoticzPORT=8085
curl=$(which curl)

domoticzAlertIDX=156       ## define in domoticz as virtual Sensor type alert and enter idx here
green=1
yellow=2
orange=3
red=4

# Let domoticz and you know
signal()
{
	echo $alertMessage
	alertMessage=$(date "+%A%_d %B %Y - %H:%M : ")"%0ADB backup $alertMessage on $HOSTNAME"
	alertMessage="${alertMessage// /+}"
	domoticzURL="http://$domoticzIP:$domoticzPORT/json.htm?type=command&param=udevice&idx=$domoticzAlertIDX&nvalue=$alertColor&svalue=$alertMessage"
	$curl -sS $domoticzURL  >/dev/null
}

LOCKFILE=/var/run/ftpbackup.pid 
if [ -f $LOCKFILE ]; then
    CPID="`cat $LOCKFILE`"  # get Process number into variable CPID
fi

if [ -e ${LOCKFILE} ] && kill -0 `cat ${LOCKFILE}`; then         # if Lockfile exists then kill process
	alertMessage="already running as PID:$CPID" 
    alertColor=$red
	signal 
	exit
else
	alertColor=$yellow
	alertMessage="Starting .. "
    signal 
fi

$curl -sS "http://$domoticzIP:$domoticzPORT/backupdatabase.php" > $DOMOTIC_BK/domoticz.db  # copio database DOMOTICZ in /var/www/domoticz_backup"

#
#Check result
#
if test $? -ne 0 ; then
	alertMessage="backupdatabase.php failed"
	alertColor=$red
	signal 
	exit 1
fi

# make sure the lockfile is removed when we exit and then claim it
trap "rm -f ${LOCKFILE}; exit" INT TERM EXIT
echo $$ > ${LOCKFILE}

# upload files
lftp -e "set ftp:list-options -a;
open ftp://$USER:$FTPPASS@$HOST;
lcd $LCD;
cd $RCD;
mirror -n --only-newer -Re --use-cache;
quit"

#
#Check result
#
if test $? -ne 0 ; then
	alertMessage="lftp failed" 
	alertColor=$red
	signal 
	rm -f ${LOCKFILE}
	exit 1
fi

#
# All OK
#
alertColor=$green
alertMessage="finished succesfully"
signal
rm -f ${LOCKFILE}
#eoj

could be that the reason?
i try to start manually from shell the script:

Code: Select all

pi@raspberrypi:/var/www/MyScripts $ ./ftpbackup.sh
Starting ..
./ftpbackup.sh: line 64: /var/run/ftpbackup.pid: Permission denied
/var/log/lftp/transfer_log: Permission denied.
lcd ok, local cwd=/var/www
cd ok, cwd=/mirror/backup
Total: 91 directories, 3117 files, 0 symlinks
New: 1 file, 0 symlinks
Modified: 5 files, 0 symlinks
6407895 bytes transferred in 1461 seconds (4.3 KiB/s)
finished succesfully

and inside domoticz log i now see:

Code: Select all

 2020-07-19 04:01:02.287 Error: SQL Query("VACUUM") : database disk image is malformed
2020-07-19 09:45:53.296 Error: SQL Query("VACUUM") : database disk image is malformed 

so i bet that this could be the reason....
this script for backup is used and unchenged since many months....the difference could be the upgarding of domoticz from beta version 12073 to actual one 12227 ?

thanks

[waaren]

Code: Select all

2020-07-19 04:01:02.287 Error: SQL Query("VACUUM") : database disk image is malformed

When you call the backupdatabase.php a backup is made and a database VACUUM is performed both using the sqlite subsystem. The VACUUM command does recognize that there is a problem in your database.

This problem can be caused by various reasons as I described in my earlier posts.

You can check if it is caused by the backup by repairing the database now and disable the backup script for one night. Check the database again tomorrow morning and if ok execute the backup manually (with sudo) and check database again directly after the backup.
That might pinpoint the issue to the backup or to an unrelated cause.

[megamarco83]

You can check if it is caused by the backup by repairing the database now and disable the backup script for one night. Check the database again tomorrow morning and if ok execute the backup manually (with sudo) and check database again directly after the backup.
That might pinpoint the issue to the backup or to an unrelated cause.

thanks for the patience
i did what you describe, please check if i did it correct:

Code: Select all

pi@raspberrypi:~/domoticz $ sudo service domoticz stop
pi@raspberrypi:~/domoticz $ sudo cp domoticz.db domoticz.keep
pi@raspberrypi:~/domoticz $ sudo cp domoticz.db domoticz.2repair
pi@raspberrypi:~/domoticz $ sudo service domoticz start
pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.2repair
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .out dump.sql
sqlite> .dump
sqlite> .quit
pi@raspberrypi:~/domoticz $ sudo sed -i '$ s/ROLLBACK;/COMMIT;/g' dump.sql
pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.repaired < dump.sql
Error: near line 1756: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1757: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1758: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1759: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1760: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1761: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1762: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1763: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1764: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1765: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1766: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1767: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1768: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1769: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1770: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1771: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 1772: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8760: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8761: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8762: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8763: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8764: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8765: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8766: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8767: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8768: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8769: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8770: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8771: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8772: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8773: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8774: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8775: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8776: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8777: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8778: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8779: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8780: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8781: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8782: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8783: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8784: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8785: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8786: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8787: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8788: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8789: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8790: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8791: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8792: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8793: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8794: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8795: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8796: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8797: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8798: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8799: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8800: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8801: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8802: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8803: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8804: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8805: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8806: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8807: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8808: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8809: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8810: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8811: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8812: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8813: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8814: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8815: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8816: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8817: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8818: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8819: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8820: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8821: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8822: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8823: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8824: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8825: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8826: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8827: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8828: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8829: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8830: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8831: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8832: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8833: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8834: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8835: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8836: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8837: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8838: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8839: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8840: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8841: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8842: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8843: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8844: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8845: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8846: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8847: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8848: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8849: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8850: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8851: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8852: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8853: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8854: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8855: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8856: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8857: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8858: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8859: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8860: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8861: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8862: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8863: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8864: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8865: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8866: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8867: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8868: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8869: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8870: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8871: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8872: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8873: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8874: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8875: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8876: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8877: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8878: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8879: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8880: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8881: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8882: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8883: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8884: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8885: NOT NULL constraint failed: LightingLog.DeviceRowID
Error: near line 8886: NOT NULL constraint failed: LightingLog.DeviceRowID
pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.repaired
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> pragma integrity_check;
ok
sqlite> .quit
pi@raspberrypi:~/domoticz $ sudo service domoticz stop
pi@raspberrypi:~/domoticz $ sudo cp domoticz.db domoticz.just2beSure
pi@raspberrypi:~/domoticz $ sudo cp domoticz.repaired domoticz.db
pi@raspberrypi:~/domoticz $ sudo service domoticz start

summarizing, i make a backup of actual db, then i correct it using your commands, then i restart domoticz and finally check the repaired db.
it seams working...

Code: Select all

pi@raspberrypi:~/domoticz $ sudo service domoticz stop
pi@raspberrypi:~/domoticz $ sudo cp domoticz.db domoticz.keep
pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.db "VACUUM;"
pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.db "PRAGMA integrity_check(10000);"
ok
pi@raspberrypi:~/domoticz $ sudo sqlite3 domoticz.db "PRAGMA foreign_key_check;"
pi@raspberrypi:~/domoticz $ sudo service domoticz start

now i deactivate from cron the ftpbackup for tonight, tomorrow i will do again the check, ok?

thanks!!!

[megamarco83]

Check the database again tomorrow morning and if ok execute the backup manually (with sudo) and check database again directly after the backup.
That might pinpoint the issue to the backup or to an unrelated cause.

hi, as i wrote in the last post i deactivate my ftpbackup for yesterday night, and today i check the database, and it's ok.

Code: Select all

pi@raspberrypi:~ $ sudo service domoticz stop
pi@raspberrypi:~ $ sudo cp domoticz.db domoticz.keep
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "VACUUM;"
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "PRAGMA integrity_check(10000);"
ok
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "PRAGMA foreign_key_check;"
pi@raspberrypi:~ $ sudo service domoticz start

your last suggestion was, if database it's ok, and it is, to run the backup manully with sudo and then check again
just to be sure to not make wrong thing, can you please wrote here the command to perfrom manual backup?
thanks

[waaren]

your last suggestion was, if database it's ok, and it is, to run the backup manully with sudo and then check again
just to be sure to not make wrong thing, can you please wrote here the command to perfrom manual backup?

First try with

Code: Select all

sudo curl -sS "http://192.168.0.105:8085/backupdatabase.php" > /var/www/domoticz_backup/domoticz.db

If that does not corrupt your database then do a

Code: Select all

sudo /var/www/MyScripts/ftpbackup.sh

[megamarco83]

hi, thanks for support

i tested both commands, and tested the database twice....everytime no errors:

Code: Select all

pi@raspberrypi:~ $ sudo curl -sS "http://192.168.0.105:8085/backupdatabase.php" > /var/www/domoticz_backup/domoticz.db
pi@raspberrypi:~ $ sudo service domoticz stop
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "VACUUM;"
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "PRAGMA integrity_check(10000);"
ok
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "PRAGMA foreign_key_check;"
pi@raspberrypi:~ $ sudo service domoticz start
pi@raspberrypi:~ $ sudo /var/www/MyScripts/ftpbackup.sh
Starting ..
lcd ok, local cwd=/var/www
cd ok, cwd=/mirror/backup
Total: 91 directories, 3119 files, 0 symlinks
New: 2 files, 0 symlinks
Modified: 18 files, 0 symlinks
4626677 bytes transferred in 1200 seconds (3.8 KiB/s)
finished succesfully
pi@raspberrypi:~ $ sudo service domoticz stop
pi@raspberrypi:~ $ sudo cp domoticz.db domoticz.keep
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "VACUUM;"
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "PRAGMA integrity_check(10000);"
ok
pi@raspberrypi:~ $ sudo sqlite3 domoticz.db "PRAGMA foreign_key_check;"
pi@raspberrypi:~ $ sudo service domoticz start

so very good nows, but...not understand what happened....
thanks again!

p.s. now i reactivate my cront job for backup, it's correct?

Code: Select all

01 04   * * *   root   /var/www/MyScripts/ftpbackup.sh 

[waaren]

p.s. now i reactivate my cront job for backup, it's correct?

Code: Select all

01 04   * * *   root   /var/www/MyScripts/ftpbackup.sh 

I would do it a more quiet time. The whole hours are quite busy for domoticz and if an automatic (internal) backup runs it might interfere at 04:01. What about

Code: Select all

13 04   * * *   root   /var/www/MyScripts/ftpbackup.sh 

?

[megamarco83]

sounds good...
i used at 04:01 AM because many devices at that time sleep
i used 01 minutes because every 5minutes (00 05 10 15 20 etc) metern write on a csv file the data, so i choose 4:01
i see that ftp backup required alomst 3minutes and the only big file is database of domoticz that is 5,2Mb the csv files are 1kb max.
i used this backup since many time....but ok, maybe i can postpone the backup at 04:11 AM? should be better?
thanks!!