SQLite table deleting over night, any advice!

[Dgillett]

So I have created a MQTT data tracking system using node red, and storing the data into SQLite.

The issue I'm having is after I create the SQLite table and data starts logging, I go home for the day, when I come back I get the error no table found , and the table has been dropped. Below will be a snip of the Node red flow.

Does anyone know why the table is getting deleted overnight?

[dceejay]

are you sure that either the clear table or backup table functions are doing so in a way that doesn't actually wipe the table ? What happens if you run without those two actions happening ?

[Dgillett]

I can disconnect those nodes and see what happens tomorrow. It only happens over night.

The clear just wipes the tables data when I click on the inject. And the Backup just copies to a mounted drive. I will attach the two nodes configuration.

Clear Inject node:

Backup EXEC node:

[UnborN]

Hi.
I notice from the timestamps of the errors that they happen every 5 seconds.
it means that your reading / or writing every 5 seconds ?

And you have the Exec node doing a backup at the same rate ? with every read / write ?
(since its wired directly after the sqlite node)
I believe its the backup exec node that may be causing the issue. maybe corrupting the database because of the high rate of backup commands.

try to unwire that part of the flow and see what happens and if all is good for a couple of days wire it separately with an inject node or cron-plus to trigger once a week.

[Colin]

Also what OS are you running on and are you using Docker, or something similar, to run node-red? Is this running on a real device or on a VM or in the cloud somewhere?

[Dgillett]

Node-red is running On a RPI 4. Using Raspian

[Dgillett]

As of now I unhooked the backup node and hooked it to its own inject to backup once a week.

[Colin]

@Dgillett

Node-red is running On a RPI 4. Using Raspian

Using Docker or not? You did not confirm that you are not using a Docker install.

[Dgillett]

Not using Docker at all

[Colin]

OK, it isn't that then.

[Dgillett]

So I disconnected the EXEC Node and put a Inject node on it to where it will backup the database once a week.

I came in this morning and the table was still deleted, It happened at 6:09am EST. You can see in the following Snip.

Any other ideas why this keeps happening over night?

I will add a Snip of the current flow

[Colin]

Have a look in /var/log/syslog and see if anything happened between the one the worked and the one that didn't.

[edje11]

I'm curious what settings there are in the inject node "Drop table"
Maybe you can run it a couple of days without this inject node, disconnect the wire.

[Dgillett]

Here is a snip of the syslog. Looks like first error occured at or around midnight

Let me know if im missing something. Still relatively new to this.

[Dgillett]

I went ahead and disconnected the drop tab just in case it was that. But i did not put a timed interval on it.

[zenofmud]

Why it the pi rebooting at midnight? As you can see, NR restarts and will run any injectnode that has any 'inject' set

[dceejay]

so

It seems to start ok at 02:28
then 5 secs later fails to read a value
then 5 secs later can't write to database.

Is there some event every 5 secs ? does that undefined data then ripple through and corrupt what follows on the next tick ?

[Dgillett]

Would this crontab job reboot the pi at midnight, I think it may be the cause if it does.

[Dgillett]

I think that sudo /sbin/shutdown -r now is the problem. I just tested and it looks llike that was my cause. Thanks.

[zenofmud]

Can you so us the configuration of the sqlite node? The reboot should not be wiping out the file unless you are storing it in /tmp which gets wiped on a reboot.