Happy with my nice dashboards, I started running tests with my different devices, notably some fake MQTT devices.
One in particular: a fake device that receives from Node-RED data about my daily electrical consumption.
But this device is supposed to receive data only once a day, which allows me to display a nice histogram.
Except that while testing, I sent data several times today… which broke my chart!! It looked great and now it doesn’t look like anything.
Is there a way to delete these « undesirable » entries from my DB? (especially since I didn’t know much about SQLite… and even less about DuckDB ^^ )
I also need to make some adjustments in the database, to correct a solar production reading that failed three nights in a row, and everything piled up on the 4th night… So if there’s a tool to browse the DuckDB database and modify values, I’ll take it
I tried DBeaver, but when I go look for the table t_device_feature_state since I imagine that’s the right one, the most recent values are from the day before yesterday… How is that possible?
To confirm with @pierre-gilles, but the data on this timeline may be stored in memory then transferred to the file…
I’ll take a look!!
EDIT: No storage in « memory ».
From what I see, unlike the SQL DB, when we stop gladys, duckdb’s .duckdb.wal remains and is more recent than the .duckdb … Can you confirm? What are the dates and times of the two files for you?
For my part I only have 1 hour of inaccessible data (which corresponds to the time difference between the files - 9:52 vs 10:48):
Are you on Windows? I’m on macOS and I do have DuckDB in TablePlus, but apparently the other versions don’t have this DB yet. In your case, you should use DBeaver
Indeed, you must copy the two files (.duckdb + .duckdb.wal), and of course copy them only if Gladys is stopped.
That’s the case, but when Gladys is stopped (via docker stop gladys) as mentioned above, the .wal is still present (unlike the SQL db) and there is 1 hour of « missing » data (clearly present on the graphs) in Gladys.
I’ve been using DBeaver professionally (DB2, MS SQL, MuSQL, PgSQL …) and personally (SQLite) for quite a while now, and it’s a great tool. Because data access is done visually, in table form (spreadsheet-style), you can easily edit the data
Have a nice day,
Jean
I tried DBeaver Community, but a small problem: on my Gladys server running Linux DBeaver sees the database but returns an « access denied ».
A DBeaver Community on a Windows PC sees the local databases but not the network-accessible databases
@Einstein8854 did you duplicate it properly or at least stop Gladys before accessing it? It’s a single connection. You can’t access it at the same time as Gladys is running.
I retrieved my file, I found records that interested me, and I deleted them in DBeaver.
But when I tried to re-transfer my database to my server… FileZilla wouldn’t cooperate and refused to write to the directory.
So I had to temporarily change the permissions of the gladysassistant folder to be able to copy my modified file there.
This brings up again a topic that had been discussed regarding starting a container as a user other than « root »: could we create a thread to establish good containerization practices?
no ^^ well — still -1h. And when I disconnect from the DB in DBeaver, this time the .wal is deleted!^^
