Database Self Healing

[ben53252642]

Each time I have seen a problem with Domoticz database, it has been possible to tell from looking at the event log.

Suggest adding a “self healing” option in which Domoticz stores a backup of the DB every say 12 hours and if there is a problem identified with the database, automatically reverts to a previous version (and keep cycling through old copies of the DB until a working version without errors is found). Auto copy the corrupt db to a special folder called “corrupt dbs” or similar incase the user wants it in the future for some reason.

Store the backup db’s with a time / date in the file name, when a working db is found before copying it into place write the date time to a .txt with a flag saying it has been restored to x time which Domoticz checks for on startup so the user can be sent an automatic notification advising that self healing has had to revert to a previous version of the database.

I could (and will if necessary) write this functionality in a bash script that reads the Domoticz log, however I think that this would potentially be a valued feature integrated into the core of Domoticz.

[waaren]

Suggest adding a “self healing” option in which Domoticz stores a backup of the DB every say 12 hours and if there is a problem identified with the database, automatically reverts to a previous version (and keep cycling through old copies of the DB until a working version without errors is found). Auto copy the corrupt db to a special folder called “corrupt dbs” or similar incase the user wants it in the future for some reason.

Store the backup db’s with a time / date in the file name, when a working db is found before copying it into place write the date time to a .txt with a flag saying it has been restored to x time which Domoticz checks for on startup so the user can be sent an automatic notification advising that self healing has had to revert to a previous version of the database.
...

@ben53252642,

Like your idea if "fatal" DB errors (causing domoticz to stop completely) can be identified. Would like to see some examples of log messages that could be used for that. For none fatal DB errors my preference would be to get a notification. That would give me a kind of early warning and hopefully enough time to solve things before I am faced with data loss.

domoticz already have the option to backup the db hourly. SETUP -->> SETTINGS --> SYSTEM ---> AUTOMATIC BACKUP:
the time/date can easily be obtained on the OS level (on linux with stat command and some others)
I use monit for monitoring and in some cases automatic recovery for quite some activities and processes on my systems. Maybe that would be a good starting point to keep an eye on the domoticz log for DB related errors ?

[ben53252642]

For none fatal DB errors my preference would be to get a notification. That would give me a kind of early warning and hopefully enough time to solve things before I am faced with data loss.

Suggest a tick box for "auto recover to a previous db" if you want an option for notifications only,

I'll get to work on a few bash scripts (which I think is the best way to initially collect all the db failure messages), starting with one that actually corrupts the db on purpose for testing. I've got a couple of msgs I know of eg:

viewtopic.php?t=10984

Once I've built a simple version in bash and mapped fatal vs non fatal corruption I'll post a list of definitions.

I've had to recover Domoticz db about 5x over the last few yrs, not many but still I think Domoticz should have a self healing capability.