Please handle SQLITE_BUSY gracefully

[mvysny]

Describe the bug
When the sqlite database is being written into by another process (a logger appending values to a table), it is possible to get blank graphs in Grafana, with an error that says "SQLITE_BUSY 5"

Workaround, which seems to be working for now, is to add PRAGMA busy_timeout before every select, e.g.

PRAGMA busy_timeout = 1000; SELECT DateTime, BatteryVoltage, ChargingCurrentToBattery, ControllerTemp from log
WHERE DateTime >= $__from / 1000 and DateTime <= $__to / 1000

To Reproduce
Hard to reproduce since this bug occurs randomly by nature. Try to have a background process which frequently writes to the sqlite database; then try to add 5+ charts to grafana so that sqlite is polled frequently.

Screenshots
Sorry - can't reproduce the issue at the moment :-D

Versions (please complete the following information):

• OS: Ubuntu 22.04 arm64 on Raspberry PI 3, Linux rpi 5.15.0-1014-raspi 16-Ubuntu SMP PREEMPT Thu Aug 25 09:50:55 UTC 2022 aarch64 aarch64 aarch64 GNU/Linux
• Run via docker? no
• Browser: Firefox
• Grafana Version 9.1.5
• Plugin Version 3.1.0
• Plugin installed via grafana-cli or manually? via Grafana ui /plugins webpage.

Additional context
Grafana log:

logger=plugin.frser-sqlite-datasource t=2022-09-20T08:35:13.451378354+03:00 level=error msg="Could not execute query" err="database is locked (5) (SQLITE_BUSY)" query="SELECT DateTime, SolarPanelVoltage, SolarPanelCurrent from log\nWHERE DateTime >= 1663650313397 / 1000 and DateTime <= 1663652113397 / 1000"
logger=plugin.frser-sqlite-datasource t=2022-09-20T08:35:13.451928147+03:00 level=error msg="Could not execute query" err="database is locked (5) (SQLITE_BUSY)" query="SELECT DateTime, SolarPanelCurrent from log ORDER BY DateTime DESC LIMIT 1"
logger=plugin.frser-sqlite-datasource t=2022-09-20T08:35:23.433018116+03:00 level=error msg="Could not execute query" err="database is locked (5) (SQLITE_BUSY)" query="SELECT DateTime, SolarPanelPower from log\nWHERE DateTime >= 1663650323399 / 1000 and DateTime <= 1663652123399 / 1000"

[mvysny]

Captured a screenshot:

[fr-ser]

This seems like a pretty reasonable request. Let me check if this is something that the underlying SQLite library provides or if I need to add some retry logic myself. 👌🏻

[wumpus]

I see this problem, and attempted to enable WAL as a fix. I'm not having much luck. I don't expect you folks to debug my personal setup (I'm using sqlite as a data source in addition to grafana config and I have a writer as well as the grafana reader...) but it would be cool if the documentation at least covered how to use sqlite3 with WAL as a data source. Thanks.

[wumpus]

I've managed to solve my personal problem with sqlite as a data source -- I was trying to use sqlite WAL (write ahead log), but my not-grafana process that was writing to the sqlite db was closing the connection every 10 seconds or so. That caused the WAL to be resolved, and there was no benefit for the "No data" problem. Making a commit every 10 seconds but keeping the connection open for a long time doesn't show any "No data" problems.

I volunteer to make a PR with this documentation.

I still think it's worth having a retry.

[fr-ser]

I volunteer to make a PR with this documentation.

Sounds good. Feel free to open a PR or another issue if you need more details.

[wumpus]

https://github.com/mattn/go-sqlite3#connection-string describes a way to set PRAGMA busy_timeout, has anyone tried it? Configuration -> Data sources -> pick a sqlite data source -> path options _busy_timeout=1000

[wumpus]

... I tried it and it seems that I still get an occasional SQLITE_BUSY.

Correctly using SQLITE WAL was definitely an improvement. Adding the connection-string doesn't seem to have made these rare events happen less often.

[wumpus]

Yeah, with only 10 people viewing my dashboards the SQLITE_BUSY thing happens every few minutes, despite my try with a connection string. You might recognize the project I'm working on:

[wumpus]

No joy with a "connect string" -- I set it to _busy_timeout=100 and when I open up an Explore window in Grafana and type in the query PRAGMA busy_timeout; I get 0 as the return value. I even restarted the grafana server service.

[navpreet-securitas]

same is happening for me. i have 2 panels in one grafana dashboard , both connecting to same Sqlite datasource.
datasource is added in readonly mode. for one panel query succeeds and data is visible and for 2nd panel in same dashboard it gives "database is locked". Upon browser page refresh , randomly one panel loads and other shows same error.

probably something related to connection establishment when loading multiple panels at same time.
https://stackoverflow.com/questions/17115398/database-locked-in-wal-mode-with-only-readers

This problem goes away when database is not in WAL mode and grafana reads db in ro mode .