5 ms·

SQLlite is awesome right up to the point where it corrupts everything and leaves you in an unrecoverable state. At that point you need to have working backups

by yaur 5y ago

SQLlite is awesome right up to the point where it corrupts everything and leaves you in an unrecoverable state. At that point you need to have working backups and a plan to migrate to a real DB server. Actually, if "working backups" is a thing that makes sense for your use case just figure out how to use PostgreSQL now.

justsomeuser 5y ago
Has this happened often? Can you reproduce it? I was under the impression that it’s very difficult to corrupt a DB file if you are using the SQLite API
- yaur 5y ago
  call fork()
  - justsomeuser 5y ago
    The docs are quite specific where you can and cannot move connection pointers over threads. 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?
    - theamk 5y ago
      Mistakes happen. 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.
      - justsomeuser 5y ago
        But you cannot compare products based on the mistakes programmers make. We are all human. Any tool can be misused though. 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.
        theamk 5y ago
        Sure you can! One of the big selling points of Rust, for example, is that handing of programmers' mistakes -- if you look at original announcement [1], the first bullet means "when programmers make mistakes, they do not turn into security or crashing issues". 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. [1] https://lkml.org/lkml/2021/4/14/1023 https://lkml.org/lkml/2021/4/14/1023
        justsomeuser 5y ago
        Ok you can compare products, but you still have to use/compare them without making any obvious mistakes. 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”.
        yaur 5y ago
        Actually fork does not create two threads it creates two processes which should "share memory" only in the "copy on write" sense. There is no undefined behavior implied here. When developers misunderstand the difference between thread and process boundaries (as in the case of the SQLite devs) things ca go to total shit real fast. If you replace "Samsung" with Unix and "Sony" with Microsoft your other statements are correct. That's the problem.
thecodemonkey 5y ago
I've never experienced this within my 10+ years of working with SQLite. I've seen it a fair bunch with MariaDB though.
- GordonS 5y ago
  I've been using SQLite in a desktop product for around 10 years, and I guess there are around 1-2k deployments of it. 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.
Annatar 5y ago
You need to be running the database on a correctly working operating system. Since the database relies on the system calls like fsync() working correctly, the filesystem must also work correctly, the stable storage must have write caching turned off and write requests must not return before they are complete. Also, the operating system must not overcommit memory. 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.
webmobdev 5y ago
> SQLlite is awesome right up to the point where it corrupts everything ... At that point you need to have working backups 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?
smhanov 5y ago
This happened to me. I never did figure out the cause. One day customers of www.websequencediagrams.com started emailing me saying they couldn't access their files. Turns out it was corrupted and would just error when accessing certain records. Also, for mysterious reasons, there was a single open transaction that had been accepting all the data for several days, so I had to be very careful when restarting the app... 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.

by yaur 5y ago

justsomeuser 5y ago

Has this happened often? Can you reproduce it? I was under the impression that it’s very difficult to corrupt a DB file if you are using the SQLite API

yaur 5y ago
call fork()
- justsomeuser 5y ago
  The docs are quite specific where you can and cannot move connection pointers over threads. 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?
  - theamk 5y ago
    Mistakes happen. 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.
    - justsomeuser 5y ago
      But you cannot compare products based on the mistakes programmers make. We are all human. Any tool can be misused though. 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.
      - theamk 5y ago
        Sure you can! One of the big selling points of Rust, for example, is that handing of programmers' mistakes -- if you look at original announcement [1], the first bullet means "when programmers make mistakes, they do not turn into security or crashing issues". 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. [1] https://lkml.org/lkml/2021/4/14/1023 https://lkml.org/lkml/2021/4/14/1023
        justsomeuser 5y ago
        Ok you can compare products, but you still have to use/compare them without making any obvious mistakes. 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”.
        yaur 5y ago
        Actually fork does not create two threads it creates two processes which should "share memory" only in the "copy on write" sense. There is no undefined behavior implied here. When developers misunderstand the difference between thread and process boundaries (as in the case of the SQLite devs) things ca go to total shit real fast. If you replace "Samsung" with Unix and "Sony" with Microsoft your other statements are correct. That's the problem.

thecodemonkey 5y ago

I've never experienced this within my 10+ years of working with SQLite. I've seen it a fair bunch with MariaDB though.

GordonS 5y ago
I've been using SQLite in a desktop product for around 10 years, and I guess there are around 1-2k deployments of it. 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.

Annatar 5y ago

You need to be running the database on a correctly working operating system. Since the database relies on the system calls like fsync() working correctly, the filesystem must also work correctly, the stable storage must have write caching turned off and write requests must not return before they are complete. Also, the operating system must not overcommit memory. 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.

webmobdev 5y ago

> SQLlite is awesome right up to the point where it corrupts everything ... At that point you need to have working backups 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?

smhanov 5y ago

This happened to me. I never did figure out the cause. One day customers of www.websequencediagrams.com started emailing me saying they couldn't access their files. Turns out it was corrupted and would just error when accessing certain records. Also, for mysterious reasons, there was a single open transaction that had been accepting all the data for several days, so I had to be very careful when restarting the app... 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.