Therr have been a couple (literally) of occasions where customers have contacted me and it turned out their database was corrupt, though I don't know the circumstances under which it occurred. Presumably there are instances where it wasn't reported to me too.
Coincidentally, the backups had stopped working a couple of months ago.
Fortunately I was able to copy the data to my machine, write some python to try to retrieve each customer's data individually, verify its consistency and merge with the older backup so most people didn't notice.
Afterwards I upgraded to the latest sqlite, as the one I had been using was six years old, and I have not had a problem since.
Whatever DB you use, a backup plan is prudent for it. No DB can magically recover from some of corruption. And unless the user does something stupid, out of ignorance, SQLite is extremely resilient. Do you have any experience with it that makes you suspect otherwise?
I was under the impression that it’s very difficult to corrupt a DB file if you are using the SQLite API
You should probably keep each connection owned by a single thread.
This is a general “do not share memory” issue, you could fork any non-thread safe C code and see undefined behaviour.
Any other issues?
But only on sqlite, the "undefined behavior" means "all your data is gone". In postgres, you can crash or fail or get invalid results, but you are not going to lose all your data at once.
I think "do not share memory with multiple writers" is in the same category as "do not use a default user/pass, only allow access from the LAN".
Both are programmer errors not related to the specific products they are implemented on top of.
That said, the "do not share" issue is not important for everyone. My Python code never calls fork, so that's not an issue at all. But I can easily imagine programs which do fork a lot.
Using the Rust example, you could say “Rust is not safe because I can wrap my code in unsafe{} and that corrupts my data when I fork”.
The OPs point is “when I run two threads that write to the same memory I get corrupt data”.
The first point of call is not “well it’s Samsung memory, so Samsung make terrible memory modules, I shall run my incorrect programs on Sony henceforth”.
It’s “why are you expecting your incorrect program to even work”.
If you replace "Samsung" with Unix and "Sony" with Microsoft your other statements are correct. That's the problem.
So in order to avoid SQLite database corruption, you need:
- hardware RAID disabled or reconfigured in JBOD ("IT") mode;
- RAID controller write cache disabled;
- RAID battery back-up cache disabled;
- individual drives' write caches disabled;
- ZFS;
- if using GNU/Linux, OOM turned off.
Even with turning off OOM, GNU/Linux's fsync() will still lie about I/O having completed, when it is in fact in transit. Therefore, if you want a reliable database, you must switch to a real UNIX, like SmartOS.
Only when all of these are done exactly as I have specified will you have a system ready for a relational database management system, and only then will a database be able to actually provide transactions.