Was working on a test app tonight and got a malformed SQLite image when running some code that produces an Out Of Bounds Exception. SQLiteDoctor (from SQLabs) reported the file’s problem as a corrupted MaxRowID and fixed it. The table with the MaxRowID problem is the UserLogs table, where user activity and errors like this are recorded. That record was not created, but a subsequent test on a backup file did successfully produce a record in the UserLogs table that showed the Out Of Bounds Exception, without corrupting the database.
Is this not entirely surprising? That a Runtime Out Of Bounds Exception can result in a Malformed SQLite Image?
According to Marco,
Doesn’t it seem odd that this would happen during an Out Of Bounds Exception? I wonder if those two events are just coincidental.
I would think this is random coincidence
SQLIte is quite robust & quite hard to corrupt so I’m really curious how the heck you managed to do this ?
[quote=429890:@Norman Palardy]I would think this is random coincidence
SQLIte is quite robust & quite hard to corrupt so I’m really curious how the heck you managed to do this ?[/quote]
Wish I knew. Maybe a temporary hardware glitch at the server?
Could this happen if I had a sudden temporary loss of available memory on the server during temporary shortlived heavy usage?
Why would that happen? That is what paging and swapping is supposed to prevent.
I have no idea. I’m just pulling things out of the air. I do see people on the net talking about this happening from time to time, and that we should expect it to eventually happen and know how to deal with it when it does. SQLiteDoctor certainly fixed it easily, but I restored from a good backup instead of relying on that.
The only way I’ve ever seen to corrupt one is two separate threads trying to share one connection to the db
Hmmm…I do have a WebTimer checking for data in that sqlite file, and manipulating the UI based on what it finds. Does that work in a different thread than the UI?
Are you using a single connection for that? Have you looked at:
https://www.sqlite.org/howtocorrupt.html
There is only one connection to the sqlite file that corrupted (in a given session). If the WebTimer wants to access any sqlite file in the same session, it uses the same connection the UI uses. And now that I look at it, the sqlite file that corrupted was not the one being accessed by the WebTimer. The UI was attempting to insert a record into it that sqlite file, where that record would record an Out Of Bounds Exception. The WebTimer never attempts to connect to that particular sqlite file.
Am I safe in assuming that if a WebTimer uses the same session connection to a given sqlite file, I’m okay?
Of course, Norman suggests that having more than on thread access the sqlite file in the same connection can be a problem. Is a WebTimer working on a different thread than the UI?
Assuming my understanding of the online docs is correct, WebTimers work in the Main Thread, so it’s not in a separate thread. And there is never more than a single connection to any sqlite file in a given session. Doesn’t this alleviate the concerns of both Norman and Tim?
And I do now see that the WebTimer involved here could have caused an attempted write to the sqlite file that corrupted, if there was a DB error when it attempted to read a different sqlite file (that WebTimer’s Action includes reading data in a different sqlite file). Such DB errors are recorded in the sqlite file that corrupted, and that WebTimer’s Action would have attempted to do that if there was a DB error.
Unless the app is entirely single threaded EVERY thread of execution should have its own connection
I’m guessing this is where your db went corrupt by sharing one db connection
The only time I ever create a Thread in this entire Web app is to update a Progress Bar in one particular WebDialog. And that wasn’t running at that time.
Every session should have its own connection
thats the only things I can think of
shared connections do cause issues on most db’s not just sqlite
Thanks for your patience on this, Norman. I also sent a query off to Marco see what he thinks could happen. I’m sure he sees a lot of corrupt sqlite files.
Personally I’d hope he sees very few 