If they are NFS mounted, is the database user able to write to the mounted disk?
Sounds window’ish, but I’ll try
(it’s not a mounted NFS)
Well, you could got through the pain of figuring out what process has the lock on the file and restarting the process, if one exists. Or you can save all that work and just reboot.
There is a known problem with NFS mounts where file locks end up being flakey.
Perhaps on Linux, but I am an old Unix (Sun Solaris) admin.
There are bad/flakey impelmentations of NFS on some Linux and the BSDs too. Since Solaris is dead…
I actually helped (up to now), but I will keep an eye on it.
It just happend again:
2019-09-21 08:33:25.528 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!! java.sql.SQLException: [SQLITE_BUSY] The database file is locked (database is locked) Query: SELECT time, value FROM item0539 WHERE TIME>'2019-09-19 08:33:19' AND TIME<'2019-09-21 08:33:22' ORDER BY time ASC Parameters: 
The db is big, but ok (I guess):
drwxrwxr-x+ 5 openhab openhab 4,0K Sep 21 08:43 . drwxrwxr-x+ 19 openhab openhab 4,0K Sep 12 14:08 .. drwxrwxr-x+ 2 openhab openhab 4,0K Sep 12 14:02 db4o drwxrwxr-x+ 2 openhab openhab 4,0K Sep 12 14:02 mapdb drwxrwxr-x+ 2 openhab openhab 4,0K Sep 12 14:02 rrd4j -rw-rw-r-- 1 openhab openhab 2,7G Sep 21 08:43 sqlite-jdbc.db
I renamed the sqlite-jdbc.db and restarted OH.
After OH created a new DB I still get the error:
2019-09-24 13:31:24.655 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!! java.sql.SQLException: [SQLITE_BUSY] The database file is locked (database is locked) Query: SELECT time, value FROM item0504 WHERE TIME>'2019-09-24 12:46:21' ORDER BY time ASC Parameters: 
I’m not surprised that forcing it to recreate the database file didn’t work. I don’t think it is a problem with the file itself. It is a problem either internal to SQLite or with the file system. At this point my only suggestion is to file an issue. I’m out of idea.
I don’t know yet how to file an issue, but I will do it after I checked other things.
Item0504 (error above) is my item “System_started” (in the freshly generated DB)
So nothing obvious.
SQLITE.org states (https://www.sqlite.org/rescode.html#busy):
The SQLITE_BUSY result code indicates that the database file could not be written (or in some cases read) because of concurrent activity by some other database connection, usually a database connection in a separate process.
I wonder whicih other process except oh will access my sqlite DB!?
That’s the root of the problem. There wouldn’t be. That is why I asked about NFS and focused on the file system more so than the SQLite itself.
That does raise something. There actually might be a 2GB limit here. Raspbian is 64-bits I’m pretty sure but your JDK is probably 32-bit as that’s the recommendation as the 64-bit version of Java has problems with serial devices on RPi. And typically 32-bit software cannot handle file sizes over 2GB.
But that doesn’t explain the problem continuing to occur after you reinitialized the DB by removing the old file and having it create a new one.
See How to file an Issue. The repo where the persistence stuff lives is https://github.com/openhab/openhab1-addons/tree/master/bundles/persistence.
Though be prepared to not get satisfaction as SQLite was dropped as officially supported by OH a long time ago.
Thanks again, Rich.
I wouldn’t mind to switch to another DB.
However I would like to understand the root cause, so I will spend some more time for my investigation.
In the meantime I will also check out the various alternative persistence approaches here.
I use MapDB for restore on startup and InfluxDB for history. I’m pretty happy with that combo. I think rrd4j is more popular than InfluxDB over all though.
I don’t have high expectations on the persistence.
Storage for up to a year, stability and the option to create graphs.
(for restore on startup I also use mapDB an am very happy with it.)
I did some more test and now it’s gone.
What I did was:
clean cache & temp (again).
increased time for copying back the rules after the “system started” signal.
Maybe the persistence was not yet ready?!
Raspian is 32 bits.
It still happens…
2019-11-08 19:00:03.139 [ERROR] [org.knowm.yank.Yank ] - Error in SQL query!!!
java.sql.SQLException: [SQLITE_BUSY] The database file is locked (database is locked) Query: SELECT time, value FROM item0867 WHERE TIME>‘2019-11-08 00:00:00’ ORDER BY time ASC Parameters: 
I guess I need to consider another persistence then.
By the way: Is there any option to migrate SQlite data from my existing DB to InfluxDB?
Search the forum. Someone published a script to migrate from rrd4j to InfluxDB using the OH REST API to pull the data out of rrd4j that should work for any active OH persistence, perhaps with only very minor modifications.
I found it!
Thanks for the hint.