manual browse, de-bug and restore database?

[renerene]

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?

[waltervl]

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.

[Kedi]

Isn't there a domoticz_crash.log? That might give some hints where to look for.

[zicht]

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

[Kedi]

So perhaps there is no error in the database, but somewhere else.
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

domoticz_crash.log?

[zicht]

domoticz_crash.log?

you can enable logging as written in the same page and viewtopic.php?t=8878

[Kedi]

A domoticz_crash.log is created at the moment of the crash and is something different that domoticz logging.

[Kedi]

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.

Just as extra info for Linux.
If you have a webserver running then you can use "phpLiteAdmin" and view/change the Domoticz database.

[waltervl]

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.

Just as extra info for Linux.
If you have a webserver running then you can use "phpLiteAdmin" and view/change the Domoticz 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.

[renerene]

So perhaps there is no error in the database, but somewhere else.
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

domoticz_crash.log?

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.

the log:

Code: Select all

2025-01-20 04:48:00.631 Error: Gas - invalid value: 'nan'

The problem was in the device. Not in the device name Gas device itself, but two other devices that i recently deleted. These were of TYPE Gas. There was about 2 or 3 minute log before DZ crashed and that gave me the option to study the failure. I found out a red log line that mentioned two devices that I recently deleted (type Gas). These devices were not to be found in the devices tab. Somehow, deleting them was halfway interrupted. Below the surface, they made Domoticz crash.

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

Create database backup

Code: Select all

cp /home/pi/domoticz/domoticz.db /home/pi/domoticz/domoticz.db.bak

Open SQLite database

Code: Select all

sqlite3 /home/pi/domoticz/domoticz.db

Remove devices from relevant tables

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);

Exit SQLite

Code: Select all

.quit

Restart Domoticz

Code: Select all

sudo service domoticz start

[Kedi]

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.

[renerene]

So this could not have happened.

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.

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

[Kedi]

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.

[renerene]

You can't just have the program delete records based on that error.

Correct. What about....

IF device=deleted AND device triggers things THEN delete device for real END

[Kedi]

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.