SQL Logger Errors

[aldera]

Hello,

I started getting these SQL Logger errors about 8:30 last night. Tried restarting the plugin, restarting Indigo Server and then restarted my computer (Macmini M1) running Sonoma 14.6.1 and Indigo Server 2023.2. Corrupt database? If so, what should I do? Any other ideas? Thanks.

Ralph

Code: Select all

  SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: INSERT INTO "device_history_1831569165" ("alive") VALUES (?);
('Sun Sep 22 15:35:39 2024',)
   SQL Logger Debug                Failed to update table device_history_1831569165 with device changes: database disk image is malformed
Traceback (most recent call last):
  File "plugin.py", line 518, in _update_device_history
    self.indigo_db.insert_row(dev_table_name, columns)
  File "/Library/Application Support/Perceptive Automation/Indigo 2023.2/Plugins/SQL Logger.indigoPlugin/Contents/Server Plugin/indigologger/indigodb.py", line 201, in insert_row
    raise self.translate_native_exception(err)
  File "/Library/Application Support/Perceptive Automation/Indigo 2023.2/Plugins/SQL Logger.indigoPlugin/Contents/Server Plugin/indigologger/indigodb.py", line 198, in insert_row
    self.execute_non_query(query, *params)
  File "/Library/Application Support/Perceptive Automation/Indigo 2023.2/Plugins/SQL Logger.indigoPlugin/Contents/Server Plugin/indigologger/sqlfactory_sqlite.py", line 134, in execute_non_query
    self.connection.execute(sql_statement, parameters)
sqlite3.DatabaseError: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1775858229" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_1775858229 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_219599929" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_219599929 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1363362267" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_1363362267 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1916939702" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_1916939702 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_722132467" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_722132467 for device history: not an error
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1097002057" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_1097002057 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_262490662" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "lastdn" TEXT, "lastup" TEXT, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_262490662 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_78210374" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_78210374 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_706601522" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_706601522 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_16799354" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_16799354 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_810303098" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_810303098 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1479739695" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_1479739695 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_96753591" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_96753591 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1831629470" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "sensorvalue" REAL, "sensorvalue_ui" TEXT);
()
   SQL Logger Debug                Failed to create table device_history_1831629470 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_721172296" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "onoffstate" BOOL);
()
   SQL Logger Debug                Failed to create table device_history_721172296 for device history: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: INSERT INTO "device_history_1439050415" ("lastmessage") VALUES (?);
('{"serial_number":"HB-00022224","type":"hub_status","firmware_revision":"194","uptime":1633287,"rssi":-45,"timestamp":1727033740,"reset_flags":"BOR,PIN,POR","seq":163069,"radio_stats":[22,1,0,3,24525],"mqtt_stats":[10,0]}',)
   SQL Logger Debug                Failed to update table device_history_1439050415 with device changes: database disk image is malformed
Traceback (most recent call last):
  File "plugin.py", line 518, in _update_device_history
    self.indigo_db.insert_row(dev_table_name, columns)
  File "/Library/Application Support/Perceptive Automation/Indigo 2023.2/Plugins/SQL Logger.indigoPlugin/Contents/Server Plugin/indigologger/indigodb.py", line 201, in insert_row
    raise self.translate_native_exception(err)
  File "/Library/Application Support/Perceptive Automation/Indigo 2023.2/Plugins/SQL Logger.indigoPlugin/Contents/Server Plugin/indigologger/indigodb.py", line 198, in insert_row
    self.execute_non_query(query, *params)
  File "/Library/Application Support/Perceptive Automation/Indigo 2023.2/Plugins/SQL Logger.indigoPlugin/Contents/Server Plugin/indigologger/sqlfactory_sqlite.py", line 134, in execute_non_query
    self.connection.execute(sql_statement, parameters)
sqlite3.DatabaseError: database disk image is malformed
   SQL Logger Error                One or more failures updating device history; see the debug log for details
   SQL Logger Debug                SQL Execution: CREATE TABLE IF NOT EXISTS "device_history_1713611753" (id integer PRIMARY KEY AUTOINCREMENT, ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP, "ipaddress" TEXT, "ipaddressui" TEXT, "lastchange" TEXT, "onoffstate" BOOL);
()

[kw123]

Likely your SQLite db is corrupt.
Very little chance to fix that.
You could try to restore an old version from backup.
Switch off logger
Restore the db
Start logger
In indigoutilities there is a tool to create a backup and also a script to restore the db.

But I do not recommend to use SQLite.
Please start using psql.
Use the psql app. It is very easy to install

SQLite db is a flat file. Any write error will corrupt the whole db.
In psql each state in indigo has its own file in the db.


Sent from my iPhone using Tapatalk

[aldera]

Thanks for the reply.

I installed the PostgreSQL App and created a server called, "indigo_history" on port 5432. It's on the same Mac mini as my Indigo Server. The database has no password. I made the settings on the SQL Logger configuration page. It's up and running. Thanks again for your help.

Ralph