manual browse, de-bug and restore database?
Moderators: leecollings, remb0
manual browse, de-bug and restore database?
Has anyone successfully used a tool like DB Browser for SQLite to manually browse, debug, and restore a database?
My issue: Domoticz crashes after about 2 minutes, shortly after a "NaN" error appears in the log for one of my devices. I’ve tried using the regular SQL dump and restore commands suggested on the forums, but without success. There doesn’t seem to be any apparent error in the database.
My plan is to open the database and manually inspect it for issues. Any advice or experiences with this approach?
My issue: Domoticz crashes after about 2 minutes, shortly after a "NaN" error appears in the log for one of my devices. I’ve tried using the regular SQL dump and restore commands suggested on the forums, but without success. There doesn’t seem to be any apparent error in the database.
My plan is to open the database and manually inspect it for issues. Any advice or experiences with this approach?
- waltervl
- Posts: 5371
- Joined: Monday 28 January 2019 18:48
- Target OS: Linux
- Domoticz version: 2024.7
- Location: NL
- Contact:
Re: manual browse, de-bug and restore database?
Yes, works fine. On Linux you have to run sglitebrowser with the same rights as the database owner (eg root) else it cannot open it.
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
Isn't there a domoticz_crash.log? That might give some hints where to look for.
Logic will get you from A to B. Imagination will take you everywhere.
-
- Posts: 268
- Joined: Sunday 11 May 2014 11:09
- Target OS: Windows
- Domoticz version: 2023.1+
- Location: NL
- Contact:
Re: manual browse, de-bug and restore database?
Mostly my succes in this came from export database to text file and build a new one from the text file...
Normally the error should not be imported again...
see https://wiki.domoticz.com/Troubleshooting
Normally the error should not be imported again...
see https://wiki.domoticz.com/Troubleshooting
Rpi & Win x64. Using : cam's,RFXCom, LaCrosse, RFY, HuE, google, standard Lua, Tasker, Waze traveltime, NLAlert&grip2+,curtains, vacuum, audioreceiver, smart-heating&cooling + many more (= automate all repetitive simple tasks)
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
So perhaps there is no error in the database, but somewhere else.
Perhaps a part of the logfile around the error.
domoticz_crash.log?
Perhaps a part of the logfile around the error.
Code: Select all
shortly after a "NaN" error appears in the log for one of my devices
Logic will get you from A to B. Imagination will take you everywhere.
-
- Posts: 268
- Joined: Sunday 11 May 2014 11:09
- Target OS: Windows
- Domoticz version: 2023.1+
- Location: NL
- Contact:
Re: manual browse, de-bug and restore database?
you can enable logging as written in the same page and viewtopic.php?t=8878domoticz_crash.log?
Rpi & Win x64. Using : cam's,RFXCom, LaCrosse, RFY, HuE, google, standard Lua, Tasker, Waze traveltime, NLAlert&grip2+,curtains, vacuum, audioreceiver, smart-heating&cooling + many more (= automate all repetitive simple tasks)
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
A domoticz_crash.log is created at the moment of the crash and is something different that domoticz logging.
Logic will get you from A to B. Imagination will take you everywhere.
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
Just as extra info for Linux.
If you have a webserver running then you can use "phpLiteAdmin" and view/change the Domoticz database.
Logic will get you from A to B. Imagination will take you everywhere.
- waltervl
- Posts: 5371
- Joined: Monday 28 January 2019 18:48
- Target OS: Linux
- Domoticz version: 2024.7
- Location: NL
- Contact:
Re: manual browse, de-bug and restore database?
And additional, if you make a database backup with menu Setup-Settings, tab Backup/Restore so it is stored locally on your PC/Laptop you can upload the database to https://sqliteviewer.app/ to review the database. You cannot change it with this app.
Domoticz running on Udoo X86 (on Ubuntu)
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Devices/plugins: ZigbeeforDomoticz (with Xiaomi, Ikea, Tuya devices), Nefit Easy, Midea Airco, Omnik Solar, Goodwe Solar
Re: manual browse, de-bug and restore database?
After a day struggling i was happy to find the issue. I used two open source Database browser (DBeaver was better), but that did not get me further. The database looked ok.Kedi wrote: ↑Monday 20 January 2025 19:37 So perhaps there is no error in the database, but somewhere else.
Perhaps a part of the logfile around the error.domoticz_crash.log?Code: Select all
shortly after a "NaN" error appears in the log for one of my devices
the log:
Code: Select all
2025-01-20 04:48:00.631 Error: Gas - invalid value: 'nan'
Here's the perplexity script I used to solve the problem:
Problem
Persistent error messages in Domoticz log for devices with IDX 2584 and 2592, which are not visible in the Devices overview.
Solution
Follow these steps to clean up and remove problematic devices:
Stop Domoticz service
Code: Select all
sudo service domoticz stop
Code: Select all
cp /home/pi/domoticz/domoticz.db /home/pi/domoticz/domoticz.db.bak
Code: Select all
sqlite3 /home/pi/domoticz/domoticz.db
Code: Select all
DELETE FROM DeviceStatus WHERE ID IN (2584, 2592);
DELETE FROM Meter WHERE DeviceRowID IN (2584, 2592);
DELETE FROM Meter_Calendar WHERE DeviceRowID IN (2584, 2592);
Code: Select all
.quit
Code: Select all
sudo service domoticz start
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
Domoticz uses a SQLite database. SQLite transactions are ACID. ( https://en.wikipedia.org/wiki/ACID )
So this could not have happened. "Somehow, deleting them was halfway interrupted"
Cause must have been something else.
B.T.W. you don't need to stop Domoticz to execute those 3 SQL commands.
I frequently change the Domoticz database 'on the fly' with phpliteqdmin without stopping Domoticz.
So this could not have happened. "Somehow, deleting them was halfway interrupted"
Cause must have been something else.
B.T.W. you don't need to stop Domoticz to execute those 3 SQL commands.
I frequently change the Domoticz database 'on the fly' with phpliteqdmin without stopping Domoticz.
Logic will get you from A to B. Imagination will take you everywhere.
Re: manual browse, de-bug and restore database?
yes, database technical there was no error. But somehow the deleted devices were still there with a value that made DZ crash, allthough, It was able to produce a red error line before crash.So this could not have happened.
Would be great if they could add an extra database check: "IF value is NaN AND device deleted THEN...."
I still have the corrupt database in case DZ programmers are interested
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
NaN means Not a Number. So the database has not a number where the program expects one.
You can't just have the program delete records based on that error.
You can't just have the program delete records based on that error.
Logic will get you from A to B. Imagination will take you everywhere.
Re: manual browse, de-bug and restore database?
Correct. What about....You can't just have the program delete records based on that error.
IF device=deleted AND device triggers things THEN delete device for real END
-
- Posts: 561
- Joined: Monday 20 March 2023 14:41
- Target OS: Raspberry Pi / ODroid
- Domoticz version:
- Location: Somewhere in NL
- Contact:
Re: manual browse, de-bug and restore database?
Look in the 'corrupt' database at devices 2584 and 2592 and look for some data inconsistancy e.g. string or nil
And I don't think the database is corrupt, but some of the data might be not consistant.
I don't think device have a status 'deleted', so if the device is deleted you can't delete it 'for real'
You might cleanup history for non existing devices, and that should normally have been done by the program.
But you could create your own set of SQL command to check for history that has no device connected to it, just to do that if you like.
And I don't think the database is corrupt, but some of the data might be not consistant.
I don't think device have a status 'deleted', so if the device is deleted you can't delete it 'for real'
You might cleanup history for non existing devices, and that should normally have been done by the program.
But you could create your own set of SQL command to check for history that has no device connected to it, just to do that if you like.
Logic will get you from A to B. Imagination will take you everywhere.
Who is online
Users browsing this forum: No registered users and 0 guests