This is the November 27, 2020 update of an old, outstanding problem for Raspberry Pi owners who use and regularly upgrade Node-Red and who simultaneously use the Node-Red SQLITE node. The issue has also been known to affect the serial port and i2c on upgrading.
I KNOW I’m not the only one who has had issues with SQLITE3 upgrading in Raspberry Pi and Node-Red, if it happens to you… here is some invaluable info…
After a recent, successful upgrade to Node-Red 1.2.5 (leaving the SQLITE node slightly behind as it can be painful to update when there are node-js upgrades involved), I was feeling quite perky when along came NR 1.2.6 – which completely trashed my setup.
DaveCJ and others on the Node-Red DISCOURSE channel helped me yesterday without which I’d have been stuck on NR 1.2.5 possibly for a long time. The NR script you see here for upgrading Node-Red suffered a node-js upgrade-related issue which could cause failure. On the morning of November 27, 2020 this was FIXED.
bash <(curl -sL https://raw.githubusercontent.com/node-red/linux-installers/master/deb/update-nodejs-and-nodered)
These days whenever I upgrade Node-Red I use that script above ( a link to which is also included in my own “The Script”) and I subsequently (no reboot required) use the 4 lines below on my Raspberry Pi 4 installations which came to life years ago on RPI2 and Raspbian Stretch and which have been upgraded to Raspbian Buster (now called Raspberry Pi OS) and updated regularly (without starting from scratch or losing valuable data).
Once you have done the Node-RED upgrade as user PI…use these 4 lines (again as user PI)..
node-red-stop cd /home/pi/.node-red npm rebuild sqlite3 node-red-start
Be warned – the SQLITE rebuild will throw out MANY warnings… even in November 2020 – the improved NR script and the 4 lines above DO solve the SQLITE upgrade problem – but you MUST be in the right directory as indicated for the latter). Use the 4 lines above AFTER upgrading Node-Red and perferably before starting to run Node-Red.
I originally noted while upgrading other NR nodes – that when it came to the SQLITE update in the NR pallette manager, SQLITE failed. I stopped NR and ran the above – which usually worked but this has always been a CONSTANT pain as there is always the chance that this fix would not work. It does seem to work just fine now but ALWAYS make a backup before upgrading SQLITE in particular.
The above represents a TINY fraction of the warning messages you see during the SQLITE rebuild. They are mainly utterly pointless warnings but watch out for any ERRORS – they will be reasonably obvious.
That’s it, nothing else, just those 4 lines EXACTLY as above and despite all those warnings, the SQLITE upgrade should work just fine. It may take several minutes for the rebuild to complete after which you can restart Node-Red without a reboot.
But REALLY – the answer, if at all possible, despite its undoubted usefulness, is to DITCH SQLITE – this nonsense has been going on for YEARS… SQLITE in Node-Red is the ONLY Node-Red node package I know of that gives me constant grief like this.
I originally picked SQLITE due to fears that MYSQL (then Maria) might do excessive writing to SD. Today with booting and running on SSD over USB3 that is issue may vanish depending on your use-case.