Inserting binary data without encoding it should *not* corrupt the database. It might make it difficult to get your data back, but other records in the database should be uneffected and the kinds of errors you were seeing from PRAGMA INTEGRITY_CHECK should not occur.
Ok, thanks.
A stray pointer or a buffer overrun in the application that SQLite links with might corrupt some internal SQLite data structures which could lead to database corruption. Experience suggests that the more likely outcome is a segfault, but corruption is a distinct possiblity.
Yes, that is my thought. I will try to run it under a memory checker (wish I was on linux).
We have not seen any SQLite bugs leading to database corruption in about a year (version 2.8.12) and SQLite has been heavily used since then. This could be some obscure SQLite bug, but that seems unlikely. It might be a bug that is only manifested on your PalmOS port, which is not a standard SQLite distribution.
This is a Win32 app that interfaces to the palm, but is not running on the palm.
Corruption can appear in a database long before you notice it. Until this problem is tracked down, you might consider modifying your code to do a PRAGMA INTEGRITY_CHECK periodically. That will help to find the problem as soon as it occurs.
I do an integrity check at init time when I open the database and again when our app completes. The interesting part on this one is that it did not appear to trip on the completion check, but did on the next run's init. So, that would lead me to believe it is an external issue. If someone was doing a defragment run while a transaction closed, would that cause such an issue?
At this point I am trying to figure out the fastest way to add redundancy so that my db is "backed up" and if I detect a fault the last good one can be restored. Sort of like a top level transaction (I already use transactions to allow multi-threading to work, so cannot add a top level one using the supplied mech). Is there something more high-performance and expedient than copying the db file to a backup after it passes integrity check and restoring if it fails?
thanks
michael
