Unable to Fetch Data from SQLite Database : attempt to write a readonly database (1544)

[drazicmartin]

Describe the bug It seam like the Grafana is trying to write into my data.db but does not have write access. I don't want grafana to have it. I simply want him to fetch data from my backend using pocket-base that use SQlite database under the hood. So I simply point data source to the data.db file. The FAQ says that it is fine if the plugins only have read-access :

Make sure, that you have access to the file and all the folders in the path of the file. Read access is enough for the plugin.

But after some time a simple query like SELECT * FROM [table] raise an error

Status: 500. Message: attempt to write a readonly database (1544)

To Reproduce Steps to reproduce the behavior:

1. start the given docker-compose file
2. In grafana add the data-source
   • 
3. Add some data to pocket-base UI
   • http://localhost:8090/_
4. Create a new dashboard and query
   • SELECT * FROM [table]
5. after some time (2/3 min) and multiples query attempt the error should apear.

Screenshots

Versions:

• Run via docker?
  • Yes
• Browser [e.g. chrome, safari]
  • Chrome
• Grafana Version [e.g. 7.3.3]
  • 10.3.3
• Plugin Version [e.g. 0.2.1]
  • 3.4.0
• Plugin installed via grafana-cli or manually?
  • done by docker-compose ENV VAR

Additional context

Here is my docker-compose file

services:
  pb:
    build: ./pb
    volumes:
     - db-data:/pb/pb_data
    ports:
      - "8090:8080"
    working_dir: /app/pb

  grafana:
    image: grafana/grafana-oss
    environment:
      GF_INSTALL_PLUGINS: frser-sqlite-datasource
    ports:
     - "3000:3000"
    volumes:
     - grafana-storage:/var/lib/grafana
     - db-data:/data_from_pb_service

volumes:
  db-data:
  grafana-storage:

I hope it's clear. If you have any questions, feel free to ask!

[fr-ser]

Could you let me know in which journal mode your database is running? You can find this out by running this command inside your database (can be done from Grafana):

PRAGMA journal_mode;

I am currently investigating the issue and I am wondering if this might be relevant: https://www.sqlite.org/wal.html#read_only_databases

[fr-ser]

And sadly your "steps to reproduce" only work on your machine I think.

Could you provide me a debug level grafana log file when the error occurs?

[drazicmartin]

Thanks for your anser !

• The command PRAGMA journal_mode; return wal
• I did success to reproduce the steps on both linux and windows system, so i think it is fairly reproductible.

The error only came accross if i'm edditing the SQL query, if i'm only viewing the dashboard and not editing it, the error never came. Also, I found that it auto fix when a new entry is added to the database, but then it came back if I futher edit.

some logs when the error appear :

2024-03-18 21:39:13 logger=context userId=1 orgId=1 uname=admin t=2024-03-18T20:39:13.788060844Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=172.20.0.1 time_ms=44 duration=44.607735ms size=116 referer="http://localhost:3001/d/a67e2671-da10-42e4-b293-61d6c7b80fd4/main-dashboard?editPanel=2&orgId=1" handler=/api/ds/query
2024-03-18 21:39:14 logger=accesscontrol.service t=2024-03-18T20:39:14.635974739Z level=debug msg="Using cached permissions" key=rbac-permissions-1-user-1
2024-03-18 21:39:14 logger=query_data t=2024-03-18T20:39:14.686296537Z level=debug msg="Processing metrics query" query="unsupported value type"
2024-03-18 21:39:14 logger=secrets.kvstore t=2024-03-18T20:39:14.68636007Z level=debug msg="got secret value from cache" orgId=1 type=datasource namespace=pb-database
2024-03-18 21:39:14 logger=plugin.frser-sqlite-datasource t=2024-03-18T20:39:14.686962583Z level=debug msg="Received request for data"
2024-03-18 21:39:14 logger=plugin.frser-sqlite-datasource t=2024-03-18T20:39:14.686989364Z level=debug msg="Variables replaced"
2024-03-18 21:39:14 logger=plugin.frser-sqlite-datasource t=2024-03-18T20:39:14.687067034Z level=debug msg="Macros applied"
2024-03-18 21:39:14 logger=plugin.frser-sqlite-datasource t=2024-03-18T20:39:14.687440766Z level=error msg="Could not execute query" err="attempt to write a readonly database (1544)" query="SELECT * FROM records\r\n"
2024-03-18 21:39:14 logger=plugin.frser-sqlite-datasource t=2024-03-18T20:39:14.687528182Z level=debug msg="Finished query" refID=A
2024-03-18 21:39:14 logger=context userId=1 orgId=1 uname=admin t=2024-03-18T20:39:14.687694291Z level=info msg="Request Completed" method=POST path=/api/ds/query status=400 remote_addr=172.20.0.1 time_ms=52 duration=52.760945ms size=116 referer="http://localhost:3001/d/a67e2671-da10-42e4-b293-61d6c7b80fd4/main-dashboard?editPanel=2&orgId=1" handler=/api/ds/query

I also investigated the version that pocketabse is using for SQLite is 3.45.1 which is almost the latest version and far from version 3.22.0 that is mentioned in the link you provided.

[fr-ser]

The command PRAGMA journal_mode; return wal

According to the SQLite docs this is not a good setup for a read only file system. See link: https://www.sqlite.org/wal.html#read_only_databases