4 ms·

Meh, like I said, big fan of SQLite. But any use of it in a "serious" application will undoubtably run into: Error: database disk image is malformed On a fairl

by mSparks 10y ago

Meh, like I said, big fan of SQLite. But any use of it in a "serious" application will undoubtably run into: Error: database disk image is malformed On a fairly regular basis. Google gives:About 57,400 results So I'm definitely not the only one.

TickleSteve 10y ago
http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption (Just so you know)
- olavgg 10y ago
  This is so true, some consumer SSD's are so evil that they do not even honor ATA_CMD_FLUSH. Bad things will happen then.
- mSparks 10y ago
  Yeah. There's that. But there are also numerous memory leaks that have to be carefully managed. Opening and closing the database regularly is usually enough. And if you do manage to let it run long enough to use all the system ram, that will malform the database to. Transactions are particularly bad. One bug I ran into earlier in the year would gobble up 12gb of system ram trying to search and insert a few 10s of mb of text, just because it was leaving prepared statements laying around without telling me.
  - TickleSteve 10y ago
    Sounds like a pretty serious bug that would probably affect millions of people.... ... thanks for reporting it. (https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html) "No memory leaks. The application is responsible for destroying any objects it allocates." "Robust against allocation failures. If a memory allocation ever fails (that is to say, if malloc() or realloc() ever return NULL) then SQLite will recover gracefully."
    - mSparks 10y ago
      Fine. But in practice. By time malloc returns null. It's blown up anything on the system that doesn't recover gracefully. possibly the file system. Which is possibly how the database becomes corrupt. And I see plenty of reported examples and work arounds. Even yum has been suffering from it for years. http://forums.fedoraforum.org/showthread.php?t=260195 http://forums.fedoraforum.org/showthread.php?t=260195 http://www.pvangsgaard.com/2016/03/16/yum-error-database-disk-image-is-malformed/ http://www.pvangsgaard.com/2016/03/16/yum-error-database-dis...
      - TickleSteve 10y ago
        The number of applications using sqlite successfully is very much greater than 1. You need substantially more evidence than that to state that what appears to be simple user error is actually a design flaw in software that tests specifically for the examples you state and is also well known for the quality of that testing.
        mSparks 10y ago
        I use it very successfully. In combination with sqlcipher and an application layer that ensures everything happens "just so" All I said is the databases are temperamental. If you do anything "unexpected" it breaks. Numerous ways and often. Most of the bugs I've seen would be easily picked up with non deterministic testing. (Leaking result sets and prepared statements for example) - which is how I found them. The testing codebase can only be that huge if they are all deterministic tests. Trouble with deterministic tests is they miss all the edge cases and bugs persist for a long long time. The transaction stuff is particularly bad. Gobbles memory without a care in the world till it either runs out and performance drops to near zero or fails and corrupts the database. So everyone is forced to break transactions they would prefer to be atomic into multiple smaller ones. These aren't things that should need a specific test case making in c, and many of the problems are horrific optimisation problems rather than show stoppers. My point is. For all those kloc of testing, the sqlite code is definitely not what I would call robust. How long has sqlite been reporting "out of memory" just because a database failed to open for some reason? The biggest problem seems to be right here: OOM testing is accomplished by simulating OOM errors. I.e. not simulated in a real way. Which is silly when it is so easy to generate them.

by mSparks 10y ago

TickleSteve 10y ago

http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/lockingv3.html#how_to_corrupt http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption (Just so you know)

olavgg 10y ago
This is so true, some consumer SSD's are so evil that they do not even honor ATA_CMD_FLUSH. Bad things will happen then.
mSparks 10y ago
Yeah. There's that. But there are also numerous memory leaks that have to be carefully managed. Opening and closing the database regularly is usually enough. And if you do manage to let it run long enough to use all the system ram, that will malform the database to. Transactions are particularly bad. One bug I ran into earlier in the year would gobble up 12gb of system ram trying to search and insert a few 10s of mb of text, just because it was leaving prepared statements laying around without telling me.
- TickleSteve 10y ago
  Sounds like a pretty serious bug that would probably affect millions of people.... ... thanks for reporting it. (https://www.sqlite.org/malloc.html https://www.sqlite.org/malloc.html) "No memory leaks. The application is responsible for destroying any objects it allocates." "Robust against allocation failures. If a memory allocation ever fails (that is to say, if malloc() or realloc() ever return NULL) then SQLite will recover gracefully."
  - mSparks 10y ago
    Fine. But in practice. By time malloc returns null. It's blown up anything on the system that doesn't recover gracefully. possibly the file system. Which is possibly how the database becomes corrupt. And I see plenty of reported examples and work arounds. Even yum has been suffering from it for years. http://forums.fedoraforum.org/showthread.php?t=260195 http://forums.fedoraforum.org/showthread.php?t=260195 http://www.pvangsgaard.com/2016/03/16/yum-error-database-disk-image-is-malformed/ http://www.pvangsgaard.com/2016/03/16/yum-error-database-dis...
    - TickleSteve 10y ago
      The number of applications using sqlite successfully is very much greater than 1. You need substantially more evidence than that to state that what appears to be simple user error is actually a design flaw in software that tests specifically for the examples you state and is also well known for the quality of that testing.
      - mSparks 10y ago
        I use it very successfully. In combination with sqlcipher and an application layer that ensures everything happens "just so" All I said is the databases are temperamental. If you do anything "unexpected" it breaks. Numerous ways and often. Most of the bugs I've seen would be easily picked up with non deterministic testing. (Leaking result sets and prepared statements for example) - which is how I found them. The testing codebase can only be that huge if they are all deterministic tests. Trouble with deterministic tests is they miss all the edge cases and bugs persist for a long long time. The transaction stuff is particularly bad. Gobbles memory without a care in the world till it either runs out and performance drops to near zero or fails and corrupts the database. So everyone is forced to break transactions they would prefer to be atomic into multiple smaller ones. These aren't things that should need a specific test case making in c, and many of the problems are horrific optimisation problems rather than show stoppers. My point is. For all those kloc of testing, the sqlite code is definitely not what I would call robust. How long has sqlite been reporting "out of memory" just because a database failed to open for some reason? The biggest problem seems to be right here: OOM testing is accomplished by simulating OOM errors. I.e. not simulated in a real way. Which is silly when it is so easy to generate them.