Recovering old historical data

[ricorico94]

Hi,

(Using domoticz v2024.4 on Bulls'eye raspberry pi.)
I faced a corrupted domoticz and domoticz database recently. I could rebuild sdcard and I could recover recent backup, however, I faced "database malformed" errors when viewing the logs of temperature sensors.
So I tried to repair the database using this procedure https://wiki.domoticz.com/Troubleshooti ... database_2 -using the Windows approach with DB Browser for SQLite, to export to SQL and then import to database to create new domoticz.db.

I could then build a smaller domoticz.db which doesn't declare such malformed database, but despite the last 7 days graph is filled with data (working properly), the older views by day and months are empty.
So I tried to recover data from an older backup to at least recover "very old" historical data. I tried to follow this procedure viewtopic.php?t=35625, but I couldn't execute successfully the few sqlite3 commands. So I tried to use the DB BRowser for SQLite software to export a Temperature_Calendar from an old backup (exporting as CSV) and then importing in my clean database that table (using Import CSV into existing empty table). (I did to reimport my entire historical data, so no need for selecting specific dates..)
After doing this, my database doesn't show any error in DB Browser (integrity check ok), and domoticz loads properly. The hourly graph of last 7 days still works, but the older period graphs (daily, monthly views) are still empty. Moreover, I again get these errors (when showing temperature sensors):

2026-06-21 19:38:38.914 Error: SQL Query("SELECT Temp_Min, Temp_Max, Chill_Min, Chill_Max, Humidity, Barometer, Temp_Avg, Date, SetPoint_Min, SetPoint_Max, SetPoint_Avg FROM Temperature_Calendar WHERE (DeviceRowID==2074 AND Date>='2025-06-21' AND Date<='2026-06-21') ORDER BY Date ASC") : database disk image is malformed
2026-06-21 19:38:38.917 Error: SQL Query("SELECT Temp_Min, Temp_Max, Chill_Min, Chill_Max, Humidity, Barometer, Temp_Avg, Date, SetPoint_Min, SetPoint_Max, SetPoint_Avg FROM Temperature_Calendar WHERE (DeviceRowID==2074 AND Date>='2024-06-21' AND Date<='2025-06-21') ORDER BY Date ASC") : database disk image is malformed
2026-06-21 19:38:38.920 Error: SQL Query("SELECT Temp_Min, Temp_Max, Chill_Min, Chill_Max, Humidity, Barometer, Temp_Avg, Date, SetPoint_Min, SetPoint_Max, SetPoint_Avg FROM Temperature_Calendar WHERE (DeviceRowID==2074 AND Date>='2026-05-21' AND Date<='2026-06-21') ORDER BY Date ASC") : database disk image is malformed
2026-06-21 19:38:38.924 Error: SQL Query("SELECT Temp_Min, Temp_Max, Chill_Min, Chill_Max, Humidity, Barometer, Temp_Avg, Date, SetPoint_Min, SetPoint_Max, SetPoint_Avg FROM Temperature_Calendar WHERE (DeviceRowID==2074 AND Date>='2025-05-21' AND Date<='2025-06-21') ORDER BY Date ASC") : database disk image is malformed
2026-06-21 19:38:39.071 Error: SQL Query("SELECT strftime('%Y', Date) as y,strftime('%m', Date) as m, AVG(Temp_Avg)/1.000000 as s FROM Temperature_Calendar WHERE DeviceRowID == 2074 GROUP BY strftime('%Y', Date), strftime('%m', Date)") : database disk image is malformed

What can happen ? How can I find more where the problem is to fix the database ?
Thanks in advance for your help

[Kimmyn]

Hello,

It seems that the corruption might be related to the historical tables instead of affecting the entire database. Given that the graphs from the last 7 days are functioning while older ones are not, I recommend starting with the `Temperature_Calendar` table. You might also consider performing a fresh SQLite dump and import, then comparing the schema of the old and new databases. While DB Browser is a handy tool, it might not identify every inconsistency. I truly hope you can recover your previous sensor history!