SQLITE3 Raspberry Pi Problems

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 a Raspberry Pi 4 upgraded some days ago to Raspbian Buster from Raspbian Stretch.

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

npm rebuild sqlite3

Be warned – the rebuild will throw out several warnings…

That’s it, nothing else, just those 3 lines exactly as above. 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.

I needed to write that down – I didn’t invent it, but Node-Red hung up on me again, in the past it was when using Raspbian Stretch, now days after an UPGRADE to Raspbian Buster (not a new install – but thanks to Antonio I also have one of those as well)….



8 thoughts on “SQLITE3 Raspberry Pi Problems

  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

Leave a Reply

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

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