Weird SQLite problems

Using WAL you should have two extra files where your database is located, *.sqlite-wal and *.sqlite-shm.
You got those? Backing em’ too up?

-> Because the users connect to .cgi they use one host computer (alias the webserver), I think.

-> I don’t use a network filesystem. I use a simple webserver like the xojocloud.

-> Yes, but I think SQLite can handle it?! Xojo Staff said this several times here on the forum.

[quote=209956:@Albin Kiland]*.sqlite-wal and *.sqlite-shm.
You got those? Backing em’ too up?[/quote]
Sure!

That’s correct for the users of the app, but did you access the file over the network from your work station?

Ahhh, now I got it :smiley:

No, I also only access over the app to the DB. I do only use the FTP to update the App and to download the DB for backup.

I apologize in advance for pointing the obvious,
but having something working fine for a long time and then suddenly stopping working…
just because it happened to me, to have a db stop working on a customer server just because their server runned out of disk space,
maybe it could be your case too?
or maybe some kind of limit on database size?
vacuum solving temporarily the problem could be a clue of this kind of problem

 Giulio

Something that occurs to me… Are you using Transactions anywhere? If so, is it possible one of your routines is missing a Commit call? That might explain your missing data.

I do execute the SQL-commands direct to the DB using querys. So, I think, no, I don’t use transactions, right?

I’ve a SqliteDatabase-Object called ‘DB’ in my app and I always use DB.SQLExecute("Query")

Unfortunately not. The DB is only about 8 MB large.

VACUUM rebuilds the DB complete while creating a temporary DB with the dump of the old one.

Right.

Another thought… When your provider does backups at night… How long does that take? It occurs to me that if you have users using the app and the backup routine comes along and locks the storage device or even just the file for an extended period of time, you might get this effect. The database or WAL file can’t be written and then you get an error and exception in your app, bringing it down.

Normally the app is used on working days between 6:30 AM and 8 PM. But it is possible, that a user don’t turn off his/her PC over night, whereby the app is running, because there is an open session.

But over the weekend I’m sure,are all PCs offline. The first lost datas occurs at monday morning.

Today It seems, as that the system has stabilised. Yesterday I added a second DB, where I write all SQL-querys in, to be able to catch up all changes. It is not the best way, but so I’m more prepared for lossy data.