Since two days, I’ve a very confusing problem with my SQLite database.
What I have:
A “normal” SQLite Database on a webserver, which is used by one webapp. The DB and the app is running for 1.5 years without any problems. It runs in the WAL-mode.
On monday morning the issues occured first time. The Database was broken and the app wasn’t able to select or execute the querys. So I downloaded the database and tried to open it in my SQLiteManager, but I can’t. After searching the internet I found a workaround over the terminal-command
echo .dump | sqlite3 kaputt.sqlite | sqlite3 neu.sqlite. After this, the DB was repaired, but a lot of changes were lost.
After uploading the DB to the app, it was running correctly. But at ca. 7 PM, when no user was using the app, the DB crashes again.
So yesterday I started to make hourly backups. Over the day the app and the DB were running fine, but in the evening the DB crashed again. So I had to rebuild the crashing DB again today morning.
Also weird is, that some changes from some users done, were submitted to the DB, but the changes are lost today. Only a view DB-inserts or -updates. If I look into my hourly backups the changes are also not there. So the changes were made for the users themselves es but are not submitted correctly to the database.
Last night I held a session to the app. This morning, when I checked the app I had following situation:
The open connection to app worked fine. I was able to select and execute some data.
But I was not able to open a new connection. Everytime I did some SQL-selects I got NilObjectException, because of the crashed DB. Again: the open connection was fine, only new sessions chrashed.
With the open connection I executed the VACUUM command, which rebuilds the DB. After this also new connections worked. But: some changes of yesterday were lost.
In short form:
- SQLite DB chrashes over night
- Open session can interact with the crashed DB, new sessions not
- Vacuum solves the problem
- Data got lost, because they are not really written to the DB. The submitting users can select their changes until the DB crashes. The changes are not in the backuped DBs.
- I had no updates on the DB or my app in the last few days.
My english is not best, but I hope you understand what I try to say.