SQLITE3 Raspberry Pi Problems and Solutions

I KNOW I’m not the only one who has issues with SQLITE3 in Raspberry Pi and Node-Red, so next time it happens to you… here is some invaluable info…

I just used the lines below on my second Raspberry Pi 4 which had been upgraded to Raspbian Buster (from Raspbian Stretch) some time ago.

As user PI… go to the /home/pi/.node-red directory

cd /home/pi/.node-red
npm rebuild sqlite3

Be warned – the rebuild will throw out MANY warnings… (update April 2020 – the 4 lines above DO solve the problem – but you MUST be in the right directory as indicated)

SQLITE3 mess

The above represents a TINY fraction of the warning messages you see during the rebuild.

That’s it, nothing else, just those 3 lines exactly as above and despite all those warnings, it works just fine. It will take several minutes for the rebuild to complete before you restart node-red.

But REALLY – the answer if possible is to DITCH SQLITE – this nonsense hs been going on for MANY MONTHS…. it is the ONLY  package that gives me constant grief like this.



13 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.

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.