# SQLite table deleting over night, any advice!

**URL:** <https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755>\
**Category:** General\
**Created:** [6 April 2021 10:11 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755 "2021-04-06T10:11:39Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [6 April 2021 10:11 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/1 "2021-04-06T10:11:39Z")

</div>

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?

 ![Capture](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/b/1bd38a342e95d50ad209edf793e6e6d7d4a32404.png)

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [6 April 2021 10:16 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/2 "2021-04-06T10:16:17Z")

</div>

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 ?

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [6 April 2021 10:36 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/3 "2021-04-06T10:36:36Z")

</div>

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:  
 ![Capture1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/c/7c79bc312df50163dc0c73955a39b9a3ef000c8d.png)

Backup EXEC node:

 ![Capture2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/a/3a15fc235ac06aa598ed0033bb4f65cbf2cbc098.png)

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [6 April 2021 10:48 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/4 "2021-04-06T10:48:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 April 2021 10:54 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/5 "2021-04-06T10:54:09Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [6 April 2021 10:55 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/6 "2021-04-06T10:55:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [6 April 2021 10:59 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/7 "2021-04-06T10:59:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 April 2021 11:00 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/8 "2021-04-06T11:00:09Z")

</div>

> [@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.

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [6 April 2021 11:01 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/9 "2021-04-06T11:01:02Z")

</div>

Not using Docker at all

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [6 April 2021 11:01 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/10 "2021-04-06T11:01:22Z")

</div>

OK, it isn't that then.

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [7 April 2021 10:15 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/11 "2021-04-07T10:15:15Z")

</div>

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.

 ![Capture1](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/3/0/301d111847ab4229c0d01a56849cb12636dc8be7.png)

Any other ideas why this keeps happening over night?

I will add a Snip of the current flow

 ![Capture](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/6/6/66cbcd56db7f08d3c95241fd74940f8a8f75b74a.png)

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [7 April 2021 11:00 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/12 "2021-04-07T11:00:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![edje11](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/edje11/32/572_2.png) [@edje11](https://discourse.nodered.org/u/edje11)\
**Post date:** [7 April 2021 11:56 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/13 "2021-04-07T11:56:40Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [7 April 2021 12:03 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/14 "2021-04-07T12:03:40Z")

</div>

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

 ![nodered](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/2/a2a067f91da68d9ff5b46edbd8257fd3174b1702.png)  
 ![nodered2](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/9/1973c9efcdf6fb9889f9c3c5e488caca6fd3718c.png)

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

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [7 April 2021 12:04 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/15 "2021-04-07T12:04:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 April 2021 12:16 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/16 "2021-04-07T12:16:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dceejay](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dceejay/32/38_2.png) [@dceejay](https://discourse.nodered.org/u/dceejay)\
**Post date:** [7 April 2021 12:17 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/17 "2021-04-07T12:17:05Z")

</div>

so

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/5/e5a8d601bdfc8c136ee44f1cee9917f2345579b1.jpeg)  
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 ?

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [7 April 2021 12:19 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/18 "2021-04-07T12:19:14Z")

</div>

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

 ![Capture](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/d/ad59c90fd0499833b930d68c0cc1aa0c568c1e9e.png)

---

<div class="post-metadata">

**Author:** ![Dgillett](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@Dgillett](https://discourse.nodered.org/u/Dgillett)\
**Post date:** [7 April 2021 12:25 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/19 "2021-04-07T12:25:50Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![zenofmud](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/zenofmud/32/316_2.png) [@zenofmud](https://discourse.nodered.org/u/zenofmud)\
**Post date:** [7 April 2021 12:33 UTC](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755/20 "2021-04-07T12:33:45Z")

</div>

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.

[Next page](https://discourse.nodered.org/t/sqlite-table-deleting-over-night-any-advice/43755.md?page=2)