Cached Query Results - SQLite database not updating in Grafana

[ssnkhan]

Describe the bug Queries executed via Grafana with a SQLite data source appear cached / incorrect.

To Reproduce Steps to reproduce the behavior:

1. Run query in Grafana (yields incorrect results, see below).
2. Run same query on a local copy of the DB, or on the remote instance (where Grafana is running) using sqlite3 which gives correct result.

Screenshots Local:

Remote:

Versions (please complete the following information):

• OS: Ubuntu
• Run via docker? No
• Browser: Safari
• Grafana Version: v8.4.3 (b7d2911cac)
• Plugin Version 3.0.0
• Plugin installed via grafana-cli or manually? grafana-cli

Additional context grafana.ini updated to allow reading of database from within the home folder. Additionally, permissions set via chmod -R a+rwX ./sqlite.db. 100% sure running the same DB locally and remotely as SHA1 hash identical.

Really appreciate your help, thanks!

[fr-ser]

Maybe you could describe your setup (and how your SQLite database is created) a bit more. Are you not using docker anywhere? Is the database file copied / linked from another place? Is the database file mounted / synced through some network drive or service?

I can't reproduce the issue on my machine normally, but under one condition I also saw "caching".

When I run Grafana in a docker container and mount the database file from my filesystem I saw this behavior. When I updated my database on my machine and keep the connection open it was already showing new values in another terminal on my machine. In Grafana (and a terminal SQLite client inside the docker container) I still saw the old values. After closing the connection on my machine the file in the docker container was updated and Grafana showed the value. I assume this is some (intended) behavior of the docker volume mounting.

[ssnkhan]

The database was created using a CLI tool - it runs on a bare metal VPS without docker. Worth noting that I recently migrated from one host to another. To do so I downloaded the .db file with scp, before then uploading it to the new host also using scp. The cryptographic hashes match too. Any help appreciated -- I've tried a few things (reboot, etc), to no avail.

[fr-ser]

And Grafana is running on the same machine as the only existing database file as well?

In my docker example I could open a terminal SQLite client on the machine where Grafana was running and this terminal client showed the same "caching" behavior as Grafana (indicating it was not a Grafana/plugin issue).

[ssnkhan]

Exactly - that's what's so confusing about this. If I download the same DB locally, or run sqlite3 on the host and paste the same query, I get the correct results. No amount of Grafana reboots appear to be fixing the issue either ...

[fr-ser]

Are there some changes that go through? Is the behavior different for

• updating a table (e.g. adding or changing a row)
• creating / deleting tables
• deleting the whole database file

Do the Grafana logs show anything out of the ordinary?

To be honest I am just grasping right now as I have no hypothesis for a possible cause.

[ssnkhan]

I haven't checked the Grafana logs, I will do that now and report back.

[ssnkhan]

Checked the logs within /var/log/grafana/ and couldn't see anything obvious. I also ran a sqlite3 database.db "PRAGMA integrity_check" which returned ok. Struggling as to what to do next now -- again computed fresh SHA1s for local and remote copies of the DB, and the remote query via Grafana is incorrect, whilst querying the same DB remotely via the native sqlite3 CLI returns the correct results ...

[fr-ser]

Can you provide something to me to reproduce the issue?

As I do not see such behavior on my machine it is tricky to continue.

Can you provide a dockerized version or the problem or some steps to execute on my machine that would lead to the issue?

You can also try with different versions of the plugin or Grafana. I don't expect this to solve the issue but at least that is something to rule out.

[fr-ser]

I would also be interested to know if this problems occurs for others users as well. This might help to isolate the issue to something plugin or setup related.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[idefixcert]

I see the same problem. I have grafana in a docker container and use a sqlite file that is mounted into the container. I use 3.0.1.

Only a restart of grafana shows the latest values.

in the path options I also tries: mode=ro&cache=shared

[fr-ser]

Have you checked if you encounter the issue mentioned here: https://github.com/fr-ser/grafana-sqlite-datasource/issues/83#issuecomment-1065394549

When I run Grafana in a docker container and mount the database file from my filesystem I saw this behavior. When I updated my database on my machine and keep the connection open it was already showing new values in another terminal on my machine. In Grafana (and a terminal SQLite client inside the docker container) I still saw the old values. After closing the connection on my machine the file in the docker container was updated and Grafana showed the value. I assume this is some (intended) behavior of the docker volume mounting.

If you encounter the situation above this is an issue with docker. You can check by opening a terminal inside your docker container and opening the database with the sqlite CLI. If you still encounter this caching behavior then this is not plugin related.

[simi]

I'm facing the same problem. Grafana is not even querying for data (I don't see any requests to get data). Looking locally in docker container using sqlite3 I can see latest data. I did some snapshots before. I tried to delete them, but there is no difference.

Restart doesn't help. The same behaviour is everywhere SQLite query should be done (including edit panel, query explorer, ...) except data source explorer.

[idefixcert]

The problem is the following: If you use grafana in docker but an other application directly on Mac, then there is no synchronisation. But if you run all applications in docker then it works. I assume you don’t face this problem on Linux but on macOS docker runs on his own vm.

[simi]

@idefixcert I have this problem on Linux.

[fr-ser]

I'm facing the same problem. Grafana is not even querying for data (I don't see any requests to get data).

How do you determine that Grafana is not even querying the data? Where do you look for the requests? If not even a request is made, please open a new issue as this seems different than the previously reported behavior.

[simi]

I'm facing the same problem. Grafana is not even querying for data (I don't see any requests to get data).

How do you determine that Grafana is not even querying the data? Where do you look for the requests? If not even a request is made, please open a new issue as this seems different than the previously reported behavior.

in browser network panel

[fr-ser]

How do you determine that Grafana is not even querying the data? Where do you look for the requests? If not even a request is made, please open a new issue as this seems different than the previously reported behavior.

in browser network panel

Then this looks indeed different. Could you, please, open a new issue with some information about your setup and the Grafana logs as well?

[simi]

How do you determine that Grafana is not even querying the data? Where do you look for the requests? If not even a request is made, please open a new issue as this seems different than the previously reported behavior.

in browser network panel

Then this looks indeed different. Could you, please, open a new issue with some information about your setup and the Grafana logs as well?

OK, I'm Grafana beginner, I'll ensure everything is ok on my side (I can query DB from explorer, seems all good) and open new issue.

[simi]

:information_source: @fr-ser I was able to fix my problem by duplicating my dashboard. The new one is not having the same problem. I did a lot of snapshots in the old one (taking a long time). Maybe something locked the old board somehow.