Weird SQLite problems

Databases
1

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.

The problems:
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.

[b]Thanks

Lars[/b]

2

PS:
the crashed DB had no “.sqlite-shm”-File.

3

maybe something changed?
like some app comes at night and blocks writes causes inconsistencies?

4

do you have one SQLite Object for all sessions? or one per session?
Do you close those when no longer needed?

5

There is no other app and i’ve got one SQLite -object per session.

I think the sessions are closed if the browser is closed?!

6

yes, they are. This sounds strange especially as the app run so long without problem.
Something changed. You switched Xojo version?

7

Nope, no switching, no updates, no changes.

I am at the end of my knowledge.

8

Is it possible, that the permissions are the source of the problem? It could be that I switched them to 755, not 777?!

9

If not a permission issue, are you very sure no suspicious code like a virus is running on your server since the problems started ? Good idea to set up a new OS and everything from scratch and test with that one.

10

The hosting providers backup of your server ?

11

Our hosting provider says thats impossible :smiley:

The provider does backup the server every day at 1 AM.

Today morning:
The DB is okay, so the issues of the last days are hopefully solved. I think it was the permission 755 on the DB.

But:
Yesterday we lost some Data again. The users do print some papers to prove what changes they have made over the day. The papers are filled from within the DB. So if the changes are written on the paper, the data must be in the DB.

Unfortunately the lost data aren’t in any backup (I do backup the DB hourly).

How could that be?

12

Have you investigated a snapshot of the SQLite database using a good database-management tool ?
Strange that the user prints data which the backup not contains. Just compare database before backup and after.

13

How do you perform your hourly backup?
Could it be that some users try to write to it at the same time and it’s locked?
Just thinking out loud here… :stuck_out_tongue:

14

Did you access the database over the network? SQLite with WAL turned on doesn’t allow that.

15

I’m not sure what you mean.

I just left my PC and my web browser online, to determine whether the connection between browser and app is still active in the morning.

16

[quote=209925:@Albin Kiland]How do you perform your hourly backup?
Could it be that some users try to write to it at the same time and it’s locked?[/quote]
Thanks for thinking loud! :slight_smile:

I just download the three DB-Files over Ajaxplorer.

17

I think Eliott is talking about storing the SQLite database on a shared volume, accessing it over a network. That is not a good idea.

18

Oh ahh,
no, it’s stored on a simple ubuntu webserver. The app is available over the internet, not over the local network.

19

I’m not VERY familiar with SQLite but how many concurrent users do you have?
Could it be multiple Writes going on a the same time corrupting things?

I would change to PostgreSQL instead but that does not solve your imminent issue :wink:

20

From Write-Ahead Logging:

[quote]All processes using a database must be on the same host computer; WAL does not work over a network filesystem.
[/quote]