Sqlite3 constant issues

I realise that SQLITE3 is an external module and not the responsibility of the Node-Red guys, but honestly, every time there is an upgrade showing in the NR palette manager for SQLITE3 in Node-Red (and thanks to DCJ and others that at least rebuilding works) SQLITE3 has to be rebuilt after stopping Node-RED. It is the only package that needs this and I'm now wondering whether it would be better to go back to MYSQL or Maria. Does anyone have a better way than sitting through a long rebuild every time?

Building should only be required once (that is the purpose of building). Are you running NR in a container ?

No I'm running NR in Buster on the Pi. Building is needed for SQLITE almost every SQLITE3 node update (no binaries) - and until I do that it brings my NR sqlite flows to a halt,

You don't need to update every time there is a new release, unless the update affects you. Also you don't need to stop node-red before rebuilding it, you can build it while node-red is running, then restart node-red. So set it building and go and have lunch, then restart later.

I like to have the latest updates - no other nodes (and I install lots) give any trouble, even the RPI serial noe has been behaving in recent updates. Thanks for that, it does help being able to continue to run NR.. but it would be so much better if they'd fix it, SQLITE updates have caused many of us problems for a long while now.

1 Like

Do you mean updates to node-red-node-sqlite? If so then it has not been re-released for over three months.

Do you mean somebody should supply the binaries? If so then who?

1 Like

Hi Colin, if you wish to start a dispute with someone, please pick someone else (and humble apologies if that wasn't your intention), I was merely expressing my view and the knowledge that out of MANY Pi installations and Many NR setups and upgrades, NO other NR nodes I've used have given me as much trouble as SQLITE - and I'm not the only one. SQLITE and the NR node work a TREAT but almost every upgrade of SQLITE Node means a SQLITE rebuild (with lots of warnings about unused vars and deprocation) to get back to square one. I moved to SQLITE as people were harping on about SD wear and tear with MYSQL on the PI. It could be suggested that with light use, I could be wearing the SD down as much with regular SLITE rebuilds as I would be making light use of MYSQL or as seems to be the flavour now, MariaDB.

I don't think @Colin is trying to start an argument here - although more temperate language would be helpful.

It is a valid point that the sqlite node has not had any updates for a long time, so it is perhaps slightly odd that you should see it getting rebuilt so often. Could you qualify when you see it rebuild? Does it happen whenever you install any other node? Or just when you install particular things?

At the moment, I don't think its clear what needs fixing - or whether any of these issues have been fed back to the right people. You acknowledged at the start of your first post that sqlite3 is an external module and not the responsibility of anyone in this forum. So whilst we can offer sympathy and discussion, any ultimately resolution isn't going to come from this forum.

Apologies if I sometimes come across rather more brusque than I intend. It comes with being a grumpy old man I think.

Righto guys, also valid point that this likely isn't the right forum to get the problem fixed.. Nick, I check "manage palette" regularly just as I do "apt-get update/apt-get upgrade". Anyway this week, the response was (for example) that "node-red-node-sqlite is 0.3.6 - upgrade to 0.3.7".

NOW, I'm looking at the page "https://www.npmjs.com/package/node-red-node-sqlite" where it DOES to be fair say that you should use " npm i --unsafe-perm node-red-node-sqlite" - and points out that a lengthy rebuild
"cd ~/.node-red
npm rebuild"
might be needed. So I just tried the "npm -i" version now and sure enough, the usual messy recoimpile followed with lots of "this statement may fall through" etc. Looks like you and DCJ are the fellows who maintain this node.

I've NOT done a major NPM update recently (I'm up to date) but after this lengthy, noisy recompile I restarted Nod-Red and still "0.3.6 - upgrade to 0.3.7" - I did the "rebuild" as detailed on that page, which triggered rebuilds of serial, I2c etc.... and took a LOT longer... I looked in GIT - can't see anywhere to discuss this node.. probably why I ended up here. Back to square one. The upgrade to 0.3.7 did not happen. After a reboot, NR is still on 0.3.6

Did you try a fresh npm install in your workspace dir (.node-red)? This has solved most of my update problems when something got stuck, especially with native code.

Your dependencies should be declared in package.json anyway, so just delete or move node_modules and more importantly the package-lock.json and run a fresh installation afterwards.

Kuema - can you please clarify. Assuming I need to stop NR first, and hence then after that, sitting in the /home/pi/.node-red directory as user pi - "rm node_modules" then "rm package-lock.json" then "npm install" ?

Exactly. That will reinstall all packages with the latest versions depending on your package.json.
Removing the package-lock.json is an important step, otherwise you could end up with older versions of some packages, as they are locked to a specific version by this file.

I would just rename node_modules into node_modules_old or so. This way you have a fall-back in case something goes wrong. You can delete it afterwards if everything is up and running again.

I would be cautious about following this advice. I would first find why you have the problem that you do with the upgrade apparently not working. First I would see what these commands show (run from the .node-red folder).

npm list node-red-node-sqlite
npm list -g node-red-node-sqlite

Good point, Colin!

Almost forgot about global modules. I try to avoid using them because they gave me more trouble than they are worth. Instead I try to keep everything local in my workspace directory. That way I can use the upgrade procedure I described without having to care about global stuff.


Thank you Colin.... I did what you asked from the .node-red dir and got this...

pi:.node-red:13:17[127]> npm list node-red-node-sqlite
node-red-project@0.0.1 /home/pi/.node-red
└── node-red-node-sqlite@0.3.6

pi:.node-red:13:18[0]> npm list -g node-red-node-sqlite
└── (empty)

So there appears only the one - local and it is indeed not the (apparently available but didnt install for me) latest.

So do I follow Kuema's advice now or something else? I'm all out of ideas...

what does npm outdated (in that dir) report ?

It appears the install failed
Run the install again from the .node-red directory and copy/paste the whole command and results. The last few lines of the install should tell you whether it succeeded.

Sorry about the delay Dave, broadband here went off earlier. Here.. and this surprises me..

pi:.node-red:15:34[0]> npm outdated
Package Current Wanted Latest Location
node-red-contrib-blynk-ws 1.0.0 0.9.2 1.0.0 node-red-project
i2c-bus 4.0.7 4.0.10 4.0.10 node-red-project
node-red-contrib-web-worldmap 2.0.18 2.0.20 2.0.20 node-red-project
node-red-node-sqlite 0.3.6 0.3.7 0.3.7 node-red-project

That WORLDMAP has just changed this afternoon. Don't know why it is reporting blynk-ws.. It says I have 1.00 ie current.

So the sqlite line does indicate that it should allow it to upgrade ... (ie the wanted column is greater than the installed column). And yes that mean it has failed to install for some reason (rather than being locked and re-installing 0.3.6) - so yes as @Colin suggested try (in that dir)

npm i --unsafe-perm node-red-node-sqlite@latest

(sit back and wait) and then paste the whole log here