3 ms·

Every once in a while I have sqlite eat it, and end up with a corrupt db file. About as often as mysql really. I wish I had some more serious automatic recove

Every once in a while I have sqlite eat it, and end up with a corrupt db file. About as often as mysql really. I wish I had some more serious automatic recovery in sqlite. I have to have a boot script check the db, export everything to an sql file if the db is corrupt then create a new file.

dijit 9y ago
That does not mirror my experience, I hate to be the guy suggesting this but have you considered the fact that you're using it wrong? What I mean by that is that SQLite really is a replacement for open() and as such is not multi-thread compatible. If you're doing a lot of work that's corrupting you can also turn up the level of binary logging until recovery becomes easy enough. I'm using SQLite effectively on a few thousand multi-threaded windows machines and I've encountered corruption exactly once- which was when a hardware raid had degraded non-gracefully.
- karmicthreat 9y ago
  Oh I guarantee I am doing something wrong. This is running on some crappy sandisk sd cards as well. So it is entirely possible that the corruption I run into is just an issue with the sd card.
- jpeeler 9y ago
  My understanding is that using SQLite is completely fine in a multithreaded architecture, but you have to ensure that the simultaneous database operations match the correct threading mode: https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html I also believe that the common way of gaining performance while protecting safety is to use multi-thread mode with connection pooling. The idea being that your application can use different database connections to achieve multiple writes without relying on the overhead of additional locking required within SQLite itself for serialized mode. Would be interesting if anybody has performance metrics that backs up what I said with data converging on an optimum number of database connections.
  - dijit 9y ago
    that's exactly the case; make sure only one thread writes, but how you feed data to that thread is up to you.

dijit 9y ago

That does not mirror my experience, I hate to be the guy suggesting this but have you considered the fact that you're using it wrong? What I mean by that is that SQLite really is a replacement for open() and as such is not multi-thread compatible. If you're doing a lot of work that's corrupting you can also turn up the level of binary logging until recovery becomes easy enough. I'm using SQLite effectively on a few thousand multi-threaded windows machines and I've encountered corruption exactly once- which was when a hardware raid had degraded non-gracefully.

karmicthreat 9y ago
Oh I guarantee I am doing something wrong. This is running on some crappy sandisk sd cards as well. So it is entirely possible that the corruption I run into is just an issue with the sd card.
jpeeler 9y ago
My understanding is that using SQLite is completely fine in a multithreaded architecture, but you have to ensure that the simultaneous database operations match the correct threading mode: https://sqlite.org/threadsafe.html https://sqlite.org/threadsafe.html I also believe that the common way of gaining performance while protecting safety is to use multi-thread mode with connection pooling. The idea being that your application can use different database connections to achieve multiple writes without relying on the overhead of additional locking required within SQLite itself for serialized mode. Would be interesting if anybody has performance metrics that backs up what I said with data converging on an optimum number of database connections.
- dijit 9y ago
  that's exactly the case; make sure only one thread writes, but how you feed data to that thread is up to you.