Fixing database of Domoticz

Fixing database of Domoticz

I have turned on automatic backup for Domomticz which keeps a copy of your database every hour and every day and every month. What I failed to check was whether there was enough room on my SD card to cope with (24+31+some months) * size of database. It should but as it turned out it didn’t. Mainly because the database had grown to over 650 MB (my blog on how I got this down to normal size and keep it that way here). Domoticz crashed and left me with a corrupted database. It took me some time to figure out what to do so I wanted to document this for others who encounter the same problem.

First things first. Domoticz had crashed because my disk was full. The problems did not stop there of course because a full volume means a whole lot of things did not work as they should. To quickly get some breathing room, I just deleted some old backups:

sudo rm /home/pi/domoticz/backups/hourly/backup-hour-??-Domoticz.db

Next up is fixing the database. In my case I had to stop Domoticz because even though the database crashed, Domoticz was up and (not) running).

sudo service domoticz.sh stop

Next we need to check the database. If you don’t have SQLite3 on your pi, you need to install it:

sudo apt-get install sqlite3

Go to your subdirectory of Domoticz and start SQLite3 with your database:

sqlite3 domoticz.db

Next you want to check whether it is corrupted using:

PRAGMA integrity_check;

If all is fine, great!, you can leave SQLite3 again using:

.exit

If not, let’s get the database fixed. The way to do this is to export the database, modify the generated SQL file and import it in a new database. Remember, do this with a COPY of your database so exit SQLite3. Let’s go through all the steps.

sudo service domoticz.sh stop
cd /home/pi/domoticz
cp domoticz.db test.db 
sqlite3 test.db


Please note that copying the database could take a long time if you haven’t cleaned it recently. After SQLite3 starts, create an export:

.mode insert
.output test.sql
.dump
.exit

Again dumping the database could take a long time if your database is large. Next you create a new database with the created dump:

sqlite3 new.db < test.sql

Again, this could take a while. When it finished, you can open your new database:

sqlite3 new.db

and check the integrity:

PRAGMA integrity_check;

It should now return without error and return an ok. Now let’s verify that there is at least something:

.tables

You should see a whole list of tables. Let’s verify that there is data in them:

SELECT COUNT(*) FROM LightingLog;

If you see tables and log entries, we can assume the fix worked. Exit SQLite3:

.exit

Now we need to remove the old database and replace it with our new database (and clean up after ourselves):

rm domoticz.db
mv new.db domoticz.db
rm test.db
rm test.sq

You can now restart Domoticz or run the cleanup of the database first and restart it afterwards.

sudo service domoticz.sh start

 

 

 

Advertisements

One thought on “Fixing database of Domoticz

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s