Domoticz database check script
Posted: Friday 25 January 2019 2:40
* Edit: 20190228 ; Include official sqlite3 error messages on failure
* Edit: 20190808 ; Include optional trigger of folllow up OS command
* Edit: 20191005 ; add Telegram as notification option
* Edit: 20191005 ; Include optional set of an Alert device with result of check
* Edit: 20191019 ; Include RC message in alert device
* Edit: 20200108 ; fixed bug(ss) in aftersucces / afterSuccess (case matters and double s also!) -- Thx. to MikeF for reporting
Triggered by a forum member experiencing unexpected behavior of domoticz, that after some root cause analysis turned out to be caused by a malformed (= corrupt) database, I created this dzVents script to check the state of the database.
It will not prevent a corruption but will signal you via Email and/or notification as soon as it find one. For now there are only four checks which should catch the 'database disk image is malformed Error.
If you have an idea for more and better domoticz database checks please let me know. If they can be implemented without consuming too much CPU cycles, I will try to add them.
When not yet familiar with dzVents please start with reading Get started before implementing (~ 5 minutes). Special attention please for "In Domoticz go to Setup > Settings > Other and in the section EventSystem make sure the checkbox 'dzVents disabled' is not checked. Also make sure that in the Security section in the settings you allow 127.0.0.1 to not need a password. dzVents uses that port to send certain commands to Domoticz. Finally make sure you have set your current location in Setup > Settings > System > Location, otherwise there is no way to determine nighttime/daytime state."
Have Fun !
* Edit: 20190808 ; Include optional trigger of folllow up OS command
* Edit: 20191005 ; add Telegram as notification option
* Edit: 20191005 ; Include optional set of an Alert device with result of check
* Edit: 20191019 ; Include RC message in alert device
* Edit: 20200108 ; fixed bug(ss) in aftersucces / afterSuccess (case matters and double s also!) -- Thx. to MikeF for reporting
Triggered by a forum member experiencing unexpected behavior of domoticz, that after some root cause analysis turned out to be caused by a malformed (= corrupt) database, I created this dzVents script to check the state of the database.
It will not prevent a corruption but will signal you via Email and/or notification as soon as it find one. For now there are only four checks which should catch the 'database disk image is malformed Error.
If you have an idea for more and better domoticz database checks please let me know. If they can be implemented without consuming too much CPU cycles, I will try to add them.
When not yet familiar with dzVents please start with reading Get started before implementing (~ 5 minutes). Special attention please for "In Domoticz go to Setup > Settings > Other and in the section EventSystem make sure the checkbox 'dzVents disabled' is not checked. Also make sure that in the Security section in the settings you allow 127.0.0.1 to not need a password. dzVents uses that port to send certain commands to Domoticz. Finally make sure you have set your current location in Setup > Settings > System > Location, otherwise there is no way to determine nighttime/daytime state."
Have Fun !
Code: Select all
--[[ -- DB check
Will give you a warning when something is wrong with the (domoticz) sqlite database
requires sqlite3
install command on linux: sudo apt install sqlite3
install command on openwrt: opkg install sqlite3-cli
install command on synology: sudo /opt/bin/opkg install sqlite3-cli
History:
20190125: first public release
20190228: Include official sqlite3 error messages on failure
20190808: Include optional trigger of follow up OS command
20191005: add Telegram as notification option
20191005: Include optional set of an Alert device with result of check
20191019: Include RC message in alert device
20200108; Fixed bug(ss) in aftersucces / afterSuccess (case matters and double s also ! )
]]--
return {
on = {
timer = {
"at 20:07", -- change to a time that suits you
"at 07:07",
},
},
logging = {
level = domoticz.LOG_DEBUG, -- change to LOG_ERROR after you tested the script
marker = "DB check"
},
execute = function(dz)
-- ======================= Settings below this line =================
local email = true -- set to false if you don't want an Email when something wrong with database
local notify = true -- set to false if you don't want a notification when something wrong with database
local afterSuccessCommand = '/bashdir/copyDB.sh' -- set to full qualified path of your followUp script or false if you don't need a followUp after success
local afterFailCommand = false -- set to full qualified path of your followUp script or false if you don't need a followUp after fail
local followUpAfterFail = false -- set to false if you don't need a followUpAfterFail
local alert = {
active = true, -- set to false if you don't want an Alert device set.
device = "dbCheck result", -- Name of your alert device (only needed when you set active = true )
failText = "Problem in database",
OKText = "database OK",
}
local subject = "Domoticz database check" -- Free text
local emailaddress = "[email protected]" -- Your Email address
local path = "/opt/domoticz/" -- full qualified path to your database
local database = "domoticz.db" -- database filename + extension
-- local database = "corrupt.db" -- test database filename + extension
-- you can corrupt a test database by just load it in an editor
-- and remove a couple of bytes
local sqlite = "/usr/bin/sqlite3" -- location of your sqlite3 tool (use the command 'which sqlite3' to find location)
local myNotificationTable = {
-- table with one or more notification systems.
-- uncomment the notification systems that you want to be used
-- Can be one or more of
dz.NSS_GOOGLE_CLOUD_MESSAGING,
-- dz.NSS_HTTP,
-- dz.NSS_KODI,
-- dz.NSS_LOGITECH_MEDIASERVER,
-- dz.NSS_NMA,
-- dz.NSS_PROWL,
-- dz.NSS_PUSHALOT,
-- dz.NSS_PUSHBULLET,
dz.NSS_PUSHOVER,
-- dz.NSS_PUSHSAFER,
dz.NSS_TELEGRAM,
}
-- ======================= No modification needed below this line ==================
local function logWrite(str,level) -- Support function for shorthand debug log statements
dz.log(tostring(str),level or dz.LOG_DEBUG)
end
local space = " "
local baseCommand = "sudo" .. space .. sqlite .. space .. path .. database .. space
local checks = {}
checks = {
"\'select count(id) from deviceStatus;\'",
"\'.schema\'",
"\'pragma integrity_check;\'",
"\'pragma foreign_key_check;\'",
}
local function rc2Text(rc)
local errorMessages = {
[0] = "database OK",
[1] = "Generic error",
[2] = "Internal logic error in SQLite",
[3] = "Access permission denied",
[4] = "Callback routine requested an abort",
[5] = "The database file is locked",
[6] = "A table in the database is locked",
[7] = "memory allocation failed",
[8] = "Attempt to write a readonly database",
[9] = "Operation terminated by sqlite3_interrupt",
[10] = "Some kind of disk I/O error occurred",
[11] = "The database disk image is malformed",
[12] = "Unknown opcode in sqlite3_file_control",
[13] = "Insertion failed because database is full",
[14] = "Unable to open the database file",
[15] = "Database lock protocol error",
[16] = "Internal use only",
[17] = "The database schema changed",
[18] = "String or BLOB exceeds size limit",
[19] = "Abort due to constraint violation",
[20] = "Data type mismatch",
[21] = "Library used incorrectly",
[22] = "Uses OS features not supported on host",
[23] = "Authorization denied",
[24] = "Not used",
[25] = "2nd parameter to sqlite3_bind out of range",
[26] = "File opened that is not a database file",
}
return(errorMessages[rc] or "Unknown error")
end
local function followUp(cmd)
os.execute('sudo ' .. cmd ..' &')
end
local function osExecute(base,check)
local fileHandle = assert(io.popen(base .. check, 'r'))
local commandOutput = assert(fileHandle:read('*a'))
local returnTable = {fileHandle:close()}
check = check:gsub("'","") .." result ==>> " .. ( returnTable[3] ~= 0 and "Failed: " .. rc2Text(returnTable[3]) .. commandOutput .. " (".. returnTable[3] .. ")" or true and "OK" )
logWrite("Command " .. check )
return check,returnTable[3] -- rc[3] contains returnCode
end
local function checkDatabase()
if dz.utils.fileExists(path .. database) then
if dz.utils.fileExists(sqlite) then
for _,check in ipairs (checks) do
local result,rc = osExecute(baseCommand,check)
if rc ~= 0 then
return rc, result
end
end
else
return -1,"sqlite3 not installed"
end
else
return -1,"wrong path to database"
end
return 0
end
local function updateAlert(rc)
if alert and alert.active then
local now = dz.time.rawDate .. ', ' .. dz.time.rawTime .. ': '
local alertLevel = dz.ALERTLEVEL_RED
if rc == 0 then alertLevel = dz.ALERTLEVEL_GREEN end
dz.devices(alert.device).updateAlertSensor(alertLevel, now .. rc2Text(rc))
end
end
-- main program
local rc, result = checkDatabase()
if rc ~= 0 then
logWrite(result,dz.LOG_ERROR)
if email then dz.email(subject,result,emailaddress) end
if notify then
dz.notify(subject, result or "Dbase check encountered unknown error", dz.PRIORITY_NORMAL, dz.SOUND_INTERMISSION,"", myNotificationTable )
end
if afterFailCommand and ( rc > 0 ) then followUp(afterFailCommand) end
else
if afterSuccessCommand then followUp(afterSuccessCommand) end
end
updateAlert(rc)
end
}
