SQLITE3 Raspberry Pi Problems and Solutions?

SQLITE3 on Node-Red on Raspberry Pi

If like me, upgrading Node-Red (in my case on Raspberry Pi) is often problematic – then it may well be worthwhile reading this update – at last solving a long-lasting issue including SQLITE and other installs which rely on NodeJS.

If you regularly upgrade a Node-Red installation which makes use of the Node-Red SQLITE node, perhaps you use the serial port and/or i2c you may have come across installation failures – if so, read on.

Node-Red

Over the years I have upgraded my Node-Red installation which currently statnds at NR 1.32 – using the script below – which you will find on the Node-Red website.

bash <(curl -sL https://raw.githubusercontent.com/node-red/linux-installers/master/deb/update-nodejs-and-nodered)

After a 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.3.2 – which completely trashed my setup.

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…you can 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 back in November 2020 – the improved NR script and the 4 lines above DID solve the SQLITE upgrade problem – but you MUST be in the correct 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.

SQLITE3 mess

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 worked just fine using NodeJS 10 – well, that is, this has worked well in the past but By April 2021, Node-Red was wanting to use NodeJS 12.x – and I found myself back at square one.

Update April 2021 – upgrading to NR 1.32. – I ran the NR upgrade script which Killed node.js v10 – installed node v12.22.1 and npn 6.14.12 – installed node-red 1.31 (previously was 1.2.9)… no other messages. Firstly I2c failed “Error: Could not locate the bindings file” – I was not actually using i2c in NR so I simply removed reference to it in the Node-red setting.js file – this time however that 4-line upgrade script failed – “This is NOT a problem with NPN” it said… so I tried rebooting and ran the SQLITE3 4-liner again – STILL no SQLITE…. so I tried something from a long time ago – deleting /home/pi/.node-red/node_modules/node-red-node-sqlite and re-ran the 4-line script – no further forward.

Node-Red said “node-red-node-sqlite (0.6.0): sqlitedb, sqlite”

Thanks to help from the NR forums – I went back to my clone of an earlier NR – but this time, before running the official NR/Node upgrade script – I performed:

sudo-apt update
sudo apt full-upgrade

The “full-upgrade” is important. THIS time after rebooting, I used the official Node-Red upgrade script as above – and everything WORKED without any messing with SQLITE, no i2c or serial issues. I can confirm the upgrade to NR 1.3.2 worked – and I’ve since upgraded all nodes that needed upgrading – without ANY issues – but for one tiny thing – this time, the NR upgrade script did NOT try to upgrade Node.JS which remained at 10.23.3 – since then I’ve spoken to the guys on the Node-Red forum (Colin and Dave in particular) and FINALLY – I’m BANG up to date with Node-Red AND Node-JS… if only I’d had this knowledge in the past… see this discussion… which includes (vitally) changing a file on the RPi which tells it which version of NodeJS it should be upgrading to…

https://discourse.nodered.org/t/npm-versioning-and-palette-manager-confusions/44064/64

It turns out as you’ll see in the link above, that everytime I thought I was upgrading modules to the latest NodeJS, I eas wrong as I’d missed out one vital step, telling my RPi which version or NodeJS to use.

it is important to ensure you do not have multiple installs of SQLITE – I had a GLOBAL install, another in the /home/pi/node_modules folder and finally an install in /home/pi/.node-red/node_modules – and my problems were largely as a result of no binaries being available for SQLITE (this also affects some other nodes) which means the installation needs ro “rebuild” them automatically from source to work with the updates NODEJS – and that means your system needs to be aware of which NodeJS it should be using – that does not happen automatically. See details in the discussion above.

It turns out that my global (-g) SQLITE installation was a legacy from years back and the /home/pi/node_modules folder IN TOTAL was a duplicate – after cloning my installation I removed that folder altogether. Indeed for a typical Node-Red installation, it seems (as you will see in the discussion above), Node-Red itself is globally installed – everything else should be installed from the /home/pi/.node-red folder (i.e. locally). As a result of this it looks like the NR developers may now skip NodeJs 12 and recommend NodeJS 14 – and that’s where I stand mid-April 2021 – NR 1.3.2, NodeJS v14.16.1 – i.e. fully up to date WITHOUT losing my data or starting from scratch – despite what you may read elsewhere, it IS possible to take NR installations from as far back as Raspbian “Jesse” or “Stretch” all the way through “Buster” to the latest Raspbian named simply “Raspbian OS” while retaining whatever content you may have built up over time.

Facebooktwitterpinterestlinkedin

29 thoughts on “SQLITE3 Raspberry Pi Problems and Solutions?

  1. was joking 🙂
    i mean, it’s known mysql corrupt sdcard on the long run, due to wear leveling and continuous writing… but sqlite too, with it’s long compile times and failing in this, and redoing and updating and what else, starts to be no good for sd, too 🙂

  2. If you are going to use databases on a RPi then you should use the more industrial SD cards.

    The very best SD cards use SLC (Single Level Cell) technology but are hard to find and very expensive – but often come with 5 year guarantees.

    MLC (Multi-Level Cell) based SD cards are easy to get and are more expensive than TLC (Triple Level Cell) cards but have much better endurance.

    The standard SD cards are usually TLC and are what is called consumer grade. Even then, some brands seem to work more reliably than others.

    I have been using Transcend 32GB High Endurance SD cards for a while and have no issue with MySQL databases.

    If your project and/or data has real value then you should use MLC wherever possible.

    1. Do you have specific known good makes and models in mind? I’ve not heard Transcend recommended before – it is usually Sandisk and Samsung that get mentioned as winners.

      1. Samsung Evo and Evo+ 32GB cards all the way down!

        My Pi2 has now been running several years on one and my Pi3 running for over a year. Even occasional power outages and forced power terminations (e.g. pull the plug) haven’t caused any issues at all.

  3. I could easily see me reverting to Maria (MySQL) as the SQLITE issues continue with no sign of a script fix. In Node-Red when updating, I frequently have to “rebuild” Sqlite3 (several minutes) which is a pain – and the build script is full of un-useful warnings about statements falling through.

    1. what about MongoDB? It uses json as native data format, it’s nodejs based (and you already have it), has excellent nodered support (AFAIK)…

      install: https://yannickloriot.com/2016/04/install-mongodb-and-node-js-on-a-raspberry-pi/

      web gui: https://webapplog.com/mongoui/

      comparison with sqlite and mysql: https://db-engines.com/en/system/MongoDB%3BMySQL%3BSQLite

      if you want to go for mysql, here some optimizations for PI: https://central.owncloud.org/t/mysql-mariadb-optimal-settings-for-running-on-raspberrypi-3-model-b/17004

    1. Excellent feedback. I’ve yet to put a large HD on the Pi. These notes however have enabled me to install/upgrade Node-Red on Buster on the Pi3 and now P4 and it all works without having to start from scratch.

      1. i see no point in using a 500gb hd, you’ll never use it fully with a raspberry (unless you use it as a nas, which, if not an rpi4 model, is not worth because of the shared bus between usb and eth), and at that point you can just use mysql or others which will not need to get a PITA every time you update your nodes 🙂

        1. The only reason I used the 500GB drive is that I had it on hand after replacing the hard drive ib a laptop with an SSD drive.

  4. The above represents a TINY fraction of the warning messages you see during the rebuild.
    Why not fix the compiler flags as the code runs fine?

    https://sqlite.org/forum/forumpost/44578d49b6:

    gcc -g -Wpedantic -Werror -Wall -Wextra \
    -Wsign-compare -fPIC -std=c89 -Wno-long-long \
    -Wno-implicit-fallthrough \
    -c -o sqlite3.o sqlite3.c

    1. I WAS going to come back with a quick answer “that’s fiine if you know how to do it” – then realised you’ve actually provided the answer by the look of it – well done.. it defeats me why the author left in such a level of warnings when the end user running his or her script can do nothing about them.

  5. I have just spent a considerable amount of time pulling sqlite out of my systems and moved on to MariaDB due to these ongoing issues with the node red module. It was brought to a head when I wanted to move some of the flows to a HASSOS (Home Assistant OS) machine and discovered you cannot run the sqlite node on that OS because you need the make tools to compile and they are not available on HASSOS. That and the fact that the Home Assistant native log can get very large and uses sqllite by default. So I bit the bullet and upgraded and MariaDB works well, the Pi4 has no problem running the database server on top of everything else.

    1. Hi Steve

      Well,historically I moved to SQLITE and put that in my “The Script” because conventional wisdom said it was lighter on SD writes than MYSQL (hence Maria) – but but of course the goalposts have changed with SSD (and coming up in here this week, a small eMMC module to replace SD). I really think the people inolved in bringing SQLITE to Raspberry Pi (much as we all appreciate this stuff is most likely voluntary) need to rethink the compilation or make precompiled modules more readily available for SQLITE – I’ve lost more than one night’s sleep over the years with constant issues installing or upgrading SQLITE in Node-Red – almost all other nodes just update, no problem.

      I have Maria installed of course but never gotten around to modifying my queries to work with Maria/MySQL.

      I think first I’ll take a look at that comment above by Rinie Kervel then if all else fails spend the time to eliminate SQLITE.

  6. People first move over to SSD and when budget is OK change to Pi4. I made a clone of my freshly generated Debian in 3,5 minutes SSD>SSD clone on USB 3.
    I was trying to generate another fresh copy in a Pi 3 as a backup to my homeserver, but that hanged at installing Node_RED(half an hour was enough to kill the Script by pulling the power off). A SSD with USB3 of 120GB costs 23 euro. The benchmark shows also almost 300MB/s big chunk writing speed.

  7. I’m trying to get follow along with a Node-Red flow with GPS data that uses sqlite and was advised to use phpliteadmin as it is “easy to use and set up”. Well, I’ve spent the every evening this past week trying to the phpliteadmin to work and I have not clue. Anyone have pointers or can send me in the right direction? I’ve not been able to find much in a search. I get to the webpage and then it asked to great a database, but then I get the error that sqlitDatabase not installed.

    RPi 4, Node-Red 1.2.5 and installed the sqlite/phpliteadmin with Synaptic.

    1. It’s such a long time since I installed SQLITE from scratch – I use “the script” to install NR, MQTT, SQLITE and the various NR nodes on a fresh Raspberry Pi. The only issues I’ve had, have been upgrades ro SQLITE node – but it looks like the upgrade to the NR install/upgrade script has fixed that. So – before thinking about NR, you need SQLITE itself in there. Never used Synaptec so can’t comment on that. phpliteadmin will create databases but only if the actual SQLITE itself is installed on the Pi. Possibly look at “the script” for pointers?

    2. extracting from the script, let’s say for debian 10: you need apache+sqlite:
      sudo apt-get -y install apache2 libapache2-mod-php7.3 sqlite3 php-sqlite3 php-xml php-mbstring

      then install phpliteadmin:
      cd /var/www/html
      sudo mkdir phpliteadmin
      cd phpliteadmin
      sudo wget –no-check-certificate https://www.phpliteadmin.org/phpliteadmin-dev.zip
      sudo unzip phpliteadmin-dev.zip
      sudo mv phpliteadmin.php index.php
      sudo mv phpliteadmin.config.sample.php phpliteadmin.config.php
      sudo rm *.zip
      sudo mkdir themes
      cd themes
      sudo wget –no-check-certificate http://bitbucket.org/phpliteadmin/public/downloads/phpliteadmin_themes_2016-02-29.zip
      sudo unzip phpliteadmin_themes_2016-02-29.zip
      sudo rm *.zip

      then configure it, manually going to search for these 3 parameters, or via sed and following lines (IMPORTANT in the 2nd one, change $adminpass to your wanted pass)
      sudo sed -i -e ‘s#\$directory = \x27.\x27;#\$directory = \x27/home/pi/dbs/\x27;#g’ /var/www/html/phpliteadmin/phpliteadmin.config.php
      sudo sed -i -e “s#\$password = \x27admin\x27;#\$password = \x27$adminpass\x27;#g” /var/www/html/phpliteadmin/phpliteadmin.config.php
      sudo sed -i -e “s#\$subdirectories = false;#\$subdirectories = true;#g” /var/www/html/phpliteadmin/phpliteadmin.config.php

      1. Thanks Mr.Shark, ran everything and nothing new was installed, the last 3 items produced this error:

        sudo sed -i -e ‘s#\$directory = \x27.\x27;#\$directory = \x27/home/pi/dbs/\x27;#g’ /var/www/html/phpliteadmin/phpliteadmin.config.php
        sed: -e expression #1, char 1: unknown command: `�’

        I made the changes with nano, but when I run the web application, the old password gets me in and not the new one and it still doesn’t let me create any databases.

        “There was a problem setting up your database, /var/lib/phpliteadmin/. An attempt will be made to find out what’s going on so you can fix the problem more easily.

        Checking supported SQLite PHP extensions…

        PDO: installed
        PDO SQLite Driver: installed
        SQLite3: installed
        SQLiteDatabase: not installed”

        Has to be something simple, where is it starting from perhaps?

        1. yes, you can use an external config file, or put all in main file as i do, it’s the same… don’t copy paste those lines because blog formatted them wrong, just look at them in the sed part to see what to search and how to replace that, are just 3 lines…

  8. I unloaded everything to do with phplliteadmin and sqlite, removed everything I did with Mr.Shark’s instructions above. Then I redid the instructions above and I have blastoff!
    Now I just need to add the fields to the database.

    Thanks for the assistance.

  9. I don’t understand the link between NR and NodeJS either. Perhaps it’s a feature that the two are independent. When I upgraded to NR 1.3.1 I checked the version of NodeJS on a lark. Good thing I did because it was version 8 and about to be unsupported by NR. So I bumped it up to v12 (the newest released version for long term support). Technically v8 was still OK for NR, but it seems NR should tell you during the upgrade that you might want to also upgrade NodeJS.

Leave a Reply

Your email address will not be published. Required fields are marked *

Leave the field below empty!

This site uses Akismet to reduce spam. Learn how your comment data is processed.