Node-red-node-sqlite

General
1

On my Raspberry Pi 3, as a result of upgrades in Node Red I did this morning I've ended up with issues with SQLITE - specifically node-red-node-sqlite.

I'm only using this to update 2 tables in sqlite, one flow runs every few seconds, really simple inserts, the other runs intermittently, another set of inserts, no high speed heavy loads, running day in and out without issue but now failing all the time since the node upgrade to 0.3 this morning.


8/7/2018, 5:16:30 PM[node: /home/pi/dbs/iot.db](http://things.bedrock.es:1880/#)msg : error

"Error: SQLITE_BUSY: database is locked"

8/7/2018, 5:20:34 PM[node: /home/pi/dbs/iot.db](http://things.bedrock.es:1880/#)msg : error

"Error: SQLITE_BUSY: database is locked"

8/7/2018, 5:20:59 PM[node: /home/pi/dbs/iot.db](http://things.bedrock.es:1880/#)msg : error

"Error: SQLITE_BUSY: database is locked"

Any pointers, suggestions welcome.
.

2

are they both pointed to the same file ? Since sqlite3 v3 they have "improved" the file locking so that only one connection may have access at one time. The workaround is to wire all request (maybe via link node) into the same node.

There is also an outstanding request to add READONLY mode so that reads and writes could happen without causing this issue but that won't help you in your situation as you want two sets of writes.

3

Hi Dave

I have 2 nodes writing to different tables in the same db and one node doing the odd read. I can certainly use a link to one node…. But as these are different tables this seems like a desperatel6y backward move. Imagine that happened with the MQTT nodes, I’d have to re-write everything.

Really? Only one node? Or does that only apply to using more than one node on the same TABLE which I’m not. I am talking to the same db however. Could you clarify? And thanks for this..

Pete

4

Sure enough, you are correct, I took off one sqlite node and via a link connected the output for it to the sqlite node on another flow. My READ is only done on NR init so I left that alone. One updates a field in table “logins” – the other inserts a record into table “timeseries” – an experiment. They have nothing to do with each other. This workaround clearly works but NR as I understand it is generally none-blocking and this is clearly blocking As the insert can’t start until the update is done even though they are unrelated tables in the same db.

As sqlite and serial are the nodes which have the most issues when doing npm upgrades, did I back the wrong horse here I wonder?

Pete

5

My scenario is quite toy.. I can imagine someone with dozens or hundreds of tables on different flows having a hard time with this news. You would not get this in other databases such as mysql but then mysql is not good for Raspberry Pi writes which is why I went for sqlite of course.

6

Indeed. I hear you. Should be possible to fix bur am on vacation so not going to look at a fix for a short while. (Hence the workaround).

7

Aha.. ok – enjoy your vacation, sorry, didn’t know. So its not SQLITE then but the node and a fix will come in time?

Pete

8

No. It is SQLite. But we can hopefully fix it in the node.

9

If you get a moment could you try this to see if it helps...
find the file sqlite.js that we install - hopefully node_modules/node-red-node-sqlite/sqlite.js

change line 13 to node.db = node.db || new sqlite3.Database(node.dbname);

(I.E. try to re-use existing db object.... just theorising here on a phone)
Thanks

10

Right, that’s clear. I don’t understand why they’d sabotage their product like that but that is clear enough.

Thanks for the clarification, that was driving me nuts and the workaround does (at least in my simple setup) appear to do the job.

Pete

11

Just got out of bed here in Spain.. Trying that mod now and yes that line 13 is in /home/pi/.node-red/node_modules/node-red-node-sqlite/sqlite.js

So back to 2 nodes after your mod below, not has a locking failure in 5 minutes… touch wood.. I guess I’ll lose that mod on any node update…

But yes after 10 mins still no errors -

12

OK - will make it so as and when I get a chance...
(but yes any other installs until then would need to be patched manually)