Re: [sqlite] Python - database disk image is malformed
Roger, On Mon, Sep 8, 2014 at 12:26 AM, Roger Binns rog...@rogerbinns.com wrote: 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
Simon, On Sun, Sep 7, 2014 at 11:39 PM, Simon Slavin slav...@bigfraud.org wrote: On 8 Sep 2014, at 3:11am, Andres Riancho andres.rian...@gmail.com 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
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 Riancho andres.rian...@gmail.com wrote: 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+malformedtype=Issuesutf8=%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
On Mon, Sep 8, 2014 at 2:27 PM, Roger Binns rog...@rogerbinns.com wrote: 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 Mon, Sep 8, 2014 at 2:37 PM, Roger Binns rog...@rogerbinns.com wrote: 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
[sqlite] Python - database disk image is malformed
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+malformedtype=Issuesutf8=%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 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
