Re: [sqlite] Python - database disk image is malformed
On Mon, Sep 8, 2014 at 1:56 PM, Andres Rianchowrote: > On Mon, Sep 8, 2014 at 2:27 PM, Roger Binns wrote: > > > > It sounds like what you could use temporary tables and let SQLite do the > > work for you. > > > Hmmm. How about this. Stop turning synchronous off for a month or two > and see if people stop reporting the fault. > > Yep, that's a great idea. > > FWIW, temporary tables *always* operate as if synchronous=off, regardless of the actually synchronous setting -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On Mon, Sep 8, 2014 at 2:37 PM, Roger Binnswrote: > On 08/09/14 03:49, Andres Riancho wrote: >> Off-list some guys contacted me and mentioned APSW [0], another >> wrapper around sqlite for python, and said that it might be worth >> giving it a try. Do you guys believe that a change in wrapper could >> improve my situation? Thanks! > > (Disclosure: I am the APSW author) > > It seems like you are randomly fishing around for things hoping for some > magic. There is no magic. Damn. I really like magic. > When deployed to a non-trivial number of places there will be some > corruption no matter what. Most machines do not run ECC, cosmic rays, > random quality hardware, bad cables etc will cause problems eventually. > > Changing SQLite settings (eg pragma synchronous=off) to deliberately lose > durability is playing with fire. It is only safe if you can prove your code > is (and always will be) bug free. > > APSW is a better wrapper for SQLite. If you use a recent version then you > will also be using a recent version of SQLite which will have more defensive > code in it based on real world experience. > > http://rogerbinns.github.io/apsw/pysqlite.html Thanks for the input. My plan will be to comment the line that sets sync=off and see how the application behaves. If the bug reports drop to zero (or near zero), we'll know that was the reason. If the errors still appear, I might experiment with apsw. > Roger > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Andrés Riancho Project Leader at w3af - http://w3af.org/ Web Application Attack and Audit Framework Twitter: @w3af GPG: 0x93C344F3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On Mon, Sep 8, 2014 at 2:27 PM, Roger Binnswrote: > On 08/09/14 03:29, Andres Riancho wrote: >> In my project we use the database to store data during the process >> life, and then remove it when the process finishes. > > It sounds like what you could use temporary tables and let SQLite do the > work for you. Was completely unaware of that feature, will take a look. >> With this in mind, sync=OFF still looks like something that could >> cause "database malformed" errors? > > The problem with synchronous off is that all your code has to be perfect, My code IS perfect (??) > not to mention the operating system and hardware. While you might be > reasonably convinced right now that interactions between all the parts of > code and database are safe, all it takes is some changes in the future to > invalidate that. > > I strongly recommend not playing with fire. Ok, makes sense. Merging Simon's answer: > Hmmm. How about this. Stop turning synchronous off for a month or two and > see if people stop reporting the fault. Yep, that's a great idea. > Roger > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Andrés Riancho Project Leader at w3af - http://w3af.org/ Web Application Attack and Audit Framework Twitter: @w3af GPG: 0x93C344F3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 08/09/14 03:49, Andres Riancho wrote: > Off-list some guys contacted me and mentioned APSW [0], another > wrapper around sqlite for python, and said that it might be worth > giving it a try. Do you guys believe that a change in wrapper could > improve my situation? Thanks! (Disclosure: I am the APSW author) It seems like you are randomly fishing around for things hoping for some magic. There is no magic. When deployed to a non-trivial number of places there will be some corruption no matter what. Most machines do not run ECC, cosmic rays, random quality hardware, bad cables etc will cause problems eventually. Changing SQLite settings (eg pragma synchronous=off) to deliberately lose durability is playing with fire. It is only safe if you can prove your code is (and always will be) bug free. APSW is a better wrapper for SQLite. If you use a recent version then you will also be using a recent version of SQLite which will have more defensive code in it based on real world experience. http://rogerbinns.github.io/apsw/pysqlite.html Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 8 Sep 2014, at 6:27pm, Roger Binnswrote: > The problem with synchronous off is that all your code has to be perfect, Hmmm. How about this. Stop turning synchronous off for a month or two and see if people stop reporting the fault. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 08/09/14 03:29, Andres Riancho wrote: > In my project we use the database to store data during the process > life, and then remove it when the process finishes. It sounds like what you could use temporary tables and let SQLite do the work for you. > With this in mind, sync=OFF still looks like something that could > cause "database malformed" errors? The problem with synchronous off is that all your code has to be perfect, not to mention the operating system and hardware. While you might be reasonably convinced right now that interactions between all the parts of code and database are safe, all it takes is some changes in the future to invalidate that. I strongly recommend not playing with fire. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 8 Sep 2014, at 11:47am, Andres Rianchowrote: > On Sun, Sep 7, 2014 at 11:39 PM, Simon Slavin wrote: > >> There are two possibilities: >> >> A) The database really is malformed >> B) The client's copy of SQLite is returning that code by mistake. >> >> Can you have one of these clients send you a copy of their database after >> they have received this message ? Then you can try and open it yourself and >> see whether it really is malformed. > > Let's say that it is possible for me to do that (ask users to submit > their DBs and actually get one), I receive it and then: > 1- Open -> Get database malformed error > 2- Open -> Can read the database > > In any of the cases, I can't imagine what to do next. For diagnostic purposes mainly. Just see whether you get (1) or (2). If you get (2) then you know your fault is messing up your program's memory rather than writing gibberish over the database file. It just tells you part of your code to check more carefully. >> All the incidents like this I can remember have been the result of bad >> memory management or using stale pointers. It's likely that you will >> eventually find that some other process is overwriting SQLite's memory or >> writing to the database file. > > Oh, so you believe that maybe if the database is really malformed I'll > find "garbage" that shouldn't be there using a hex editor, and by > understanding what that garbage is (lets say log messages that should > go to stderr) I can fix the issue on my software? That would be an > outcome of #2 above? An outcome of #1. Exactly. Sometimes the thing you find written over your database file tells you what went wrong. For instance, years ago I once found a configuration file corrupted with text including the string "Account slavin logged off.". Since that string didn't get created before a certain point in my program it told me which part of the program was messing up its memory handling. It turned out that I was referring to a pass-by-reference variable as if it was pass-by-value." >> But it's possible you've found a genuine bug in SQLite and we'll try to help. > > Well, this should be really unlikely, right? We hope so. It's tempting to write things like "SQLite has billions of happy installations there are no bugs in it you idiot." but a few obscure bugs are found every year. Nevertheless they tend to be bugs that require an odd or unlikely combination of things to trigger them rather than basic file handling faults. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
Off-list some guys contacted me and mentioned APSW [0], another wrapper around sqlite for python, and said that it might be worth giving it a try. Do you guys believe that a change in wrapper could improve my situation? Thanks! [0] https://github.com/rogerbinns/apsw On Sun, Sep 7, 2014 at 11:11 PM, Andres Rianchowrote: > List, > > I'm using sqlite as the database backend for an open source > project and it works perfectly 99% of the time; however some users > have reported "database disk image is malformed" errors [1][2]. > > At the moment the w3af project has a really clean wrapper around > sqlite [3] which allows many threads to talk with the sqlite database > by: > * Making sure only one SQLiteExecutor thread has an open > connection to the DB > * All the client threads queue the queries and the thread with > the connection runs them > > The DB stores a considerable amount of rows, in a short period of > time, but I don't believe that's an issue. > > I've read through the how to corrupt document [0] and nothing > seemed to match with what I'm doing. So, my questions are: > > * Do you see anything wrong with my wrapper? [3] > * Is it possible to debug "database disk image is malformed" > (at the python level, maybe an attribute of the exception?) to better > understand what is causing the issues? > * I'm setting [4] "PRAGMA synchronous=OFF" for increased > performance. Can this trigger malformed errors? > > Not a sqlite expert... am I missing something big? > > [0] https://www.sqlite.org/howtocorrupt.html > [1] > https://github.com/andresriancho/w3af/search?q=database+disk+image+is+malformed=Issues=%E2%9C%93 > [2] https://github.com/andresriancho/w3af/issues/4905 > [3] > https://github.com/andresriancho/w3af/blob/master/w3af/core/data/db/dbms.py > [4] > https://github.com/andresriancho/w3af/blob/master/w3af/core/data/db/dbms.py#L293 > > Regards, > -- > Andrés Riancho > Project Leader at w3af - http://w3af.org/ > Web Application Attack and Audit Framework > Twitter: @w3af > GPG: 0x93C344F3 -- Andrés Riancho Project Leader at w3af - http://w3af.org/ Web Application Attack and Audit Framework Twitter: @w3af GPG: 0x93C344F3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
Simon, On Sun, Sep 7, 2014 at 11:39 PM, Simon Slavinwrote: > > On 8 Sep 2014, at 3:11am, Andres Riancho wrote: > >>I'm using sqlite as the database backend for an open source >> project and it works perfectly 99% of the time; however some users >> have reported "database disk image is malformed" errors [1][2]. > > There are two possibilities: > > A) The database really is malformed > B) The client's copy of SQLite is returning that code by mistake. > > Can you have one of these clients send you a copy of their database after > they have received this message ? Then you can try and open it yourself and > see whether it really is malformed. Let's say that it is possible for me to do that (ask users to submit their DBs and actually get one), I receive it and then: 1- Open -> Get database malformed error 2- Open -> Can read the database In any of the cases, I can't imagine what to do next. > All the incidents like this I can remember have been the result of bad memory > management or using stale pointers. It's likely that you will eventually > find that some other process is overwriting SQLite's memory or writing to the > database file. Oh, so you believe that maybe if the database is really malformed I'll find "garbage" that shouldn't be there using a hex editor, and by understanding what that garbage is (lets say log messages that should go to stderr) I can fix the issue on my software? That would be an outcome of #2 above? > But it's possible you've found a genuine bug in SQLite and we'll try to help. Well, this should be really unlikely, right? > Simon > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Andrés Riancho Project Leader at w3af - http://w3af.org/ Web Application Attack and Audit Framework Twitter: @w3af GPG: 0x93C344F3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
Roger, On Mon, Sep 8, 2014 at 12:26 AM, Roger Binnswrote: > On 07/09/14 19:11, Andres Riancho wrote: >> * I'm setting [4] "PRAGMA synchronous=OFF" for increased >> performance. Can this trigger malformed errors? > > Read the doc: > > https://sqlite.org/pragma.html#pragma_synchronous > > TLDR: yes > > To improve write performance use WAL: > > https://sqlite.org/wal.html > > Realise that SQLite can only be safe if at various points the data it wants > on the storage is actually completely written out and unaffected by power > failures etc. You can go a lot faster by not doing that, but then the data > isn't safe. Well, I should have explained my use case a little bit more. In my project we use the database to store data during the process life, and then remove it when the process finishes. This will never be done by w3af: * Process #1: Store something in sqlite * Shutdown * Process #3: Read data from sqlite The flow always looks like: * Process #1: Store something in sqlite * Process #1: Read data from sqlite ... * Process #1: Store something in sqlite * Process #1: Read data from sqlite * Process #1; Close DB and remove file I got this into account when I decided to go with sync=OFF. For me if I have a power failure, it doesn't matter much, since "Process #1" will die, and there is no way for a new process of w3af to read the DB (broken or not). With this in mind, sync=OFF still looks like something that could cause "database malformed" errors? > Roger > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Andrés Riancho Project Leader at w3af - http://w3af.org/ Web Application Attack and Audit Framework Twitter: @w3af GPG: 0x93C344F3 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 07/09/14 19:11, Andres Riancho wrote: > * I'm setting [4] "PRAGMA synchronous=OFF" for increased > performance. Can this trigger malformed errors? Read the doc: https://sqlite.org/pragma.html#pragma_synchronous TLDR: yes To improve write performance use WAL: https://sqlite.org/wal.html Realise that SQLite can only be safe if at various points the data it wants on the storage is actually completely written out and unaffected by power failures etc. You can go a lot faster by not doing that, but then the data isn't safe. Roger ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Python - database disk image is malformed
On 8 Sep 2014, at 3:11am, Andres Rianchowrote: >I'm using sqlite as the database backend for an open source > project and it works perfectly 99% of the time; however some users > have reported "database disk image is malformed" errors [1][2]. There are two possibilities: A) The database really is malformed B) The client's copy of SQLite is returning that code by mistake. Can you have one of these clients send you a copy of their database after they have received this message ? Then you can try and open it yourself and see whether it really is malformed. All the incidents like this I can remember have been the result of bad memory management or using stale pointers. It's likely that you will eventually find that some other process is overwriting SQLite's memory or writing to the database file. But it's possible you've found a genuine bug in SQLite and we'll try to help. Simon ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users