Error: SQLITE_READONLY: attempt to write a readonly database

CoderN · 19 June 2025 18:47

I have a simple flow like this

where the properties of HAS2 SQLite is:

SQLStatement:
	SELECT
		metadata_id
	FROM
		states_meta
	WHERE
		entity_id = 'sensor.octopus_energy_electricity_[meter#]_[mpan#]_current_accumulative_cost'
;

The database was opened as Read=Only mode. It generated the following error:

Error: SQLITE_READONLY: attempt to write a readonly database

However, the same SQL statement works fine with readonly mode on CLI:

sqlite> .header on
sqlite> .open --readonly home-assistant_v2.db
sqlite>SELECT
    metadata_id
FROM
    states_meta
WHERE
    entity_id = 'sensor.octopus_energy_electricity_[meter#]_[mpan#]_current_accumulative_cost'
;
metadata_id
7361

How do I fix it?

Many Thanks!

marcus-j-davies · 19 June 2025 19:33

Welcome to the forums @CoderN

You will need to check access on the Node RED process user to that file path, even select statements, might do some background (non destructive) writes - like a temp table for instance (the mode you mentioned will stop altering data, not necessarily system based tables it might need to do stuff with) and file permissions will play a part here

Given this is HA - it might be beneficial to refer to the HA forums, as if using the Node RED plugin - they will have the answers, in how Access is granted to plugins at certain file paths

I could be wrong here of course, but it's my best guess.

Others may have more info (and may correct me)

Steve-Mcl · 19 June 2025 19:41

At a guess, the DB is in a directory that the account running the node-red process (inside the HA docker instance), does not have write access to?

If you raise this on the HA forum - they will probably understand how these things fit together and be able to offer advice.

CoderN · 20 June 2025 15:02

@marcus-j-davies

Thanks for your help. I have installed Home Assistant and Node-RED as 2 separated docker containers sharing a single user. I have verified the read permission inside the Node-RED container with docker exec. Here are the 2 compose.yaml files.

$ cat node-red/compose.yaml
---
services:
  node-red:
    container_name: node-red
    image: nodered/node-red:latest
    deploy:
      resources:
        limits:
          memory: 256M
    restart: unless-stopped
    user: "3005:3005"
    environment:
      - TZ=Europe/London
    ports:
      - "1880:1880"
    volumes:
      - ./data:/data:rw
      - ../homeassistant/config/home-assistant_v2.db:/mnt/ha2/config/home-assistant_v2.db:ro

$ cat homeassistant/compose.yaml
---
include:
  - ./volumes.yaml

services:
  homeassistant:
    image: linuxserver/homeassistant:latest
    container_name: homeassistant
    deploy:
      resources:
        limits:
          memory: 1G
    environment:
      - PUID=3005
      - PGID=3005
      - TZ=Europe/London
      - REQUESTS_CA_BUNDLE=/etc/ssl/certs/ca-certificates.crt
    volumes:
      - "/etc/localtime:/etc/localtime:ro"
      - "./custom-cont-init.d:/custom-cont-init.d:ro"
      - "./config:/config"
      - type: volume
        source: cctv
        target: /cctv
        volume:
          subpath: ha2/hass_events
      - type: volume
        source: cctv
        target: /config/nest
        volume:
          subpath: ha2/nest

    ports:
      - 8123:8123
    restart: unless-stopped

    depends_on:
      mosquitto:
        condition: service_started
        restart: false

I set the permission of the home-assistant_v2.db in node-red container as read only on purpose. Node-RED should not modify it.

And the SQL Node-RED it run was a simple SELECT statement. It should not require write access regardless what the sqlite file was generated by.

I have posted on the forums of Home Assistant for help as well.

CoderN · 20 June 2025 15:06

@Steve-Mcl

Thanks for your suggestion. Yeah. I have posted on Home Assistant forums as well.

Having said that, the node reporting error is a node-red-node-sqlite node. It seems to me that Node-Red SQLITE should be more relevant.

Steve-Mcl · 20 June 2025 15:15

The SQLite node is reporting it cannot write to the DB. This looks like permissions.

If you know how docker works (it is like a virtual machine running inside a host) - that is where HA runs Node-RED (and so that is where the SQLite node is running also). Therefore, the account that runs the Node-RED process is an account inside the VM (aka docker container) and so, by the looks of the DB file path (/mnt/xxx/yyy) the DB is NOT inside the container but actually inside the host (the computer running HA / Docker host). Thats why I said:

Colin · 20 June 2025 15:26

I think there is confusion about exactly what you are complaining of. Is it that, when accessing a read only database, it is not possible to read from the DB?

Steve-Mcl · 20 June 2025 15:54

oh, I see. I speed read the write error:

This might then be a quirk of sqlite. For example, I am not certain if select queries that cause a lock should have write access.

failing that, perhaps the OP has INSERT/UPDATE quersies in the flows?

failing that, the node-red-node-sqlite is always asking for open "with write" (if that is such a thing?)

Lets see what the OP says.

CoderN · 20 June 2025 17:11

@Steve-Mcl

Sorry for the confusion I have created.

I am not complaining. I know it must be my configuration errors. Let me provide more information about it.

For another basic test, I changed the file compose.yaml for node-red to

$ cat node-red/compose.yaml
---
services:
  node-red:
    container_name: node-red
    image: nodered/node-red:latest
    deploy:
      resources:
        limits:
          memory: 256M
    restart: unless-stopped
    environment:
      - TZ=Europe/London
    ports:
      - "1880:1880"
    volumes:
      - ./data:/data:rw
      - ../homeassistant/config/home-assistant_v2.db:/mnt/ha2/config/home-assistant_v2.db:rw

Now node-red is run as the default user (1000) and the home-assistant_v2.db is mounted as read-write.

Then I have changed the ownership of home-assistant_v2.db to user 1000. And it is writeable by the user node-red inside the container as shown below:

$ sudo docker exec -it -u 1000 node-red bash
e607b472495d:~$ id
uid=1000(node-red) gid=1000(node-red) groups=1000(node-red)
e607b472495d:~$ ls -lh /mnt/ha2/config/
total 4G     
-rw-rw-rw-    1 1000     1000        6.6G Jun 20 17:40 home-assistant_v2.db
e607b472495d:~$ touch /mnt/ha2/config/home-assistant_v2.db
e607b472495d:~$ ls -lh /mnt/ha2/config/
-rw-rw-rw-    1 1000     1000        6.6G Jun 20 18:23 home-assistant_v2.db
e607b472495d:~$ ps aux | grep "node-red"
    1 node-red  0:00 {entrypoint.sh} /bin/bash ./entrypoint.sh
    7 node-red  0:12 node-red
  778 node-red  0:00 bash
  846 node-red  0:00 ps aux
  847 node-red  0:00 grep node-red

And I have changed the database mode of the sqlite node to Read-Write-Create as shown below:

The SQL statement executed was SELECT * FROM states_meta ORDER BY entity_id DESC LIMIT 1

It still reported the same error Error: SQLITE_READONLY: attempt to write a readonly database when the testing inject was pressed.

What have I missed?

CoderN · 20 June 2025 17:21

The docker log might be helpful:

20 Jun 17:21:27 - [info] [sqlitedb:137383098fbd7a8e] opened /mnt/ha2/config/home-assistant_v2.db ok
20 Jun 17:21:29 - [error] [sqlite:HA2] Error: SQLITE_READONLY: attempt to write a readonly database
20 Jun 17:25:25 - [error] [sqlite:HA2] Error: SQLITE_READONLY: attempt to write a readonly database
20 Jun 17:25:27 - [error] [sqlite:HA2] Error: SQLITE_READONLY: attempt to write a readonly database

CoderN · 20 June 2025 17:24

Sorry for the confusion. I am not complaining. I know it must be my configuration errors.

I have stated the situation again with more information above. This time with the database access set to RWC mode and the read-write permissions of the database file of the node-red user inside the container verified. It still gave me the same error.

Steve-Mcl · 20 June 2025 18:00

My guess is the db has permissions issue OR it is being locked by HA.

Potential Clue: Upgrading to 0.24 resulted in read-only database - Configuration - Home Assistant Community

Have you tried restarting HA?

My next step would likely be to mount ../homeassistant/config with rw and do some simple file read/write tests (using the file nodes in node-red) to see if the node-red process has necessary permissions to the mounted fs. Next I would closely inspect the permissions of the db (though what these all are/should be is not something I am familiar with - HA folk will likely know how this is supposed to work off the top of their heads)

Best I can do i'm afraid (not a HA user)

CoderN · 20 June 2025 23:42

@Steve-Mcl

Thanks for your help.

I am happy to report that I have found the culprit. There are two more files accompany the sqlite database file. The error is gone once I have mounted the extra files inside the node-red container. In my case, they are home-assistant_v2.db-wal and home-assistant_v2.db-shm.

Topic		Replies	Views
SQlite error with node red on Docker General database , docker	6	579	31 January 2022
Accessing data on Sqlite3 from Node-Red on Docker General database , docker	3	207	16 March 2023
Error spreadsheet General	15	211	13 July 2024
Node-red-node-sqlite General	11	2221	8 August 2018
Errors related to sqlite when migrating from docker image 2.2.2-16 to 3.0.2-16 General	4	364	14 August 2022

Error: SQLITE_READONLY: attempt to write a readonly database

Related topics