Re: [sqlite] Python - database disk image is malformed

[Richard Hipp]

On Mon, Sep 8, 2014 at 1:56 PM, Andres Riancho 
wrote:

> 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

[Andres Riancho]

On Mon, Sep 8, 2014 at 2:37 PM, Roger Binns  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

Re: [sqlite] Python - database disk image is malformed

[Andres Riancho]

On Mon, Sep 8, 2014 at 2:27 PM, Roger Binns  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

[Roger Binns]

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

[Simon Slavin]

On 8 Sep 2014, at 6:27pm, Roger Binns  wrote:

> 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

[Roger Binns]

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

[Simon Slavin]

On 8 Sep 2014, at 11:47am, Andres Riancho  wrote:

> 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

[Andres Riancho]

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
 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+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

[Andres Riancho]

Simon,

On Sun, Sep 7, 2014 at 11:39 PM, Simon Slavin  wrote:
>
> 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

[Andres Riancho]

Roger,

On Mon, Sep 8, 2014 at 12:26 AM, Roger Binns  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

[Roger Binns]

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

[Simon Slavin]

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.

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