Re: [sqlite] database disk image is malformed

[Peter da Silva]

I have been bitten by this a couple of times, so now I'm super-conservative
about how I deal with this.

What I do is have any parent database setup done by having the parent spawn
a child process to do the actual database work, and return any data the
parent needs in the status or (if more than a success status is needed)
through a pipe.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Rowan Worth]

On Fri, 15 Nov 2019 at 16:10, Graham Holden  wrote:

> I've been having problems with my email system... I don't think
> earlier attempts at sending have made it to the list, but if they
> did, apologies for any duplication...
>
> Monday, November 11, 2019, 5:46:05 PM, Jukka Marin 
> wrote:
>
> >> On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:
> >>
> >> > The main process first opens the databases and checks that their
> >> > version matches that of the software and if not, the databases are
> >> > closed and initialized by running a script.
> >> >
> >> > After closing the databases, main process forks the children and
> >> > all processes (including main process) open the databases and use
> >> > their own connections.
> >> >
> >> > What I was trying to ask was this:  If any of the children dies
> >> > (a bug in the code), main process will restart the child.  At
> >> > this point, the main process has the databases open, so the new
> >> > child receives the connections as well.  What should I do now?
> >>
>
> This isn't from personal experience, but (possibly misremembered)
> snippets from this list and (possibly incorrect) deductions from
> them...
>
> The problem (or, perhaps, "a" problem) with passing SQLite connections
> across fork() is, I think, how Linux?/POSIX? deals with file-locks on
> the underlying file-handle. IIRC, if both parent/child process share a
> file-handle, and one of them closes that file, then ALL file-level
> locks (which is what SQLite uses) on that handle are released: not
> just the ones created by the terminating process.
>

The mechanism you're thinking of exists, but only between threads of the
same process. It's also described in the "how to corrupt an sqlite database
file" writeup, section 2.2:

https://sqlite.org/howtocorrupt.html#_posix_advisory_locks_canceled_by_a_separate_thread_doing_close_

fork creates separate processes, so it shouldn't be impacted by that
mechanism. But there's an easily identified problem: posix locks are not
inherited by a fork()ed child, and sqlite keeps track (in-memory) of what
locks it has acquired. So after a fork() the child will inherit sqlite's
internal idea of what locks are held, but none of the actual locks, making
it instantly out of sync with reality if any locks were held.

-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Simon Slavin]

On 15 Nov 2019, at 8:03am, Graham Holden  wrote:

> What I *think* this may mean is that re-spawned children will inhereit
> the open file-handle of the SQLite connection opened by the parent
> after it initially fired all child processes.

That's not a problem.

> Even if the (re-spawned)
> child never make use of that connection or the file-handle, when it
> next dies (either a natural death or another unexpected termination),
> then the OS will close the inherited file-handle and release all
> file-locks on it

That's a huge problem.  In which case you're right: the parent process should 
close SQLite connections before it forks.

Is that really how Unix works ?  How annoying.  Seems a strange design-choice, 
but it wouldn't be the only one.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Graham Holden]

I've been having problems with my email system... I don't think
earlier attempts at sending have made it to the list, but if they
did, apologies for any duplication...

Monday, November 11, 2019, 5:46:05 PM, Jukka Marin  wrote:

>> On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:
>> 
>> > The main process first opens the databases and checks that their
>> > version matches that of the software and if not, the databases are
>> > closed and initialized by running a script.
>> > 
>> > After closing the databases, main process forks the children and
>> > all processes (including main process) open the databases and use
>> > their own connections.
>> > 
>> > What I was trying to ask was this:  If any of the children dies
>> > (a bug in the code), main process will restart the child.  At
>> > this point, the main process has the databases open, so the new
>> > child receives the connections as well.  What should I do now?
>> 

This isn't from personal experience, but (possibly misremembered)
snippets from this list and (possibly incorrect) deductions from
them...

The problem (or, perhaps, "a" problem) with passing SQLite connections
across fork() is, I think, how Linux?/POSIX? deals with file-locks on
the underlying file-handle. IIRC, if both parent/child process share a
file-handle, and one of them closes that file, then ALL file-level
locks (which is what SQLite uses) on that handle are released: not
just the ones created by the terminating process.

What I *think* this may mean is that re-spawned children will inhereit
the open file-handle of the SQLite connection opened by the parent
after it initially fired all child processes. Even if the (re-spawned)
child never make use of that connection or the file-handle, when it
next dies (either a natural death or another unexpected termination),
then the OS will close the inherited file-handle and release all
file-locks on it (that would have been created by the parent process).

At this point, the parent process will THINK it has appropriate
file-locks and that it is safe to access the database file, but to all
the other (child) processes, it does not appear to have any locks, and
they too may try to access the database file. And that way, madness
lies!

I *suspect* Simon's "The conservative way to do it" (close the
connection/file-handle around re-spawns) may be more necessary than
originally thought. (Alternatively, perhaps, have whatever database
access the parent is doing happen in another child process, and have
the parent "just" monitoring processes and re-spawning as needed).

Of course, I may be wrong, or the "releasing all locks" may only apply
in some circumstances... hopefully someone with more direct experience
can confirm or deny.

Graham


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Andreas Kretzer]

Just to make sure, you didn't oversee that (like I was ...):
Daemonizing a process is also a fork() - and this invalidates
your connection!

I used the daemon() function in my program (together with a
few other processes) that used a DB connection. I opened
this connection _before_ I called daemon() and everything seemed
right. But as soon, as the other processes terminated, the lock
and WAL files went away, and everything I did with the DB didn't
reach the actual files. After that, the DB image was damaged.

Andreas

Am 11.11.2019 um 18:37 schrieb Simon Slavin:

On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:


The main process first opens the databases and checks that their
version matches that of the software and if not, the databases are
closed and initialized by running a script.

After closing the databases, main process forks the children and
all processes (including main process) open the databases and use
their own connections.

What I was trying to ask was this:  If any of the children dies
(a bug in the code), main process will restart the child.  At
this point, the main process has the databases open, so the new
child receives the connections as well.  What should I do now?

Okay, that gives us enough information to work with.

The conservative way to do it is to have the main process close the connection 
before forking and open it again.  Then, of course, the child processes make 
their own connections.

But I don't think that's necessary.  A child process can have access to the 
main process' database connection but ignore it.  So I think the main process 
can fork without closing its connection.  Then each child can never use that 
one but instead make its own.

Of course, every one of these connections needs to set a timeout.  And every 
call to the SQLite3 library needs to check its result code and make sure it is 
getting SQLITE_OK (or, for queries, SQLITE_DONE etc.).


Should the child close the databases before opening them again?
Will this close the databases for the main process as well?

As you suspected, closing the connection releases both memory structures and 
file handles.  Anything that tries to use that connection will then fail 
because it has no idea what it's talking to.

What puzzles me is this: you're getting "database malformed" and nothing you've 
described justifies this.  Assuming that this isn't just one old database which is 
genuinely corrupt, but that you are using a fresh uncorrupt database each time, you seem 
to have a genuine bug in your code.

This happens mostly because something is stomping on the memory assigned to a 
connection.  In your case, this probably means something is stomping on the 
memory assigned to one of the child processes.

So, first write yourself a quick script to use the shell tool to check the 
database for corruption.  Then run that, even while your program is running, 
and see if you can figure out whether your database really is corrupt or 
whether your program is getting spurious error messages.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--

Mit freundlichen Grüßen
Andreas Kretzer

ETB Electronic Team
Beratungs- und Vertriebs GmbH

Berliner Straße 8a
15537 Erkner

FON   +49 3362 889349-12
FAX   +49 3362 889349-23

email: a.kret...@etb-electronic.de

AG Potsdam HRB 16532; Sitz der Gesellschaft: Am Mellensee
Geschäftsführer: Wolfgang A. Runge, Marco Runge, Jürgen Gentzsch


___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Jukka Marin]

On Mon, Nov 11, 2019 at 05:37:37PM +, Simon Slavin wrote:
> On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:
> 
> > The main process first opens the databases and checks that their
> > version matches that of the software and if not, the databases are
> > closed and initialized by running a script.
> > 
> > After closing the databases, main process forks the children and
> > all processes (including main process) open the databases and use
> > their own connections.
> > 
> > What I was trying to ask was this:  If any of the children dies
> > (a bug in the code), main process will restart the child.  At
> > this point, the main process has the databases open, so the new
> > child receives the connections as well.  What should I do now?
> 
> Okay, that gives us enough information to work with.
> 
> The conservative way to do it is to have the main process close the 
> connection before forking and open it again.  Then, of course, the child 
> processes make their own connections.
> 
> But I don't think that's necessary.  A child process can have access to the 
> main process' database connection but ignore it.  So I think the main process 
> can fork without closing its connection.  Then each child can never use that 
> one but instead make its own.

Okay, that's what I was hoping for.

> Of course, every one of these connections needs to set a timeout.  And every 
> call to the SQLite3 library needs to check its result code and make sure it 
> is getting SQLITE_OK (or, for queries, SQLITE_DONE etc.).

Yes, I'm doing all this,

> > Should the child close the databases before opening them again?
> > Will this close the databases for the main process as well?
> 
> As you suspected, closing the connection releases both memory structures and 
> file handles.  Anything that tries to use that connection will then fail 
> because it has no idea what it's talking to.

Ok.

> What puzzles me is this: you're getting "database malformed" and nothing 
> you've described justifies this.  Assuming that this isn't just one old 
> database which is genuinely corrupt, but that you are using a fresh uncorrupt 
> database each time, you seem to have a genuine bug in your code.

That was happening before I changed my code to open the databases in
the children.  Originally, only main process opened the databases and
the children "inherited" the connections.  I was wondering if this was
okay, but I didn't see a warning in the SQLite docs, so.. I never found
the "how to corrupt your database" manual on my own ;-)

I came back to the mailing list when I noticed the above "problem" of
a child dying and needing to respawn it while the main process already
has the databases open.

So I guess it's safe now that all the children open the databases by
themselves.

Thanks to all who responded!

  -jm
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Simon Slavin]

On 11 Nov 2019, at 5:13pm, Jukka Marin  wrote:

> The main process first opens the databases and checks that their
> version matches that of the software and if not, the databases are
> closed and initialized by running a script.
> 
> After closing the databases, main process forks the children and
> all processes (including main process) open the databases and use
> their own connections.
> 
> What I was trying to ask was this:  If any of the children dies
> (a bug in the code), main process will restart the child.  At
> this point, the main process has the databases open, so the new
> child receives the connections as well.  What should I do now?

Okay, that gives us enough information to work with.

The conservative way to do it is to have the main process close the connection 
before forking and open it again.  Then, of course, the child processes make 
their own connections.

But I don't think that's necessary.  A child process can have access to the 
main process' database connection but ignore it.  So I think the main process 
can fork without closing its connection.  Then each child can never use that 
one but instead make its own.

Of course, every one of these connections needs to set a timeout.  And every 
call to the SQLite3 library needs to check its result code and make sure it is 
getting SQLITE_OK (or, for queries, SQLITE_DONE etc.).

> Should the child close the databases before opening them again?
> Will this close the databases for the main process as well?

As you suspected, closing the connection releases both memory structures and 
file handles.  Anything that tries to use that connection will then fail 
because it has no idea what it's talking to.

What puzzles me is this: you're getting "database malformed" and nothing you've 
described justifies this.  Assuming that this isn't just one old database which 
is genuinely corrupt, but that you are using a fresh uncorrupt database each 
time, you seem to have a genuine bug in your code.

This happens mostly because something is stomping on the memory assigned to a 
connection.  In your case, this probably means something is stomping on the 
memory assigned to one of the child processes.

So, first write yourself a quick script to use the shell tool to check the 
database for corruption.  Then run that, even while your program is running, 
and see if you can figure out whether your database really is corrupt or 
whether your program is getting spurious error messages.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Jukka Marin]

On Mon, Nov 11, 2019 at 05:03:25PM +, Simon Slavin wrote:
> On 11 Nov 2019, at 1:42pm, Jukka Marin  wrote:
> 
> > Or does the main process need to close all databases, then fork, then
> > reopen the databases?
> 
> Which processes access the databases ?  The main process ?  Its children ?  
> Are they all using the same connection ?
>  Are they all trying to use the same connection at the same time ?

All processes access the databases.  No, I changed the code so that
every process opens the databases separately, so they use their own
connections (at random times, so probably simultaneously).

The main process first opens the databases and checks that their
version matches that of the software and if not, the databases are
closed and initialized by running a script.

After closing the databases, main process forks the children and
all processes (including main process) open the databases and use
their own connections.

What I was trying to ask was this:  If any of the children dies
(a bug in the code), main process will restart the child.  At
this point, the main process has the databases open, so the new
child receives the connections as well.  What should I do now?
Should the child close the databases before opening them again?
Will this close the databases for the main process as well?

(One way is to stop using the databases in the main process, so
they are not passed to children, but this would be a major change
in the code.)

  -jm
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Shawn Wagner]

Doing the latter - closing everything, forking, re-opening - is always
going to be safe. Or if the parent isn't going to use the connection, just
don't open the database until you're in the child after forking.

On Mon, Nov 11, 2019 at 8:08 AM Jukka Marin  wrote:

> On Fri, Nov 08, 2019 at 09:57:25AM +0200, Jukka Marin wrote:
> > On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote:
> > > This line stood out:
> > >
> > > > The main process opens the databases and then forks the other
> processes
> > > which can then perform database operations using the already opened
> > > databases.
> > >
> > > From
> > >
> https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_
> > > :
> > >
> > > > Do not open an SQLite database connection, then fork(), then try to
> use
> > > that database connection in the child process. All kinds of locking
> > > problems will result and you can easily end up with a corrupt database.
> > > SQLite is not designed to support that kind of behavior. Any database
> > > connection that is used in a child process must be opened in the child
> > > process, not inherited from the parent.
> > >
> > > In this kind of situation, I usually use pthread_atfork() callbacks to
> > > automate closing databases and then re-opening them in the parent and
> child.
> >
> > Okay, thanks!  I suspected it could be something like this, but couldn't
> > find anything in the SQLite docs.
>
> In some situations, my main process will have the databases opened before
> it needs to fork a new child (this happens only if a child dies and
> has to be restarted).  If the child process immediately closes its copies
> of the databases and then reopens them, will it be safe?
>
> Or does the main process need to close all databases, then fork, then
> reopen the databases?
>
> Thanks again!
>
>   Jukka Marin
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Simon Slavin]

On 11 Nov 2019, at 1:42pm, Jukka Marin  wrote:

> Or does the main process need to close all databases, then fork, then
> reopen the databases?

Which processes access the databases ?  The main process ?  Its children ?  Are 
they all using the same connection ?  Are they all trying to use the same 
connection at the same time ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Jukka Marin]

On Fri, Nov 08, 2019 at 09:57:25AM +0200, Jukka Marin wrote:
> On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote:
> > This line stood out:
> > 
> > > The main process opens the databases and then forks the other processes
> > which can then perform database operations using the already opened
> > databases.
> > 
> > From
> > https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_
> > :
> > 
> > > Do not open an SQLite database connection, then fork(), then try to use
> > that database connection in the child process. All kinds of locking
> > problems will result and you can easily end up with a corrupt database.
> > SQLite is not designed to support that kind of behavior. Any database
> > connection that is used in a child process must be opened in the child
> > process, not inherited from the parent.
> > 
> > In this kind of situation, I usually use pthread_atfork() callbacks to
> > automate closing databases and then re-opening them in the parent and child.
> 
> Okay, thanks!  I suspected it could be something like this, but couldn't
> find anything in the SQLite docs.

In some situations, my main process will have the databases opened before
it needs to fork a new child (this happens only if a child dies and
has to be restarted).  If the child process immediately closes its copies
of the databases and then reopens them, will it be safe?

Or does the main process need to close all databases, then fork, then
reopen the databases?

Thanks again!

  Jukka Marin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Jukka Marin]

On Thu, Nov 07, 2019 at 09:26:46AM -0800, Shawn Wagner wrote:
> This line stood out:
> 
> > The main process opens the databases and then forks the other processes
> which can then perform database operations using the already opened
> databases.
> 
> From
> https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_
> :
> 
> > Do not open an SQLite database connection, then fork(), then try to use
> that database connection in the child process. All kinds of locking
> problems will result and you can easily end up with a corrupt database.
> SQLite is not designed to support that kind of behavior. Any database
> connection that is used in a child process must be opened in the child
> process, not inherited from the parent.
> 
> In this kind of situation, I usually use pthread_atfork() callbacks to
> automate closing databases and then re-opening them in the parent and child.

Okay, thanks!  I suspected it could be something like this, but couldn't
find anything in the SQLite docs.

I'll change my code and see what happens :-)

Thanks again for the quick reply!

  Jukka Marin
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Shawn Wagner]

This line stood out:

> The main process opens the databases and then forks the other processes
which can then perform database operations using the already opened
databases.

From
https://sqlite.org/howtocorrupt.html#_carrying_an_open_database_connection_across_a_fork_
:

> Do not open an SQLite database connection, then fork(), then try to use
that database connection in the child process. All kinds of locking
problems will result and you can easily end up with a corrupt database.
SQLite is not designed to support that kind of behavior. Any database
connection that is used in a child process must be opened in the child
process, not inherited from the parent.

In this kind of situation, I usually use pthread_atfork() callbacks to
automate closing databases and then re-opening them in the parent and child.


On Thu, Nov 7, 2019 at 9:18 AM Jukka Marin  wrote:

> Dear List,
>
> I'm developing software which keeps parameters and real-time data in
> SQLite databases on a x86_64/linux system.  I am getting "database
> disk image is malformed" errors from SQLite when using select.  Some
> select operations succeed, some fail.  This happens on multiple systems.
>
> I would like to know if I'm doing something that is known not to work
> with SQLite.  Background information:
>
> The software runs on a xen virtual machine.  The database files are
> on a separate disk (actually, a separate partition of the host linux)
> which is directly mounted on the virtual machine (not using NFS)
> and uses ext4 filesystem.  The whole system runs off a single SSD disk.
>
> SQLite version is 3.28.0 and it is currently built and statically
> linked against the application binary.
>
> The databases are originally created by a script and the sqlite3
> command.  "pragma journal_mode=wal;" is set for all databases.
>
> The software consists of multiple processes.  The main process opens
> the databases and then forks the other processes which can then perform
> database operations using the already opened databases.  No locking is
> done in the processes, they rely on the SQLite internal mechanisms.
> sqlite3_busy_timeout is set to 2500 for all databases.
>
> The processes mostly read (select) data from the databases, but also
> occasionally add new rows or update existing rows.
>
>
> Is this system with multiple processes accessing the databases
> safe with SQLite?  If it is, does anyone have ideas of what could
> be corrupting the database files?
>
> These systems are running 24/7, but of course, it _may_ be possible
> that someone has disconnected the power supply without shutting down
> the system first and causing errors in the database files.
>
> Is there something I could do to (try to) prevent database corruption
> if power is lost?  (In software - a UPS is a hardware solution, of
> course).
>
> If the database is corrupted, is there any way of recovering it?
> Maybe I should use sqlite3 to dump and recreate the database (of
> course, some or all of the data might get lost)?
>
> Experienced SQLite users, please share your ideas - thank you! :-)
>
>   Jukka Marin
>   (a beginner with SQLite)
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database disk image is malformed

[Jukka Marin]

Dear List,

I'm developing software which keeps parameters and real-time data in
SQLite databases on a x86_64/linux system.  I am getting "database
disk image is malformed" errors from SQLite when using select.  Some
select operations succeed, some fail.  This happens on multiple systems.

I would like to know if I'm doing something that is known not to work
with SQLite.  Background information:

The software runs on a xen virtual machine.  The database files are
on a separate disk (actually, a separate partition of the host linux)
which is directly mounted on the virtual machine (not using NFS)
and uses ext4 filesystem.  The whole system runs off a single SSD disk.

SQLite version is 3.28.0 and it is currently built and statically
linked against the application binary.

The databases are originally created by a script and the sqlite3
command.  "pragma journal_mode=wal;" is set for all databases.

The software consists of multiple processes.  The main process opens
the databases and then forks the other processes which can then perform
database operations using the already opened databases.  No locking is
done in the processes, they rely on the SQLite internal mechanisms.
sqlite3_busy_timeout is set to 2500 for all databases.

The processes mostly read (select) data from the databases, but also
occasionally add new rows or update existing rows.


Is this system with multiple processes accessing the databases
safe with SQLite?  If it is, does anyone have ideas of what could
be corrupting the database files?

These systems are running 24/7, but of course, it _may_ be possible
that someone has disconnected the power supply without shutting down
the system first and causing errors in the database files.

Is there something I could do to (try to) prevent database corruption
if power is lost?  (In software - a UPS is a hardware solution, of
course).

If the database is corrupted, is there any way of recovering it?
Maybe I should use sqlite3 to dump and recreate the database (of
course, some or all of the data might get lost)?

Experienced SQLite users, please share your ideas - thank you! :-)

  Jukka Marin
  (a beginner with SQLite)
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 6 Oct 2017, at 1:55pm, Fahad  wrote:

> I can now reliably corrupt my database - and this happens only when some
> other process is writing to the database while I perform sqlite3_close_v2 on
> the connections.

Reliable corruption will help investigation tremendously.  To help investigate 
this ..

What macOS version ?  If you’re not running the 'bash' that comes with it, tell 
us.

What format is the drive the database is stored on in ?  Is it the boot drive 
for that computer ?

Is any part of your test setup still running as a Safari addon/plugin ?

Just for debugging purposes, can you try your program running with the database 
stored on some other drive ?  Perhaps an external spinning disk, or a Flash 
drive.  Does that increase or decrease the frequency of corruption ?

> #define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra
> #define SQLITE_TEMP_STORE 3 // always use memory

Just for debugging purposes, can you try removing these ?  Just let SQLite do 
whatever it would do by default.  Does that increase or decrease the frequency 
of corruption ?

> Page 24256: btreeInitPage() returns error code 11
> On tree page 722 cell 1: 2nd reference to page 24256
> On tree page 932 cell 3: 2nd reference to page 24255

This suggests that something has overwritten your database file starting at 
page 24255.  It is a common source of errors reported by SQLite: some other 
part of a program (Safari ?) suddenly decides to write to the wrong file 
handle.  If you have a hex viewer for the Mac (I recommend '0xED.app') you 
could take a look and see if the text starting there looks like your data (i.e. 
preceding pages) or something completely different.

Having received error messages like this, please close all connections to the 
database, then use the shell tool to execute "PRAGMA integrity_check()" and 
check that the database file on disk is corrupt, rather than some in-memory 
version of it accessed by your software.

Simon.

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I can now reliably corrupt my database - and this happens only when some
other process is writing to the database while I perform sqlite3_close_v2 on
the connections. I'll explain what I'm doing.

Since the last I wrote, I've disabled all the flags other than these:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra
#define SQLITE_TEMP_STORE 3 // always use memory

It now assumes that sqlite is in serialised mode (and thus, thread safe).
I've removed all sorts of clever caching within the app and have simply two
connections in WAL mode (one for writing, one for reading). These are used
by various different threads, presumably at the same time but that should
not be an issue with sqlite in serialised mode.

This is how I can reproduce the malformed bug:

1) I have a bash script that runs in a loop:

#!/bin/bash
while :
do
  ls -alF
  sqlite3 mydb.db "CREATE TABLE IF NOT EXISTS junk (INTEGER a);"
  sqlite3 mydb.db "SELECT count(*) FROM junk;"
  sqlite3 mydb.db "SELECT count(*) FROM junk;INSERT INTO junk VALUES (1);"
  sqlite3 mydb.db "PRAGMA integrity_check;"

  sleep 0.5
done


2) I have my app running on the side. I launch it with a fresh copy of the
mydb.db for the app (pre-corruption, with data from the app in there
already). The app does it's thing (selecting / inserting data). I then quit
the app, at which point it closes the two connections with sqlite3_close_v2
*successfully*. 

3) 8/10, my bash script suddenly starts spewing this on the console:

Page 24256: btreeInitPage() returns error code 11
On tree page 722 cell 1: 2nd reference to page 24256
On tree page 932 cell 3: 2nd reference to page 24255

This happens only after I am able to close the connections successfully from
the app and the app successfully quits (connected to Xcode's debugger).

I have no idea what is going on since I've dumbed down the code to the point
where it's simply opening a connection at launch, closing it at termination.
I'm not using any extra mutex's within the app, assuming the sqlite works
correctly in serialised mode.

Please help!



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[David Raymond]

The terminology for that option has always caught me out. The best phrase is 
the one below.
http://www.sqlite.org/compile.html#threadsafe
"When compiled with SQLITE_THREADSAFE=2, SQLite can be used in a multithreaded 
program so long as no two threads attempt to use the same database connection 
(or any prepared statements derived from that database connection) at the same 
time."

So for thread safety level, 0 < 2 < 1


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Fahad
Sent: Monday, September 25, 2017 5:27 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] 'database disk image is malformed' only on the mac

I don't think so:

https://sqlite.org/threadsafe.html

"With -DSQLITE_THREADSAFE=2 the threading mode is multi-thread." Setting it
to 0 disables all mutexes (assumes single threaded)

So I've set it to be multi-threaded.

Okay so I've wrapped @synchronized(..) around my database usage, stopped
caching / re-using prepared statements and am finalising them as soon as
they're used. I am still re-using the same database connection linked to the
same thread (i.e. I still have multiple threads, each with their own
database connection opened, however only one thread at any given time is
able to perform a db-operation, such as SELECT / UPDATE and so on).

Database corruption has stopped completely, except I now am seeing
occasional reports of disk I/O errors (error 522). I have no idea how the
database file is being truncated. I've made sure with the user that no other
instance of the app is running. I've made sure the code itself is not
deleting or touching any of the -wal and -shm files. I've also fallen back
to using these flags now:

#define SQLITE_ENABLE_FTS3 1

#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra

#define SQLITE_THREADSAFE 2 // 1: Serialized, 2: Multi-threaded, 3:
Single-threaded

#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_TEMP_STORE 3 // use memory

Like I said before, I am tempted to just fallback to using DELETE / TRUNCATE
journaling mode; I'm worried about concurrency though. The app has various
plugins that can at any time access and write to the same database that's
already in-use by the main app. Will this pose a problem with DELETE or
TRUNCATE journaling? 

Thanks
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I don't think so:

https://sqlite.org/threadsafe.html

"With -DSQLITE_THREADSAFE=2 the threading mode is multi-thread." Setting it
to 0 disables all mutexes (assumes single threaded)

So I've set it to be multi-threaded.

Okay so I've wrapped @synchronized(..) around my database usage, stopped
caching / re-using prepared statements and am finalising them as soon as
they're used. I am still re-using the same database connection linked to the
same thread (i.e. I still have multiple threads, each with their own
database connection opened, however only one thread at any given time is
able to perform a db-operation, such as SELECT / UPDATE and so on).

Database corruption has stopped completely, except I now am seeing
occasional reports of disk I/O errors (error 522). I have no idea how the
database file is being truncated. I've made sure with the user that no other
instance of the app is running. I've made sure the code itself is not
deleting or touching any of the -wal and -shm files. I've also fallen back
to using these flags now:

#define SQLITE_ENABLE_FTS3 1

#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_SYNCHRONOUS 1 // 1: Normal, 2: Full, 3: Extra

#define SQLITE_THREADSAFE 2 // 1: Serialized, 2: Multi-threaded, 3:
Single-threaded

#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_TEMP_STORE 3 // use memory

Like I said before, I am tempted to just fallback to using DELETE / TRUNCATE
journaling mode; I'm worried about concurrency though. The app has various
plugins that can at any time access and write to the same database that's
already in-use by the main app. Will this pose a problem with DELETE or
TRUNCATE journaling? 

Thanks
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Keith Medcalf]

>#define SQLITE_ENABLE_FTS3 1
>#define SQLITE_OMIT_DEPRECATED 1
>#define SQLITE_OMIT_SHARED_CACHE 1
>#define SQLITE_OMIT_AUTOMATIC_INDEX 1
>#define SQLITE_OMIT_DECLTYPE 1
>
>#define SQLITE_DEFAULT_MMAP_SIZE 0
>#define SQLITE_DEFAULT_MEMSTATUS 0
>#define SQLITE_DEFAULT_SYNCHRONOUS 1
>
>#define SQLITE_THREADSAFE 2
>
>#define SQLITE_MAX_MMAP_SIZE 0
>#define SQLITE_TEMP_STORE 3
>
>
>I've set it to be thread-safe. 

Actually, no.  You have set it to "thread unsafe".  Thread Safe is the default, 
which is SQLITE_THREADSAFE=1

The values of SQLITE_THREADSAFE are
  0:  No threading. SQLite3 routines will only ever be called from a single 
thread.
  1:  Thread Safe.  SQLite3 will impose thread-safety on your code so that 
you may freely do whatever you want from any thread.
  2:  No Thread Safety: Thread Safety is turned off.  You are responsible for 
ensuring thread safety.

If you turn on thread safety (return to the default), does the application work 
properly?  If so, you have made a threading error in your code from which 
"thread safe" is protecting you -- on the other hand, if it still does not work 
properly then the error does not involve threading (within the SQLite3 code) 
but is rather something else the application is doing wrong (like tromping on 
memory owned by SQLite3).




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Dan Kennedy]

On 09/21/2017 04:20 AM, Fahad wrote:

Thanks Jens, yes I didn't take the warnings from the Thread Sanitizer
lightly. Although I'm very confident with the actual implementation (using
thread local dictionaries) and have verified time and again using unit tests
that my code is otherwise thread-safe, I really do think there are perhaps
certain things I'm not totally clear about.

The oddest thing about the thread sanitiser race condition warnings was that
the thread 'reading' the same object was in fact from a 'Query-only'
connection (with PRAGMA query_only=1 set) and the thread 'writing' was
usually the writer. I've compiled SQLite with multi-threaded support and
that didn't seem to help.


If it's the one I think it is, its safe. It happens when a writer 
updates the hash table stored in the *-shm file while a reader is 
reading it. But the hash table is designed so that:


  * To add an entry, a single 32-bit 0x is overwritten with a 
non-zero 32-bit integer is written to the shared-memory, and
  * It doesn't matter to the reader whether or not it sees the 0x00 or 
the new value


So, although there is a race condition that affects which branch of a 
condition the reader takes, both branches are safe.


If you post one of the call stacks we can confirm that that is the error 
you're seeing.


Dan.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Kees Nuyt]

On Wed, 20 Sep 2017 06:41:05 -0700 (MST), Fahad
 wrote:

[...]
> 1) Thread A: Create a new connection, if one already does not exist for the
> thread. Store it's 'reference count' in the thread storage (I close a
> connection when the count becomes zero).
> 2) Thread A: Get a previously stored statement for that connection against a
> name (using a dictionary for this) from the statement cache (again, from the
> thread local storage) - if one doesn't exist, call sqlite3_prepare_v2 on a
> new statement and save it in the statement cache. Since a single thread can
> always only have a single db connection, the statement is thread-safe.
>
> ... assume some nested asynchronous calls
>
> 3) Thread A: Grab a cached connection, and then grab an existing statement.
> If found, re-use it by first calling sqlite3_clear_bindings(pStmt).

I'm not an expert, but: _clear_bindings() is not enough to clear
the statement context data. To reuse a statement, you'd have to
_reset() it.

> 4) Thread A: Close connection (i.e. decrement the reference count, if it's
> zero first clear the statement cache by calling sqlite3_reset and
> sqlite3_finalize on all the cached statements). In case the reference count
> is't 0, the connection is kept alive. 
[...]

HTH
-- 
Regards,
Kees Nuyt
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Thanks Jens, yes I didn't take the warnings from the Thread Sanitizer
lightly. Although I'm very confident with the actual implementation (using
thread local dictionaries) and have verified time and again using unit tests
that my code is otherwise thread-safe, I really do think there are perhaps
certain things I'm not totally clear about. 

The oddest thing about the thread sanitiser race condition warnings was that
the thread 'reading' the same object was in fact from a 'Query-only'
connection (with PRAGMA query_only=1 set) and the thread 'writing' was
usually the writer. I've compiled SQLite with multi-threaded support and
that didn't seem to help. 

Once I added @synchronized(lockObj) { .. } around all my readers and
writers, I stopped getting these warnings from the sanitiser. Database
corruption stopped for one user but instead got replaced by disk I/O errors.
Since then I've now disabled all the statement caching as well. Thread local
instances are guaranteed to belong to that particular thread, but I'm no
longer taking any chances. I'm now preparing a new statement and finalising
it immediately inside of a synchronised block of code. Going to try if this
works, but in effect months of 'clever engineering' has been replaced with a
bunch of ugly synchronised blocks that defeat the point of using sqlite with
multi-threading support :)



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

No I'm not.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Brian Macy]

Fahad,

Are you calling sqlite3_wal_checkpoint_v2?

Brian Macy


On Sep 20, 2017, 1:59 PM -0400, wrote:
>
> I've run the Thread Sanitizer with my own SQLite-based on macOS, and haven't 
> seen any warnings in sqlite3.c. So what you got could be a real warning sign.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Jens Alfke]

> On Sep 19, 2017, at 8:20 PM, Fahad  wrote:
> 
> I recently switched on the Thread Sanitizer in Xcode only to find that it
> was complaining of race conditions inside of the sqlite3.c code, that the
> various readers and writers were trying to read / write to the same
> wal-index.

I've run the Thread Sanitizer with my own SQLite-based on macOS, and haven't 
seen any warnings in sqlite3.c. So what you got could be a real warning sign.

It might be worth investigating some of those warnings to see if they stem from 
illegal usage on your part, like inadvertently using a connection or statement 
on the wrong thread. (When I was doing my own tests with the Thread Sanitizer, 
I found a warning on my own code which initially looked like a false positive, 
but I investigated anyway and discovered it really was a bug of mine.)

—Jens
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I've tried that as well. Since I'm using PRAGMA journal_mode=WAL on all the
connections, I've had issues with MMAP (as acknowledged by the threads
above) so have had to disable that. I also need FTS 3 to work.

The rest of the flags to do with synchronisation and threading, I've enabled
/ disabled / modified / removed over and over again. What's worse is that I
cannot easily reproduce these corruptions, at least not using unit tests
(I've tried writing various tests with multiple threads reading and writing
at the same time). 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I apologise for the many posts, but I'm writing in hope that one of you may
point out something that I'm either doing wrong, or a concept I haven't
fully grasped. 

I'm aware that prepared statements are tied to the database connection they
were created for. In order to get more speed out of my recurring statements
(almost every statement I sqlite3_prepare_v2, I'm then re-using it later), I
store them in the thread local cache as well. Roughly, here's what I am
doing:

1) Thread A: Create a new connection, if one already does not exist for the
thread. Store it's 'reference count' in the thread storage (I close a
connection when the count becomes zero).
2) Thread A: Get a previously stored statement for that connection against a
name (using a dictionary for this) from the statement cache (again, from the
thread local storage) - if one doesn't exist, call sqlite3_prepare_v2 on a
new statement and save it in the statement cache. Since a single thread can
always only have a single db connection, the statement is thread-safe.

... assume some nested asynchronous calls

3) Thread A: Grab a cached connection, and then grab an existing statement.
If found, re-use it by first calling sqlite3_clear_bindings(pStmt).

4) Thread A: Close connection (i.e. decrement the reference count, if it's
zero first clear the statement cache by calling sqlite3_reset and
sqlite3_finalize on all the cached statements). In case the reference count
is't 0, the connection is kept alive. 

Since a thread could be doing various things and a method call may result in
another nested method call that needs to query the db again, the connection
is kept alive and only closed when necessary. This keeps the overall active
'readers' in check. If there are 7 threads, there are 7 readers in theory
(although each thread will almost always run a 'task' and aim at closing the
connection if it can).

The above scenario can be imagined for multiple asynchronous threads. As I
explained, I do however use a persistent 'writer' sqlite connection (along
with a reference count for that too) and open / close only if there are no
more tasks requiring a writer. The writer was previously using a mutex in
case multiple threads need to access the same writer, but given I keep
prepared statements in a thread local cache, the statements were always
valid against the connection these were created. For the writer I use a
separate prepared statement cache by the way.

As you can see, the architecture is very elaborate, but all this ensures
utmost performance whilst ensuring thread-safety along with concurrency. The
app performs very well against multiple asynchronous tasks, but it's being
bogged with database corruption every now and then.

Could it be that I need to prepare and finalise the statements and not
re-use them like I do? Not sure how accurate this is but it seems to suggest
that we need to do this:

https://stackoverflow.com/questions/36364162/accessing-sqlite-database-from-multiple-processes-and-sqlite-busy

Reading up on look-aside memory (https://sqlite.org/malloc.html#lookaside)
I'm now even more confused as I think the memory is being stomped over
presumably and causing issues since I'm re-using my statements like I do?



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

I can't be certain now but I think this thread is related:

http://sqlite.1065341.n5.nabble.com/Re-Database-corruption-and-PRAGMA-fullfsync-on-macOS-td95366.html

It was this thread that I landed on earlier this year to presumably fix the
issues I was experiencing personally. This did help, in fact it did cut down
on the reports (I was literally getting 3 reports a day at one time and had
to act quickly). As I explained, using rather primitive locking mechanism,
I'm able to 'serialize' access between threads and this has greatly helped
but am still seeing 'disk I/O' errors and these don't go away till the
process is killed and restarted.

Given WAL relies on the -shm memory mapped file, I think deep down somewhere
there's more to memory mapping and Mac OS than meets the eye. Given two
processes in my case are writing to the same database (both opening it in
WAL journal mode), I suspect somewhere down the line these go out of sync do
to the full sync flushing issues mentioned in the thread above.

I would love to continue using WAL but am now full of doubts. I'm going to
try and switch to DELETE journal mode on the Mac exclusively (as I
mentioned, the exact same code is shared between our iOS and Mac app - not
an atom's worth of difference between the two, and iOS has never troubled
me). 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 20 Sep 2017, at 4:20am, Fahad  wrote:

> These are the flags I've finally settled on:

Revert all those settings.  Allow SQLite to use its default settings.  See if 
that makes your problem go away.

This is purely for testing.  Once you know whether it works or not you can 
start setting them again.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Hi Dan

The plugin I'm referring to is a 'Share' plugin that one can embed inside of
a mac app, which then shows up in the "Sharing" menu in Safari. When you
click on it, it launches in its own process, allowing you to share the
currently viewed website with your main app. Thus, the main app and this
plugin are two separate processes accessing the same WAL database at the
same time (both could be 'writing').

I'll explain a bit more because disk I/O errors and disk corruption is
really killing me right now. These are the flags I've finally settled on:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_OMIT_DEPRECATED 1
#define SQLITE_OMIT_SHARED_CACHE 1
#define SQLITE_OMIT_AUTOMATIC_INDEX 1
#define SQLITE_OMIT_DECLTYPE 1

#define SQLITE_DEFAULT_MMAP_SIZE 0
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_DEFAULT_SYNCHRONOUS 1

#define SQLITE_THREADSAFE 2

#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_TEMP_STORE 3


I've set it to be thread-safe. Although the app may have two processes
running at any given time (the main app,  and the plugin), any single
process itself has a single writer and multiple readers. Since WAL doesn't
support the read-only flag, I changed the readers to open like so (just as I
do my writer):

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, ,
SQLITE_OPEN_READWRITE, NULL) == SQLITE_OK);

and then I set these to be query_only:

if (sqlite3_exec(readOnlyDB, "PRAGMA query_only=1;", NULL, NULL, NULL) !=
SQLITE_OK) {
  // ...   
}

The single writer is being accessed and used by the process using a
@synchronized() block (Objective-C), ensuring any prepared statement is used
and immediately reset and finalized before leaving the block.

To ensure thread-safety and a bit of 'database pooling' (so I don't have to
open and close connections on the same thread over and over again,
especially if I have nested calls in my code), I'm open a read-only
connection and then store it in the thread-local dictionary (i.e. [[NSThread
currentThread] threadDictionary]).

This gives me the concurrency I need, along with thread-safety to ensure the
same database connection is not used in a different thread. Prepared
statements tied to a read-only connection are also being stored this way -
in the thread local dictionary). 

I recently switched on the Thread Sanitizer in Xcode only to find that it
was complaining of race conditions inside of the sqlite3.c code, that the
various readers and writers were trying to read / write to the same
wal-index. This may be desirable (as I read elsewhere that this is okay) I
felt this may be causing issues. I've thus far wrapped each and every call
to the databse using the same @synchronized(lockObj) call. Doing so
essentially has made my otherwise multi-threaded app, a serialized app since
readers wait on each other, as well as on the main writer before accessing
the database. I tested this with a user seeing disk corruption often (and
mostly when he's using both the plugin and the main app) and he's reported a
90% improvement. This time he didn't see malformed disk errors but instead
saw 'disk I/O' errors after a few hours, but a relaunch of the app fixed it.

Right now I've tried every single flag in SQLite. Ive read, and re-read the
how to corrupt your database as well as anything and everything. I'm
struggling to figure this out. The *exact same code* works in iOS just fine
- not a single complaint for years. It's only the mac (any mac, it seems).

Maybe I could just switch to TRUNCATE journal mode now that I've effectiely
serialized all database access and I'm not getting any of the benefits of
multi-threading? Would that help? I should add that I switched to WAL
earlier this year and ever since have had issues reported, at least once a
week. Nowadays it's almost once a day. With journal_mode DELETE I never had
an issue, but then the app was pretty serial then.

Thanks
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Dan Kennedy]

On 09/15/2017 10:38 AM, Fahad wrote:

I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it
again using the main app and the safari share plugin.


You don't need to call sqlite3_shutdown(). All it does is release the 
resources allocated by sqlite3_initialize() - which are trivially small 
for a workstation app and in any case are released automatically when 
the process is closed.

Main App: Finished writing to db, just opened a new connection to read
(after opening a connection I set PRAGMA query_only=1; PRAGMA
read_uncommitted=1; and register some custom functions).


The two pragmas are benign but likely no-ops. "PRAGMA read_uncommitted" 
only affects shared cache connections, and "PRAGMA query_only" doesn't 
change the behaviour of read-only connections.

The connection opened fine however the moment the app ran a SELECT statement
I think it coincided with a write from the the Plugin (separate process; it
opens a connection, writes and then closes it), boom the -shm file vanished
and only the .db and .db-wal files were present in the folder. The -wal was
zero bytes.

I have a feeling the -shm file got deleted by the plugin while it was in
fact in use by the main app (the main app only releases all its connections
when closing, so at any given time it has at least one open connection, even
when not being used).


What is the "safari share plugin"? The only reference to it on the 
internet seems to be here:


  
http://webcache.googleusercontent.com/search?q=cache:aIggi9ZiFkoJ:appshopper.com/mac/productivity/2do+=1=en=clnk=th

"Fixed an issue with the Safari share plugin, which would at times corrupt the 
database". A similar problem perhaps.

Is your database stored on a network file-system?

Have you read this?

  https://www.sqlite.org/howtocorrupt.html

Dan.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 15 Sep 2017, at 2:24pm, Fahad  wrote:

> #define SQLITE_ENABLE_STAT4 1

Should not affect your problem.

> I also changed this:
> 
> #define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 1000
> 
> to 250

That may affect your problem.  Try extreme values, with the objective being to 
trigger definite corruption.  That way you have a reliable demonstration of a 
bug you can post about.

Have you made any other changes to defaults, or used any PRAGMAs ?

I’m glad you found a way to minimise your problem.  I’m sorry I can help 
explain what’s actually going on.


Simon.
-- 
 http://www.bigfraud.org | I'd expect if a computer was involved
 | it all would have been much worse.
 No Buffy for you.   |-- John "West" McKenna
 Leave quickly now. -- Anya  |  THE FRENCH WAS THERE

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Hi Simon

Wanted to chime in to say I was able to in fact cause the database to go
corrupt from other external processes trying to write to the database (in
WAL mode) while the main app was also using the database. So I have a
feeling it's something else. I've tried hard to create a mini-app that
demonstrates this but have had no luck; it happens randomly but frequently
enough to warrant a red flag,

After 'stress' testing the app launched twice with various threads trying to
open and close the connection, I was able to crash the app once but for a
very different reason, something about a pointer being misaligned while
writing stats to disk. I had enabled this flag earlier and turning it off
seems to have helped:

#define SQLITE_ENABLE_STAT4 1

I also changed this:

#define SQLITE_DEFAULT_WAL_AUTOCHECKPOINT 1000

to 250

A smaller checkpoint seems to have either put a bandaid on the corruption
issue or I'm finding it increasingly difficult to corrupt the db. I've tried
launching 5 instances of the app while also trying to access and write from
the plugins as before - everything goes smoothly. No corruption.

Could the two changes I made above have had an impact somehow? Again, I'm
unable to reliably corrupt the db but I could sit and repeat the steps 20
times and have it go corrupt at least once before. Now I've been trying for
over two hours and it seems to be okay.

regards
Fahad



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 15 Sep 2017, at 4:38am, Fahad  wrote:

> How can I prevent this from happening? Like I said, using a unit test I was
> able to reproduce this 1 out of 20 tries but when using the real app and
> plugin at the same time, I am able to reproduce every 5th try (it seems the
> app is opening / using / closing connections in a peculiar way).

Was your unit test also using the Safari Plugin architecture ?

Have you every managed to reproduce the fault in a stand-alone program ?  It 
doesn’t need to be your complete App, just a simple test program which opens 
the file and does the INSERT.

Everything you write makes me think your problem is with the Safari Plugin, or 
the Safari architecture in general.  If that’s the case you may need to take 
the problem up with Apple’s developer forum, or using one of your free Apple 
Developer Membership support calls.

Simon.
-- 
 http://www.bigfraud.org | I'd expect if a computer was involved
 | it all would have been much worse.
 No Buffy for you.   |-- John "West" McKenna
 Leave quickly now. -- Anya  |  THE FRENCH WAS THERE

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Fahad]

Hi Simon

I tried it with sqlite3_shutdown(), didn't help. I was able to reproduce it
again using the main app and the safari share plugin.

Main App: Finished writing to db, just opened a new connection to read
(after opening a connection I set PRAGMA query_only=1; PRAGMA
read_uncommitted=1; and register some custom functions).

The connection opened fine however the moment the app ran a SELECT statement
I think it coincided with a write from the the Plugin (separate process; it
opens a connection, writes and then closes it), boom the -shm file vanished
and only the .db and .db-wal files were present in the folder. The -wal was
zero bytes.

I have a feeling the -shm file got deleted by the plugin while it was in
fact in use by the main app (the main app only releases all its connections
when closing, so at any given time it has at least one open connection, even
when not being used). 

How can I prevent this from happening? Like I said, using a unit test I was
able to reproduce this 1 out of 20 tries but when using the real app and
plugin at the same time, I am able to reproduce every 5th try (it seems the
app is opening / using / closing connections in a peculiar way).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 'database disk image is malformed' only on the mac

[Simon Slavin]

On 18 Aug 2017, at 12:30am, Fahad G  wrote:

> I do not have a way to reproduce this just yet, but I've been seeing way too 
> many diagnostic logs from customers where their databases are being corrupt 
> primarily on the Mac (the exact same code is shared between a Mac app, iPhone 
> and iPad) past several months - more so when I switched to WAL and started 
> dedicating a 'reader' connection for all reads, and a 'writer' for all 
> writes. 

I would say that I don’t see anything wrong with your compiler settings and 
PRAGMAs but that doesn’t mean much because I don’t know much about that stuff.

Would like to check and find out some facts.

At what point does your software detect this corruption ?  Is the database okay 
when it’s opened but get corrupted while he program is working ?  Or is the 
corruption in the file on disk and gets noticed immediately after opening ?  
This might help us figure out whether something in your program is stomping on 
SQLite3 memory.

This started happening several months ago ?  Let’s call it May.  Did you change 
development environments or versions of your Dev tools ?  Do you develop in 
Xcode ?  If so, did you start using a new version fo Xcode ?  Is your 
development computer using a stable version of the OS or the latest Developer 
Release we’re not meant to talk about ?

Are your customers using all the same version of macOS and iOS or are they 
varied ?

Does your application use sqlite3_shutdown() when it quits ?  If not, can you 
make this change ?

Do you check the value returned when you close a database connection and show 
an appropriate error message ?

> I read on the forums that mmap could be at fault (as I was using it). 
> Disabling it almost immediately felt that it solved the problem. However I'm 
> still occasionally now getting reports (weekly) of users running into a 
> "database disk image is malformed" error.

You are correct that use of mmap was (rarely) causing corruption and/or false 
reports of corruption.  Current versions of SQLite no longer use mmap because 
of this.  I’m see you’re using the latest SQLite amalgamation version.

The type of corruption done by this bug was not detectable immediately the file 
was opened.  It might only be noticed when the program tried to read a specific 
record or use a specific index.  Is it possible that your users who are still 
reporting corruption are still using databases which were corrupted earlier ?  
In other words the software is no longer corrupting databases but your users 
have 'legacy corruption' in their files ?

Hope some of this helps or another reader can help you.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] 'database disk image is malformed' only on the mac

[Fahad G]

Hi

Although I've read all the rules (and am otherwise aware of what it takes to 
report a bug), I want to apologise upfront. I do not have a way to reproduce 
this just yet, but I've been seeing way too many diagnostic logs from customers 
where their databases are being corrupt primarily on the Mac (the exact same 
code is shared between a Mac app, iPhone and iPad) past several months - more 
so when I switched to WAL and started dedicating a 'reader' connection for all 
reads, and a 'writer' for all writes. 

I have read and tried every possible combination of flags and setting up the 
connections, making sure (via numerous unit tests) that the code in question is 
working, thread safe etc. I recently also switched to SERIALIZED mode (compile 
time option) in hope that this would go away. When this started happening on a 
daily basis a coupe of months ago, I read on the forums that mmap could be at 
fault (as I was using it). Disabling it almost immediately felt that it solved 
the problem. However I'm still occasionally now getting reports (weekly) of 
users running into a "database disk image is malformed" error. I've asked one 
of the users to send us a copy of the corrupt database, but this isn't always 
possible (waiting on them).

I open for writing using:

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, , 
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX, NULL) == 
SQLITE_OK);

if (sqlite3_exec(dbConnection, "PRAGMA main.journal_mode=WAL; PRAGMA 
synchronous=normal;", NULL, NULL, NULL) != SQLITE_OK) {
 ...
}


And for reading:

BOOL dbOpened = (sqlite3_open_v2(path.UTF8String, , 
SQLITE_OPEN_FULLMUTEX | SQLITE_OPEN_READONLY | SQLITE_OPEN_WAL, NULL) == 
SQLITE_OK)


if (sqlite3_exec(readOnlyDB, "PRAGMA read_uncommitted=1; PRAGMA query_only=1; 
PRAGMA synchronous=normal;", NULL, NULL, NULL) != SQLITE_OK) {
 ...
}


I frequently would run VACUUM and ANALYZE but stopped doing that as well (in 
order to single this issue out), but am still seeing these error reports come 
in. 

I'm using the latest SQL amalgamation (v3.20.0) with the following compile time 
options:

#define SQLITE_ENABLE_FTS3 1
#define SQLITE_THREADSAFE 2
#define SQLITE_DEFAULT_MEMSTATUS 0
#define SQLITE_ENABLE_STAT4 1
#define SQLITE_MAX_MMAP_SIZE 0
#define SQLITE_OMIT_DEPRECATED 1
#define SQLITE_OMIT_SHARED_CACHE 1

Any help would be appreciated.

Thanks
Fahad
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database disk image is malformed

[Simon Slavin]

On 1 May 2016, at 2:39am, Igor Korot  wrote:

> How do I recover the information?

Do you have a copy of that file from before you updated ?  Can you run 
integrity_check() on it ?

Simon.

[sqlite] database disk image is malformed

[Igor Korot]

Simon,

On Sat, Apr 30, 2016 at 10:12 PM, Simon Slavin  wrote:
>
> On 1 May 2016, at 2:39am, Igor Korot  wrote:
>
>> How do I recover the information?
>
> Do you have a copy of that file from before you updated ?  Can you run 
> integrity_check() on it ?

IIUC, this file didn't change. It contains a project for Anjuta.

Thank you.

>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database disk image is malformed

[Igor Korot]

Hi,
I just updated my system along with the compiler from 4.x to 5.2.

And now I start receiving the aforementioned errors trying to start
Anjuta.

I tried to open the db file with sqlite3 - it opens, but running
"PRAGMA integrity_check" it immediately gives:

igor at IgorDellGentoo ~/.cache/anjuta $ sqlite3 .anjuta_sym_db.db
SQLite version 3.11.1 2016-03-03 16:17:53
Enter ".help" for usage hints
sqlite> PRAGMA integrity_check;
Error: database disk image is malformed
sqlite> .schema
Error: database disk image is malformed

How do I recover the information? Or even better - it would be nice to have the
information back.

Thank you.

Re: [sqlite] "database disk image is malformed" error occurs more (AGAIN, damage)

[Mario M. Westphal]

Had another damaged database report.

This time it is a configuration database which holds application settings. 

 

The file is stored on a local disk, not shared, and only ever accessed by my 
application and only by one thread.

The database is run in FULL sync mode for maximum security.

I’m puzzled. These files are very small, a few MB only.


The error message is

 

*** in database main ***Page 15: Rowid 3050 out of order (max larger than 
parent max of 3016)Page 128: Rowid 3017 out of order (min less than parent min 
of 3050) 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[James K. Lowden]

On Fri, 30 Jan 2015 13:17:26 -0500
Stephen Chrzanowski  wrote:

> 2.1 Filesystems with broken or missing lock implementations
> 
> SQLite depends on the underlying filesystem to do locking as the
> documentation says it will. But some filesystems contain bugs in their
> locking logic such that the locks do not always behave as advertised. 

The problem is even deeper than that.  NFS does not implement Posix
semantics.  The actual behavior is hard to reason about and far outside
SQLite's scope.  

On a local file system the kernel guarantees filebuffer cache
coherency.  If process A reads a block from the disk, and process B
modifies the same block, the next time process A consults that block it
will see B's changes.  Note this happens whether or not locking is
involved, and regardless of the state of the disk.  It's a by-product of
a unified buffer cache.  

On a network filesystem there is no unified buffer cache.  Writes by B
are not seen when A consults its cached block.  NFS does not promise
that a second read by A will reflect changes made by B.  Even if all
locks are implemented corrected and honored, A stands to read invalid
data unless steps are taken to manage the cache, something SQLite
doesn't do afaik.  

The subject has been discussed here before, as it turns out.  The
Googles returned
http://sqlite.1065341.n5.nabble.com/SQLite-on-NFS-cache-coherency-td33697.html,
which contains much more detail and references.  

DBMS implementations are always about arbitrating access to shared
data.  They require a single, unified view of the data.  Getting that
view over a remote filesystem is difficult in the best of circumstances
and requires explicit measures be taken.  SQLite doesn't attempt to
do so, by design.  (A reasonable choice IMO.)  If you want multi-node
access to a database over a network, there are many other options.
Unsurprisingly, none of them use a network filesystem either.  

HTH.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

As I wrote above, damaged databases are replaced. No user continues working 
with a damaged database once it has been identified. The issue here is to 
detect this early and avoid it altogether.

 

> One column of one row of one table may get corrupted.  

> If that's the case then the database can be used for years 

> without any problem being noticed.

> Theoretically "PRAGMA integrity_check" will notice it, however.

 

a) As I wrote above. 

b) integrity_check must find such issues. That’s how I understand it and 
Richard told me once.

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

1. No client-server, I use MySQL, SQL-Server or ORACLE for that.

 

2. No access to the SQLite database ever by more than one process concurrently 
in writable mode. In readable mode, yes. But the reported damage cases were 
always single user, one PC.

 

3. I cannot prevent or disallow users to keep their databases on NAS or remote 
server storage. Telling them that keeping a file on a NAS box will probably 
damage the file would be sales venom. 

Despite, the reported cases were all databases stored on local disks, except 
one. 

I keep databases between 0.5 and 10 GB on NAS (Linux/SAMBA) and Windows servers 
for my test scenarios and perf/load tests. No troubles, no corruption.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

I estimate that over 90% of the users keep the database on local disks. I can 
tell from the log files.

Keeping the SQLite database it on a network server really hurts performance. 
That’s not what SQLite is designed for, besides all other aspects of network 
locking mentioned in various SQLite docs. I use a MySQL or other RDBMS backend 
for such scenarios.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Stephen Chrzanowski]

On Fri, Jan 30, 2015 at 8:07 AM, Mario M. Westphal  wrote:

>
> When a user encounters the problem he/she restores the last working
> backup. I have a few users who faced this problem more than once. Here I
> always assumed some hardware glirch, a shaky USB connection, disk trouble,
> network problems (if the database is held on a Windows server or NAS),
> buggy SAMBA implementations or similar. Often, when asked, users
> ‘remembered’ a power failure, or some other problems with the disk or
> network. Case closed.
>

*Client/Server model*
It hasn't been mentioned by you yet, but if your software is acting in a
client/server model, ensure that your server is accessing the file LOCALLY
and not at a remote point.  Ensure that you're using the internal SQLite
threading tools and checking every single result for every single call in
the server software.  Do not ever let a remote client directly access to
the database file.

*NAS - Network Attached Storage*

If multiple users are accessing the file that lives on a different
computer, it is remote storage, which means NAS.  Any computer with any
share available on a network, that machine *IS* to be considered a NAS to a
remote machine.  Drobo, FTP, Windows, Unix/Linux,  CIFS/NFS/etc - Whatever
the protocol used, if what you're accessing isn't local to the computer, it
is a NAS.  Windows, Linux, and "Other" network protocols, be it 'buggy'
SAMBA or a Windows file share, it doesn't matter.  *ALL* are prone to
making SQLite have issues.  A single user using a single remote source
should be OK (But I wouldn't trust it), but the SECOND you start throwing
multiple connections at a remote file, you're begging, pleading, and even
offering your first born child to the computer Gods asking for data
problems.  The problem is NOT with Windows, and the problem isn't going to
show up in your event logs anywhere, but with the file sharing protocol
itself at the remote side, and even THAT machine won't make note of bad
file accesses or when a file is accessed.  The remote system isn't properly
releasing the necessary lock information to your computer, which is where
the problem is happening.

Directly from https://www.sqlite.org/howtocorrupt.html

--
2.0 File locking problemsSQLite uses file locks on the database file, and
on the write-ahead log or WAL file, to coordinate access between concurrent
processes. Without coordination, two threads or processes might try to make
incompatible changes to a database file at the same time, resulting in
database corruption.

2.1 Filesystems with broken or missing lock implementations

SQLite depends on the underlying filesystem to do locking as the
documentation says it will. But some filesystems contain bugs in their
locking logic such that the locks do not always behave as advertised. *This
is especially true of network filesystems and NFS in particular.* If SQLite
is used on a filesystem where the locking primitives contain bugs, and if
two or more threads or processes try to access the same database at the
same time, then database corruption might result.

--
{Highlighted by me}

Write your software to detect where the file is being loaded from.  If your
software is written for Windows, it is SIMPLISTIC to find out what kind of
drive you're accessing a file from, and it is even MORE simplistic to find
out if you're accessing a file via a UNC (\\system\share) by just looking
at what the full file path your program is loading the file from.  I've
never coded anything under a 'Nix system except for scripts, but there
should be a way to find out if the path you're accessing is remote by
looking at /etc/fstab (or equiv) and track from there.  The moment your
software sees a that it is accessing something OTHER than a file "local to
the computer, be it HDD/SDD/USB", warn the user of possible data
corruption, log that the attempt was made, and go from there on whatever
path you want to proceed.  Proceed with systems running as usual, or, deny
access to the file, or close out of the application entirely.

Confirm, with ABSOLUTE CERTAINTY, that database files that are being used
are on local storage devices and validate that if these files ARE being
accessed locally, THEN maybe start digging into different kinds of
corruption problems.  Removing a machine from your process is going to make
things MUCH easier to diagnose.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Simon Slavin]

On 30 Jan 2015, at 1:07pm, Mario M. Westphal  wrote:

> What worries me more are the incidents where users see this problem happen 
> several times, with q database kept on a local hard disk or SSD.

Just to make it clear, when corruption is reported, the corruption is not 
automatically fixed.  The database will still be corrupt, and as the app 
continues it may notice the same corruption again and report it again.  This is 
why I asked you whether you are noticing more corruption or are just continuing 
to use a corrupt database.

So yes, if the user continues to use the same database, they'll get more error 
messages.  And if they restore a backup it might be a good idea to check to see 
whether that backup is corrupt.  At least until you have tracked down the cause 
of your corruption and stopped it.

> that’s really hard to tell, because unless SQLite has to access a corrupted 
> section of the file during normal operation, or integrity_check() is run, a 
> damaged database may behave perfectly normal for a long time...


One column of one row of one table may get corrupted.  If that's the case then 
the database can be used for years without any problem being noticed.  
Theoretically "PRAGMA integrity_check" will notice it, however.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

> Okay.  First, stop doing VACUUM after this.  You're not improving things and 
> you may be making things worse

 

Not important. If this error is encountered the database is marked and the user 
reminded on every open/close to replace it with a backup. The database is not 
supposed to be used after SQLite has reported it as corrupt.

 

When a user encounters the problem he/she restores the last working backup. I 
have a few users who faced this problem more than once. Here I always assumed 
some hardware glirch, a shaky USB connection, disk trouble, network problems 
(if the database is held on a Windows server or NAS), buggy SAMBA 
implementations or similar. Often, when asked, users ‘remembered’ a power 
failure, or some other problems with the disk or network. Case closed.

 

 

What worries me more are the incidents where users see this problem happen 
several times, with q database kept on a local hard disk or SSD. The Windows 
event log shows no reports about anything disk related. No power failure. No 
hard shut-down. No problems reading or writing data in other applications.

 

The database may be several months old or fresh. The error is sometimes 
encountered during a diagnosis run (with integrity_check) or a 
SELECT/INSERT/UPDATE suddenly returns the dreaded SQLITE_CORRUPT error code. 
This can happen for databases with 200 MB or databases with 10 GB. It 
apparently does not necessarily happen during times of high activity or bulk 
inserts. But that’s really hard to tell, because unless SQLite has to access a 
corrupted section of the file during normal operation, or integrity_check() is 
run, a damaged database may behave perfectly normal for a long time...

 

I have now implemented the ErrorCallback routine and future versions will log 
anything reported that way to the log file. Maybe this gives us some more data 
to work with. I assume that this function is safe to use in a scenario where 
multiple instances/connections of SQLite are in use in parallel? My application 
uses multiple threads, but each thread uses a separate instance of SQLite.

 

-- Mario

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[RSmith]

On 2015/01/30 14:45, Mario M. Westphal wrote:

- The databases in question are stored on a location hard disk or SSD.

- If a user stores his database on a NAS box or Windows server, it is accessed 
directly, via standard Windows file system routines.

- From what I can tell, network-based databases are not more likely to corrupt 
than databases stored on built-in disks or SSDs or databases kept on disks or 
USB sticks connected via USB.


That is simply not true. The report-back on locking success via a local resource (albeit for a removable drive) is under normal 
circumstances absolute and correct. For a network file (remote) source, that is just not true in near all network cases.  If you can 
be sure only one instance of your program access it over the network and nothing else, then it should not be harmed, but this is 
difficult.


Users kill their processes and re-start programs and SQLite connections (unwittingly) that finds hot roll-back journals and all 
kinds of things that might fall into a long "busy" cycle which may again prompt a process-kill, etc.


It's easy to tell though, when you get reports of corruption, require the file location information. A pattern should quickly emerge 
if this is a networking problem.



- My software is updated every 2 to 4 weeks, and I always include and ship with 
the latest SQLite version.

- There is a big variance in when users update so some users may work with 
versions several months old, but not older than 2 months, typically.

- A user may access a database from multiple computers, but then only in 
read-only mode. Write access is only permitted when the database is opened in 
exclusively.

- I use SQLite since about 2008, but the code base is changed frequently. I 
maintain old databases (up to maybe one year old and use them in regression 
tests before shipping).



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Stephan Beal]

On Fri, Jan 30, 2015 at 1:45 PM, Mario M. Westphal  wrote:

> - From what I can tell, network-based databases are not more likely to
> corrupt than databases stored on built-in disks or SSDs or databases kept
> on disks or USB sticks connected via USB.
>

That's a big assumption. Network filesystems are historically _notorious_
for locking-related problems (the root of many corruption problems).

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

- The databases in question are stored on a location hard disk or SSD.

- If a user stores his database on a NAS box or Windows server, it is accessed 
directly, via standard Windows file system routines.

- From what I can tell, network-based databases are not more likely to corrupt 
than databases stored on built-in disks or SSDs or databases kept on disks or 
USB sticks connected via USB.

- My software is updated every 2 to 4 weeks, and I always include and ship with 
the latest SQLite version. 

- There is a big variance in when users update so some users may work with 
versions several months old, but not older than 2 months, typically.

- A user may access a database from multiple computers, but then only in 
read-only mode. Write access is only permitted when the database is opened in 
exclusively.

- I use SQLite since about 2008, but the code base is changed frequently. I 
maintain old databases (up to maybe one year old and use them in regression 
tests before shipping). 

 

-- Mario

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Simon Slavin]

On 29 Jan 2015, at 7:04pm, Mario M. Westphal  wrote:

> The diagnosis log of my application reports the output of integrity_check() 
> already.
> 
> I retrieved the log from the most recent error report. This is my application 
> has logged:
> 
> '*** IN DATABASE MAIN ***
> ON TREE PAGE 385120 CELL 24: INVALID PAGE NUMBER 151192068
> CORRUPTION DETECTED IN CELL 24 ON PAGE 385120
> CORRUPTION DETECTED IN CELL 25 ON PAGE 385120
> MULTIPLE USES FOR BYTE 1612 OF PAGE 385120
> FRAGMENTATION OF 30 BYTES REPORTED AS 0 ON PAGE 385120'

Okay.  First, stop doing VACUUM after this.  You're not improving things and 
you may be making things worse.

Second, a corrupt database may remain corrupt.  So we try to distinguish 
between (A) and (B):

A) Something corrupted my database but that was just once and it has never 
happened again
B) Something is continually corrupting my database.

So have you tried replacing that database with one which isn't corrupt and 
seeing whether the new 'clean' one somehow becomes corrupt ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Stephen Chrzanowski]

On Thu, Jan 29, 2015 at 2:07 PM, Mario M. Westphal  wrote:


> Most database damaged errors encountered over time could be pinned to
> power failures, disk or *network problems*.
>
> 


Network problems?  I might have missed a good chunk of this thread, but,
this begs to be asked Are you running a client/server model in which
the server is the ONLY machine accessing the database file, or, do you have
multiple machines touching the file via a network share?  If you're running
multiple machines talking via a network interface directly to the database,
you need to stop, ESPECIALLY with the up in frequency you seem to be
running into this problem.  I did note you did read the "How To Corrupt"
page, but you may have missed the whole networking thing that shouldn't be
done.

If you're running client/server in that a client opens a custom network
protocol to a server application, and the server application touches the
database BY ITSELF, then you need to look at what the hardware is doing
between the application and the storage device.

I can't say for certain, and maybe Dr Hipp and others will need to get
involved in looking at the low level SQLite code base, but if YOUR code
base code is from 2008, and it is now 2015, and you've got applications
talking with a single source (Meaning one customer = one source of their
own data) with different versions of the SQLite code, *MAYBE* you're
looking at an older and bugged version of SQLite that is doing one thing to
the raw data while a working version comes back and informs you with the
"WTF?" errors.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

The core code is in place since about 2008. 

 

I took advantage of changes in SQLite over time, from using the shared cache to 
switching to WAL mode for databases which are not opened in read-only mode.

These changes were made between 12 and six months ago, and tested during beta 
tests and also in the wild. 

Most database damaged errors encountered over time could be pinned to power 
failures, disk or network problems. 

But a too high number of recent reports (couple of months) could not be linked 
to any hardware problem or power failure. 

 

My application uses multiple concurrent threads, but each thread works with its 
own instance of SQLite (on the same database). Transactions are used to improve 
performance and for control 

flow. Every error returned by SQLite is logged to the log file. If a SQLite 
function returns the dreaded “disk image malformed” error, my application 
immediately stores that error and remembers it – the database is marked as 
defective and the user is notified as soon as possible.

 

My users run daily backups of all their important data, including the database 
so usually they can roll-back to the last known working backup and continue. 

 

I will implement Richard’s suggestions to gather more info to the log file. The 
next time a user reports the problem, we may get extra hints about why and when 
this happened.

 

Thanks for the great support and advice.

 

-- Mario

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

The diagnosis log of my application reports the output of integrity_check() 
already.

I retrieved the log from the most recent error report. This is my application 
has logged:

 

'*** IN DATABASE MAIN ***

 

ON TREE PAGE 385120 CELL 24: INVALID PAGE NUMBER 151192068

 

CORRUPTION DETECTED IN CELL 24 ON PAGE 385120

 

CORRUPTION DETECTED IN CELL 25 ON PAGE 385120

 

MULTIPLE USES FOR BYTE 1612 OF PAGE 385120

 

FRAGMENTATION OF 30 BYTES REPORTED AS 0 ON PAGE 385120'

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Mario M. Westphal]

My application does not phone home :-/ but I can add output of these functions 
to the log file my application maintains. My users know how to collect these 
log files and send them to me.

I will also add the error logging callback to my wrapper class and route it to 
the log file.

 

This should give additional information in case these errors repeat. I will do 
that right away and ship this with the next update. Will take a couple of weeks 
to saturate the user basis.

 

 

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[RSmith]

On 2015/01/28 20:06, Mario M. Westphal wrote:

1. I don’t have the damaged databases here so I cannot run the diagnosis 
myself. The databases are usually too large to upload or transfer.

2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation 
and Visual Studio 2012).
But since not every user always keeps up to day, older versions of SQLite are 
also in use, some maybe 3 to 4 four months old.


Hi Mario,

Thank you for being specific. I have to ask, what were the changes you implemented in your application a few months ago?  SQLite 
seems to not be in general worse for wear, but almost weekly you are getting serious problems (in DB terms) cropping up - and this 
only started some months ago. The common denominator seems to be "some months ago", so your system may have changed in a way that 
somehow facilitates the error.


Of course you already pointed this out, so it's understood, but my aim is that: if you could list the changes you have made recently 
in general  and maybe specific to SQLite usage, we might better guess at which things to check or recognise similarities with 
problems we've faced.  That said, there are not many design choices that might cause "Database malformed" errors and since you are 
already familiar with all the documentation, we could assume you would have noticed anything obvious.


All this makes it very hard to guess. Getting specific logs with the improved error reporting would be helpful as Richard suggested, 
or making your app "phone home".  Are the come-backs all random? Do you have your own server running a user version or test version 
under full load at your own offices perhaps?


Also, getting one Malformed Database a week out of how many? 5, 500, 500 000?  (Not that it changes anything, there should be no 
incidents, but it might tell us something about the prevalence). Does your system  have a back-up mechanism? (The DB sizes you 
describe seem to suggest you would shy away from an auto-multi-backup scenario).



I know all of the above do not help directly, but this error seems strange so I 
am simply prompting for more information.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more

[Richard Hipp]

On 1/28/15, Mario M. Westphal  wrote:
> 1. I don’t have the damaged databases here so I cannot run the diagnosis
> myself. The databases are usually too large to upload or transfer.
>
> 2. The SQLite version I currently use is 3.8.8.1 (complied using the
> Amalgation and Visual Studio 2012).
> But since not every user always keeps up to day, older versions of SQLite
> are also in use, some maybe 3 to 4 four months old.
>
>
> 2. Sorry for being not more specific. With “running analysis” I meant that
> may application runs a
>
> PRAGMA integrity_check(100)
>
> after running a wide range of logical checks which checks the data stored in
> the database itself.

Can you adjust your application to "phone home" with the results of
"SELECT sqlite_source_id(); PRAGMA integrity_check;" when you find a
problem?

Please also consider activating the Error and Warning Log
(https://www.sqlite.org/errlog.html) and having your application phone
home anomalies detected there too.

>
> My diagnosis routine then runs ANALYZE for good measure and because the data
> in some of the large tables may change over time a lot.
> If an error has been found by integrity_check(), the diagnosis runs a
> REINDEX operation because this could save the database sometimes in the
> past.
> As the final step of the diagnosis routine, my application runs a VACUUM to
> compress the database (I run the database with auto_vacum=OFF).
>
>
> 3.  The "disk image is malformed" error is often encountered during normal
> processing, when one or more SQLite functions return the error e.g. during
> adding large amounts of data. Since my application often works in an
> unattended mode, it records such errors, logs them into a log file and then
> informs the user at the earliest opportunity. I know that an "disk image is
> malformed" may go unnoticed for a long time, especially if the user does not
> run the diagnosis routines frequently.
>
> 3. I have read  https://www.sqlite.org/howtocorrupt.html of course. This is
> what I refer to as "rule book of how to damage your SQLite database".
>
> -- Mario
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
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] "database disk image is malformed" error occurs more

[Mario M. Westphal]

1. I don’t have the damaged databases here so I cannot run the diagnosis 
myself. The databases are usually too large to upload or transfer.

2. The SQLite version I currently use is 3.8.8.1 (complied using the Amalgation 
and Visual Studio 2012).
But since not every user always keeps up to day, older versions of SQLite are 
also in use, some maybe 3 to 4 four months old.


2. Sorry for being not more specific. With “running analysis” I meant that may 
application runs a 

PRAGMA integrity_check(100)

after running a wide range of logical checks which checks the data stored in 
the database itself. 

My diagnosis routine then runs ANALYZE for good measure and because the data in 
some of the large tables may change over time a lot. 
If an error has been found by integrity_check(), the diagnosis runs a REINDEX 
operation because this could save the database sometimes in the past.
As the final step of the diagnosis routine, my application runs a VACUUM to 
compress the database (I run the database with auto_vacum=OFF).


3.  The "disk image is malformed" error is often encountered during normal 
processing, when one or more SQLite functions return the error e.g. during 
adding large amounts of data. Since my application often works in an unattended 
mode, it records such errors, logs them into a log file and then informs the 
user at the earliest opportunity. I know that an "disk image is malformed" may 
go unnoticed for a long time, especially if the user does not run the diagnosis 
routines frequently.

3. I have read  https://www.sqlite.org/howtocorrupt.html of course. This is 
what I refer to as "rule book of how to damage your SQLite database".

-- Mario



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more frequently...?

[Simon Slavin]

On 28 Jan 2015, at 3:15pm, Mario M. Westphal  wrote:

> The damage is usually detected during “diagnosis” runs. This feature runs an 
> “analyze” and a” vacuum” command in order to physically validate the database 
> (and to optimize and compact it).

Please don't do that.  Neither of those things does any diagnosis on the 
database.  And they both make changes which can complicate damage or cover it 
up and let it happen again.

The command

PRAGMA integrity_check

checks your database for faults.  That's the way to detect faults and tell 
whether there really is a problem with your database.



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more frequently...?

[Stephan Beal]

On Wed, Jan 28, 2015 at 4:19 PM, Richard Hipp  wrote:

> On 1/28/15, Mario M. Westphal  wrote:
> > Recently I get an increasing number of error reports about “database disk
> > image malformed” errors from my users. These errors show up out of the
> blue,
> > with databases held on local hard disks or even SSD’s, no power failures,
> > Windows crashes or anything that’s in the rule book of “How to damage
> your
> > SQLite database”.
> >
>
> This shouldn't happen.  (But read
> https://www.sqlite.org/howtocorrupt.html for more information).
>


To paste in part of a recent thread from the Fossil list which _might_ be
relevant here...


On Sat, Jan 24, 2015 at 10:10 PM, Richard Hipp  wrote:

> On 1/24/15, Richard Hipp  wrote:
> > On 1/24/15, Michai Ramakers  wrote:
> ...>>   SQLITE_CORRUPT: database corruption at line 53682 of [1412fcc480]
> >
>
> Actually, Fossil version 331204dc93 contained a dodgy version of
> SQLite which could generate corruption reports such as the above.  The
> database was not really corrupt.  The error was in the corruption
> detection mechanism.  That error has long since been fixed.



-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" error occurs more frequently...?

[Richard Hipp]

On 1/28/15, Mario M. Westphal  wrote:
> Hello,
>
>
>
> I’m using SQLite in one of my applications for several years with great
> success.
>
> The databases managed with SQLite are between 1 and maybe 10 GB, with about
> 50 tables or so.
>
> The platform is Windows 7 or higher.
>
>
>
> Recently I get an increasing number of error reports about “database disk
> image malformed” errors from my users. These errors show up out of the blue,
> with databases held on local hard disks or even SSD’s, no power failures,
> Windows crashes or anything that’s in the rule book of “How to damage your
> SQLite database”.
>

This shouldn't happen.  (But read
https://www.sqlite.org/howtocorrupt.html for more information).

Can you provide additional information?
*   Exactly which version (sqlite_source_id()) of SQLite you are running.
*   The output from the "PRAGMA quick_check;" and/or "PRAGMA
integrity_check;" commands.



>
>
> The damage is usually detected during “diagnosis” runs. This feature runs an
> “analyze” and a” vacuum” command in order to physically validate the
> database (and to optimize and compact it).
>
>
>
> Are there any settings/options I can check and which are known to increase
> the likelihood of physical database damage?
>
>
>
> + I always use the most recent version of SQLite.
>
> + I switched to using WAL mode during a larger update about a year ago.
>
> + I use syncmode=NORMAL for a good balance between speed and security.
>
> + I have PRAGMA wal_autocheckpoint=2 to speed up bulk inserts (this tip
> came from drh).
>
> + I use nested transactions implemented via checkpoints
>
>
>
> Anything I need to look for or check?
>
>
>
> I was under the impression that physical damage is very unlikely and only
> happens under well-known conditions. Maybe something has changed in recent
> SQLite builds that somehow causes this to happen more often? I recall that
> physical damage was really, really rare over the past years – but now I get
> reports maybe once a week…
>
>
>
> Thanks for reading and your ideas and comments.
>
>
>
> -- Mario
>
>
>
>
>
>
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "database disk image is malformed" error occurs more frequently...?

[Mario M. Westphal]

Hello,

 

I’m using SQLite in one of my applications for several years with great success.

The databases managed with SQLite are between 1 and maybe 10 GB, with about 50 
tables or so.

The platform is Windows 7 or higher.

 

Recently I get an increasing number of error reports about “database disk image 
malformed” errors from my users. These errors show up out of the blue, with 
databases held on local hard disks or even SSD’s, no power failures, Windows 
crashes or anything that’s in the rule book of “How to damage your SQLite 
database”.

 

The damage is usually detected during “diagnosis” runs. This feature runs an 
“analyze” and a” vacuum” command in order to physically validate the database 
(and to optimize and compact it).

 

Are there any settings/options I can check and which are known to increase the 
likelihood of physical database damage?

 

+ I always use the most recent version of SQLite.

+ I switched to using WAL mode during a larger update about a year ago. 

+ I use syncmode=NORMAL for a good balance between speed and security.

+ I have PRAGMA wal_autocheckpoint=2 to speed up bulk inserts (this tip 
came from drh).

+ I use nested transactions implemented via checkpoints

 

Anything I need to look for or check?

 

I was under the impression that physical damage is very unlikely and only 
happens under well-known conditions. Maybe something has changed in recent 
SQLite builds that somehow causes this to happen more often? I recall that 
physical damage was really, really rare over the past years – but now I get 
reports maybe once a week…

 

Thanks for reading and your ideas and comments.

 

-- Mario

 

 

 

 

 

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[David Barrett]

In my experience, retrying does often work.

-david

On Tue, Sep 25, 2012 at 7:31 PM, Rittick Gupta  wrote:

> Do you think a retry of the query would help resolve this issue ? Do I
> have to close & reopen the database.
>
> Thanks for your help.
>
> regards,
>
> Rittick
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Rittick Gupta]

Do you think a retry of the query would help resolve this issue ? Do I have to 
close & reopen the database. 

Thanks for your help.

regards,

Rittick
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[David Barrett]

Yes, still having that problem. We've moved to new servers with SSD's and a
ton of RAM, and that seems to have helped matters -- not sure why, though I
don't know why it was happening in the first place.  (I'm *guessing* the
issue is due to two conflicting queries happening at the same time, so if
the queries happen faster the probability of collision goes down?)

-david

On Tue, Sep 25, 2012 at 6:39 PM, Rittick Gupta  wrote:

> David,
>   Thanks. Do you still have the problem ? Did you find a workaround to
> avoid the problem - appreciate your response.
>
> regards,
>
> Rittick
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[Rittick Gupta]

David,
  Thanks. Do you still have the problem ? Did you find a workaround to 
avoid the problem - appreciate your response.

regards,

Rittick

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

[David Barrett]

You might be interested in this thread:

http://www.theusenetarchive.com/usenet-message-sqlite-does-disabling-synchronous-and-shared-cache-cause-%22error-database-disk-image-is-malformed%22-20780199.htm

No conclusion was ever obtained, but the discussion was good.

-david

On Mon, Sep 24, 2012 at 4:55 PM, Rittick Gupta  wrote:

> I have a mult-threaded application. Each thread uses its own database
> handle. One thread got a return value of 11 (disk image malformed) - with
> the sqlite3_step statement (select statement). When we restarted the
> application after the failure the database recovered and did not give any
> error.
>
> Why did the "sqlite3_step" statement returned a "disk malformed" error
> when the disk is not corrupt ?
>
> Any help will be appreciated.
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Simon Slavin]

On 23 Jan 2012, at 9:45am, Sreekumar TP wrote:

> -My journal mode is set to WAL, synchronous mode is NORMAL and Checkpoint
> mode is Manual.
> 
> Transactions are written to the WAL file, therefore, even if the WAL file
> is lost, I should be able to retrieve the database without
> corruption.(sacrificing some data ofcourse)   ?

The simplest way to get an uncorrupted database is to use the command-line tool 
to .dump your database file to SQL commands, then use the command-line tool to 
run those commands to make another database file.  This does not guarantee you 
a database that makes sense unless you used transactions correctly.  But it 
does guarantee you a database which will pass the corruption-check PRAGMAs.

> Moreover, I do know that there was no checkpoint in progress when the power
> was turned OFF. Hence  syncs should not be a problem ?

If you were using a modern-day standard computer then you have no idea when the 
computer was updating the hard disk.  All sorts of foreground tasks are running 
which might have held it up: print service, checking for new email, etc..  What 
you can hope for is that changes are written in the order they were made, which 
is something SQLite relies on to keep its database files uncorrupted.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/01/12 01:45, Sreekumar TP wrote:
> Transactions are written to the WAL file, therefore, even if the WAL
> file is lost, I should be able to retrieve the database without 
> corruption.(sacrificing some data ofcourse)   ?

Correct.  A checkpoint will end up modifying the main database file.

However you need to identify how you got the corruption in the first place
otherwise it will happen again.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8dP4oACgkQmOOfHg372QQdgACguIlWFuXuV3GPIfVWugf5RdbE
oEAAnAhXDR6JH7W5nKCraNKYnpFEfYkl
=VPyM
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Sreekumar TP]

-My journal mode is set to WAL, synchronous mode is NORMAL and Checkpoint
mode is Manual.

Transactions are written to the WAL file, therefore, even if the WAL file
is lost, I should be able to retrieve the database without
corruption.(sacrificing some data ofcourse)   ?

Moreover, I do know that there was no checkpoint in progress when the power
was turned OFF. Hence  syncs should not be a problem ?

-Sreekumar





On Mon, Jan 23, 2012 at 3:00 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 23/01/12 01:03, Sreekumar TP wrote:
> > is it possible to find out if sqlite was in the middle of a transaction
> > when the power off occurred ?
>
> WAL mode has the transactions in a separate file.  Normal mode alters the
> database and keeps the original data in a rollback journal.
>
> However unless you identify the underlying cause (eg disk lying about
> syncs) it won't help since you have to assume all data you see is a lie.
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk8dKLYACgkQmOOfHg372QTuBQCgwFLnZ5108QRfelWm11qDiRjY
> szYAn0tgEw0klQqRllRSyepezxt550ha
> =SRbO
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/01/12 01:03, Sreekumar TP wrote:
> is it possible to find out if sqlite was in the middle of a transaction
> when the power off occurred ?

WAL mode has the transactions in a separate file.  Normal mode alters the
database and keeps the original data in a rollback journal.

However unless you identify the underlying cause (eg disk lying about
syncs) it won't help since you have to assume all data you see is a lie.

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8dKLYACgkQmOOfHg372QTuBQCgwFLnZ5108QRfelWm11qDiRjY
szYAn0tgEw0klQqRllRSyepezxt550ha
=SRbO
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Sreekumar TP]

Yes, this is a case of corruption. The problem occurred during a power
cycle test.  I have the synchronous mode set to NORMAL.
By examining the header of the DB ,is it possible to find out if sqlite was
in the middle of a transaction when the power off occurred ?

-Sreekumar


On Mon, Jan 23, 2012 at 2:21 PM, Roger Binns  wrote:

> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 23/01/12 00:13, Sreekumar TP wrote:
> > What can I infer from these logs ?
>
> Your database is corrupted.  Here is a list of possible causes:
>
>  http://www.sqlite.org/howtocorrupt.html
>
> Roger
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.11 (GNU/Linux)
>
> iEYEARECAAYFAk8dH4kACgkQmOOfHg372QThhwCgyPS+Sl28MEMHln4I6iRcm5VL
> 2ZkAn0tIpqfbGbkIZwUs3myTbwzv+jdw
> =Czsj
> -END PGP SIGNATURE-
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Roger Binns]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 23/01/12 00:13, Sreekumar TP wrote:
> What can I infer from these logs ?

Your database is corrupted.  Here is a list of possible causes:

  http://www.sqlite.org/howtocorrupt.html

Roger
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.11 (GNU/Linux)

iEYEARECAAYFAk8dH4kACgkQmOOfHg372QThhwCgyPS+Sl28MEMHln4I6iRcm5VL
2ZkAn0tIpqfbGbkIZwUs3myTbwzv+jdw
=Czsj
-END PGP SIGNATURE-
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database disk image is malformed

[Sreekumar TP]

Hi,

I have get the following error when a sql statment is executed ( query ) -
"database disk image is malformed".

On running the quick_check pragma on the db, I get the following log -

"*** in database main ***
On tree page 1595 cell 5: Rowid 2104 out of order (max larger than parent
max of 2068)
On tree page 1597 cell 4: Rowid 2331 out of order (max larger than parent
max of 2070)
On tree page 1620 cell 1: Rowid 5412 out of order (max larger than parent
max of 2072)
On tree page 1621 cell 1: Rowid 2073 out of order (min less than parent min
of 5412)
On tree page 1762 cell 1: Rowid 5409 out of order (max larger than parent
max of 2318)
Page 1766: btreeInitPage() returns error code 11
On tree page 541 cell 21: Child page depth differs
Page 1127: btreeInitPage() returns error code 11
On tree page 1413 cell 0: 2nd reference to page 1127
On tree page 1413 cell 1: Rowid 5418 out of order (max larger than parent
max of 2331)
On tree page 541 cell 23: Child page depth differs
On tree page 1812 cell 0: Rowid 2332 out of order (min less than parent min
of 5418)
Page 1802 is never used
Page 1806 is never used"


What can I infer from these logs ?


-Sreekumar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Gorshkov]

I'm having the same problem with my application.

Basically, it's a combination of jukebox/music management app I've been 
developing myself over the last few years. I had always used the dump 
commands to back up & restore the database, given that I develop on both 
linux and windows. When the backup commands were introduced, I switched 
them them. Things have been working fine.

General procedure is:
- backup database under windows, to my server
- reboot to linux
- restore from windows backup
- work
- backup database to my server
- reboot to windows
- restore
- work

and so on.
Until a week or so ago, both linux and windows were running 3.6.14. 
Recent upgrade of my gentoo system, however, upgraded sqlite there to 3.7.2

Now what I get is the following:

-backup database under windows to my server
- reboot to linux
- restore from windows backup
when I try to update or do anything in the database, my app crashes with 
the malformed database error.

If I nuke the linux database file, re-create from scratch instead of 
backup restore and load the virgin file from a dump, everything is ok.

On 2011-02-09 13:25, Dan Kennedy wrote:
>
>> I didn't find a way yet to reproduce the issue with a "clean" database.
>> Only way I can reproduce it is with some of the database, like the
>> test.db3. So I'm running out of ideas.
>
> This is the theory. test.db3 is an auto-vacuum database.
>
> http://www.sqlite.org/src/info/89b8c9ac54
>
> Dan.
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: woensdag 9 februari 2011 19:26
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database disk image is malformed 3.7.x
> 
> 
> > I didn't find a way yet to reproduce the issue with a "clean"
> database.
> > Only way I can reproduce it is with some of the database, like the
> > test.db3. So I'm running out of ideas.
> 
> This is the theory. test.db3 is an auto-vacuum database.
> 
>http://www.sqlite.org/src/info/89b8c9ac54
> 
> Dan.
> 

You are correct, the test.db3 is the only database with the PRAGMA
AUTO_VACUUM 1. Looking at the ticket, it looks likely that this issue is
indeed triggered. 

Only thing I don't know, is how this PRAGMA changed. Can't remember that
this is set in any way, maybe a viewer tool changed it... 

Thanks for helping!
Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

> I didn't find a way yet to reproduce the issue with a "clean" database.
> Only way I can reproduce it is with some of the database, like the
> test.db3. So I'm running out of ideas.

This is the theory. test.db3 is an auto-vacuum database.

   http://www.sqlite.org/src/info/89b8c9ac54

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/10/2011 12:10 AM, Dan Kennedy wrote:
> On 02/09/2011 08:17 PM, Dennis Geldhof wrote:
>> I checked some things for the attached database. It is created with
>> sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite
>> wrapper. The header displays (with the ./showdb) version 3.7.4, so it is
>> opened and changed with a database viewer tool. Before executing the
>> "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after
>> executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is
>> executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It
>> does not matter if the database was encrypted while executing the
>> "query", the result stays the same.
>
> Thanks for doing this. It sounds like it is worth trying to figure
> out what makes this db special.
>
> Are you able to make the test.db3 database available for download or
> mail it to me directly? This mailing list strips out attachments.

You already did that... My mistake.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/09/2011 08:17 PM, Dennis Geldhof wrote:
> I checked some things for the attached database. It is created with
> sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite
> wrapper. The header displays (with the ./showdb) version 3.7.4, so it is
> opened and changed with a database viewer tool. Before executing the
> "query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after
> executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is
> executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It
> does not matter if the database was encrypted while executing the
> "query", the result stays the same.

Thanks for doing this. It sounds like it is worth trying to figure
out what makes this db special.

Are you able to make the test.db3 database available for download or
mail it to me directly? This mailing list strips out attachments.

Dan.





>
> The "query" is used to change some table structures in the database and
> executes the following actions on the database;
> BEGIN TRANSACTION;
>
> ALTER TABLE [A] RENAME TO [TMP_A]
> ALTER TABLE [B] RENAME TO [TMP_B]
>
> CREATE TABLE [A] (); -- where A is a new table, stripped columns
> CREATE TABLE [B] (); -- where B is a new table, stripped columns
> CREATE TABLE [C] (); -- stripped columns
>
> INSERT INTO [A]
> SELECT
>   (SELECT [ColumnA] FROM [D] WHERE [ColumnB] = [TMP_A].[ColumnB]),
>   (SELECT [ColumnC] FROM [TMP_B] WHERE ([ColumnD] =
> [TMP_A].[ColumnD]) AND (NOT [ColumnC] ISNULL) LIMIT 1),
>   -- stripped more columns
> FROM [TMP_A];
>
> -- stripped some more INSERT INTO [] SELECT
> -- stripped some UPDATE
>
> DROP TABLE [TMP_A];
> DROP TABLE [TMP_B];
> DROP TABLE [D];
> DROP TABLE [E];
> DROP TABLE [F];
>
> COMMIT TRANSACTION;
>
>
> When omitting the DROP TABLE [TMP_B], the 3.7.4 does not detect
> corruption directly, until some more updates are executed on the
> database.
>
> When the database before the "query" is .dump-ed to file, and .read into
> a new 3.6.23.1 database, the corruption does not become visible after
> the "query". So it seems to be in the header of that database.
>
> I didn't find a way yet to reproduce the issue with a "clean" database.
> Only way I can reproduce it is with some of the database, like the
> test.db3. So I'm running out of ideas.
>
> Dennis
>
> This message contains confidential information and is intended only for the 
> individual named. If you are not the named addressee you should not 
> disseminate, distribute or copy this e-mail. Please notify the sender 
> immediately by e-mail if you have received this e-mail by mistake and delete 
> this e-mail from your system. E-mail transmission cannot be guaranteed to be 
> secure or error-free as information could be intercepted, corrupted, lost, 
> destroyed, arrive late or incomplete, or contain viruses. The sender 
> therefore does not accept liability for any errors or omissions in the 
> contents of this message, which arise as a result of e-mail transmission. If 
> verification is required please request a hard-copy version.
>   Please consider the environment before printing this email message
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

I checked some things for the attached database. It is created with
sqlite version 3.6.3 or 3.6.23.1 with the help of the system.data.sqlite
wrapper. The header displays (with the ./showdb) version 3.7.4, so it is
opened and changed with a database viewer tool. Before executing the
"query" the database integrity is OK in both 3.6.23.1 and 3.7.4, after
executing it is OK for 3.6.23.1 but malformed for 3.7.4. The "query" is
executed using the system.data.sqlite wrapper (sqlite v3.6.23.1). It
does not matter if the database was encrypted while executing the
"query", the result stays the same. 

The "query" is used to change some table structures in the database and
executes the following actions on the database;
BEGIN TRANSACTION;

ALTER TABLE [A] RENAME TO [TMP_A]
ALTER TABLE [B] RENAME TO [TMP_B]

CREATE TABLE [A] (); -- where A is a new table, stripped columns
CREATE TABLE [B] (); -- where B is a new table, stripped columns
CREATE TABLE [C] (); -- stripped columns

INSERT INTO [A]
SELECT
(SELECT [ColumnA] FROM [D] WHERE [ColumnB] = [TMP_A].[ColumnB]),
(SELECT [ColumnC] FROM [TMP_B] WHERE ([ColumnD] =
[TMP_A].[ColumnD]) AND (NOT [ColumnC] ISNULL) LIMIT 1),
-- stripped more columns
FROM [TMP_A];

-- stripped some more INSERT INTO [] SELECT
-- stripped some UPDATE

DROP TABLE [TMP_A];
DROP TABLE [TMP_B];
DROP TABLE [D];
DROP TABLE [E];
DROP TABLE [F];

COMMIT TRANSACTION;


When omitting the DROP TABLE [TMP_B], the 3.7.4 does not detect
corruption directly, until some more updates are executed on the
database.

When the database before the "query" is .dump-ed to file, and .read into
a new 3.6.23.1 database, the corruption does not become visible after
the "query". So it seems to be in the header of that database.

I didn't find a way yet to reproduce the issue with a "clean" database.
Only way I can reproduce it is with some of the database, like the
test.db3. So I'm running out of ideas.

Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: woensdag 9 februari 2011 5:57
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database disk image is malformed 3.7.x
> 

> The only problem we know of was caused by 3.7.0, which was replaced
> by 3.7.0.1 when the problem was discovered. Even with 3.7.0, you
needed
> the right sequence of writes from 3.7.0 and some earlier version.
> 
> I don't know how the corruption you're seeing is caused. I would like
> to though.

OK. I'll spent time today to try to get a real reproducible example. But
on the moment I have really no clue why some have the issue and some
don't have the issue.

> 
> Do you use auto-vacuum mode? Or incremental vacuum?
> 
> Dan.

I did not set any vacuum-modes (so they are still on the defaults). And
I never VACUUM the database explicitly. 

Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/08/2011 10:24 PM, Dennis Geldhof wrote:
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Dan Kennedy
>> Sent: dinsdag 8 februari 2011 14:51
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] database disk image is malformed 3.7.x
>>
>
>> Then run the resulting executable with the path to a database
>> file as the first argument and "dbheader" as the second. i.e.
>>
>> ./showdb test.db dbheader
>>
>> where "test.db" is the database file name. The program prints
>> out a short report that, if the database was ever written by
>> 3.7.0 or newer, includes the version number of the most recent
>> version to do so.
>
> Hi Dan,
>
> The tool shows me the SQLite version of the test.db3 is indeed 3007004.
> So the database created with version 3.6.23.1, is once modified with a
> 3.7.4 and then modified with 3.6.23.1 again.
>
> So I guess we run into something similar to this ticket;
> http://www.sqlite.org/src/info/51ae9cad317a1 .
>
> One strange thing though, I also have a database with version 3007002
> which does not experience the database corruption. Can it be that the
> size just matches accidentally for that database?

The only problem we know of was caused by 3.7.0, which was replaced
by 3.7.0.1 when the problem was discovered. Even with 3.7.0, you needed
the right sequence of writes from 3.7.0 and some earlier version.

I don't know how the corruption you're seeing is caused. I would like
to though.

Do you use auto-vacuum mode? Or incremental vacuum?

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: dinsdag 8 februari 2011 14:51
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database disk image is malformed 3.7.x
> 

> Then run the resulting executable with the path to a database
> file as the first argument and "dbheader" as the second. i.e.
> 
>./showdb test.db dbheader
> 
> where "test.db" is the database file name. The program prints
> out a short report that, if the database was ever written by
> 3.7.0 or newer, includes the version number of the most recent
> version to do so.

Hi Dan,

The tool shows me the SQLite version of the test.db3 is indeed 3007004.
So the database created with version 3.6.23.1, is once modified with a
3.7.4 and then modified with 3.6.23.1 again. 

So I guess we run into something similar to this ticket;
http://www.sqlite.org/src/info/51ae9cad317a1 .

One strange thing though, I also have a database with version 3007002
which does not experience the database corruption. Can it be that the
size just matches accidentally for that database? 

Thanks, 
Dennis



This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/08/2011 08:26 PM, Dennis Geldhof wrote:
>> -Original Message-
>> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
>> boun...@sqlite.org] On Behalf Of Dan Kennedy
>> Sent: dinsdag 8 februari 2011 12:33
>> To: sqlite-users@sqlite.org
>> Subject: Re: [sqlite] database disk image is malformed 3.7.x
>>
>
>> We're very interested in how this happened. Do you ever write to the
>> db using the 3.7.4 tools? Or write to it with any other 3.7.X version?
>
> I have several machines over here (with developers) and the corruption
> only occurs on some machines. Besides that I am not able to reproduce
> this issue with a brand new database (maybe because my tools are at
> version 3.7.4). I can only reproduce this issue on some of the database
> that are in use more than 2 months. Looking at the description you gave
> and the symptoms we have, maybe this is caused by issue;
> http://www.sqlite.org/src/info/51ae9cad317a1 . Because the
> System.Data.Sqlite wrapper is still at sqlite version 3.6.23.1 and the
> tools update automatically we could have triggered that issue.
> Is there a way to check if the database was ever opened with a sqlite
> version newer than 3.6.23.1, so we can make sure it was ever edited by a
> tool?

Grab the C file from this link and compile it to a standalone
executable.

 
http://www.sqlite.org/src/raw/tool/showdb.c?name=471c0f8fa472e71bb7654500096a5bdb4ea1fb2a

Then run the resulting executable with the path to a database
file as the first argument and "dbheader" as the second. i.e.

   ./showdb test.db dbheader

where "test.db" is the database file name. The program prints
out a short report that, if the database was ever written by
3.7.0 or newer, includes the version number of the most recent
version to do so.

Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

> -Original Message-
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Dan Kennedy
> Sent: dinsdag 8 februari 2011 12:33
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] database disk image is malformed 3.7.x
>

> We're very interested in how this happened. Do you ever write to the
> db using the 3.7.4 tools? Or write to it with any other 3.7.X version?

I have several machines over here (with developers) and the corruption
only occurs on some machines. Besides that I am not able to reproduce
this issue with a brand new database (maybe because my tools are at
version 3.7.4). I can only reproduce this issue on some of the database
that are in use more than 2 months. Looking at the description you gave
and the symptoms we have, maybe this is caused by issue;
http://www.sqlite.org/src/info/51ae9cad317a1 . Because the
System.Data.Sqlite wrapper is still at sqlite version 3.6.23.1 and the
tools update automatically we could have triggered that issue. 
Is there a way to check if the database was ever opened with a sqlite
version newer than 3.6.23.1, so we can make sure it was ever edited by a
tool?

> Vacuum it?

We never vacuum the database.

> I think the mailing list stripped your attachment. Can you put
> somewhere
> we can download it from? Or just mail it to me if you like. Thanks.

Uploaded: http://jjb3.nl/sqlite/test.db3 (3KB)

Thanks,
Dennis

This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed 3.7.x

[Dan Kennedy]

On 02/08/2011 06:00 PM, Dennis Geldhof wrote:
> Hi all,
>
>
>
> I experienced some strange behavior between different versions of
> sqlite. Our application uses the System.Data.Sqlite wrapper
> (http://sqlite.phxsoftware.com/) which is on sqlite version 3.6.23.1,
> but the tools we use to view the database are on sqlite version 3.7.4.
> In the application no signs of database corruption are found, but the
> tools cannot open the database (tools; http://osenxpsuite.net/?xp=3  /
> http://www.sqliteexpert.com/).

Version 3.7.0 of SQLite introduced a field in the database file header
containing the logical size of the database. So that a database file
can be arbitrarily extended (AKA preallocated) without corrupting it.
Earlier versions of SQLite just used the size of the file as the size
of the database.

   http://www.sqlite.org/releaselog/3_7_0.html

The symptoms you are reporting come about if the header field indicates
that the database image is *larger* than the file on disk. If this is
the case, version 3.7.0 and newer assume that the file is corrupt and
report the error you are seeing. Earlier versions never read the header
field and never see a problem. Since the integrity check passes, it is
likely that the database is fine except that the header field is set
incorrectly.

We're very interested in how this happened. Do you ever write to the
db using the 3.7.4 tools? Or write to it with any other 3.7.X version?
Vacuum it?

> Attached you will find a database file which is not encrypted, and all
> tables are dropped. The corruption is still in there and is used to
> generate the output mentioned above.

I think the mailing list stripped your attachment. Can you put somewhere
we can download it from? Or just mail it to me if you like. Thanks.

Dan.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] database disk image is malformed 3.7.x

[Dennis Geldhof]

Hi all,

 

I experienced some strange behavior between different versions of
sqlite. Our application uses the System.Data.Sqlite wrapper
(http://sqlite.phxsoftware.com/) which is on sqlite version 3.6.23.1,
but the tools we use to view the database are on sqlite version 3.7.4.
In the application no signs of database corruption are found, but the
tools cannot open the database (tools; http://osenxpsuite.net/?xp=3  /
http://www.sqliteexpert.com/). 

 

To verify the behavior, I downloaded different versions of the sqlite
windows commandline tool from the sqlite website and ran the following
commands;

 

C:\tmp\malformed>3_6_23_sqlite3.exe test.db3 "pragma integrity_check;"

ok

 

C:\tmp\malformed>3_6_23_1_sqlite3.exe test.db3 "pragma integrity_check;"

ok

 

C:\tmp\malformed>3_7_0_sqlite3.exe test.db3 "pragma integrity_check;"

Error: database disk image is malformed

 

C:\tmp\malformed>3_7_4_sqlite3.exe test.db3 "pragma integrity_check;"

Error: database disk image is malformed

 

 

Another thing that might be interesting, is that when you run "VACUUM;"
with a 3.6.x version on the database, the corruption seems to be solved
for the 3.7.x versions. 

 

The corruption seems to occur after running a number of CREATE TABLE /
INSERT / UPDATE / ALTER TABLE / DROP TABLE commands in one transaction.
Normally the database is encrypted using the encryption provided by the
System.Data.Sqlite wrapper, but  the corruption also remains when
decrypting the database. 

 

Attached you will find a database file which is not encrypted, and all
tables are dropped. The corruption is still in there and is used to
generate the output mentioned above.

 

Can someone help me out with this issue?

 

Regards,

Dennis Geldhof


This message contains confidential information and is intended only for the 
individual named. If you are not the named addressee you should not 
disseminate, distribute or copy this e-mail. Please notify the sender 
immediately by e-mail if you have received this e-mail by mistake and delete 
this e-mail from your system. E-mail transmission cannot be guaranteed to be 
secure or error-free as information could be intercepted, corrupted, lost, 
destroyed, arrive late or incomplete, or contain viruses. The sender therefore 
does not accept liability for any errors or omissions in the contents of this 
message, which arise as a result of e-mail transmission. If verification is 
required please request a hard-copy version.
 Please consider the environment before printing this email message
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" while using WAL

[Alexey Pechnikov]

2010/8/2 Yoni Londner :
> Hi,
> Actually it DOES help.
> I just found out that I ran the wrong executable.
> Sorry if I wasted your time :-)
> When will you have an official release of this version?

See planned date here:
http://sqlite.org/draft/releaselog/3_7_1.html

"SQLite Release 3.7.1 On 2010 August 19 (3.7.1)"

-- 
Best regards, Alexey Pechnikov.
http://pechnikov.tel/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" while using WAL

[Yoni Londner]

Hi,
Actually it DOES help.
I just found out that I ran the wrong executable.
Sorry if I wasted your time :-)
When will you have an official release of this version?
Yoni.

On Mon, Aug 2, 2010 at 3:02 PM, Yoni Londner  wrote:

> Hi Dan,
> Thanks for the response.
> I tried to new version, but the problem still exist.
> I continued to debug it, and found that the error detected in btreeInitPage
> (on decodeFlags).
> I also found that if i run without enabling shared cache, the problem does
> not happen.
> Hope that it will help.
> Yoni.
>
>
> On Mon, Aug 2, 2010 at 2:33 PM, Dan Kennedy  wrote:
>
>>
>> On Aug 2, 2010, at 6:13 PM, Dan Kennedy wrote:
>>
>> >
>> > On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote:
>> >
>> >> Hi,
>> >> Forgot to mention that the inserting should be inside a
>> >> transactions, so
>> >> complete repro steps are:
>> >> 1. open sqlite connection.
>> >> 2. PRAGMA journal_mode=WAL
>> >> 3. PRAGMA synchronous=full
>> >> 4. PRAGMA temp_store=memory
>> >> 5. sqlite3_enable_shared_cache(1)
>> >> 6. begin transaction
>> >> 7. while(1) loop - insert records to a table.
>> >> 8. after 15 seconds, run a checkpoint on separate thread (using
>> >> another
>> >> sqlite connection with same settings as above).
>> >> 9. get the above error AFTER the checkpoint finished.
>> >
>> > Thanks for this report.
>> >
>> > If possible, can you try your test with the latest from
>> > fossil (newer than e75b52d156)? Thanks.
>> >
>> >
>> http://www.sqlite.org/src/zip/SQLite-016486c7d544dcf9.zip?uuid=016486c7d544dcf9b7422cb0fb9804aa1c418f68
>> >
>> > Please post if you need an amalgamation instead of the
>> > source package.
>>
>> Easier: Grab it from here:
>>
>>   http://www.sqlite.org/draft/download.html
>>
>> ___
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" while using WAL

[Yoni Londner]

Hi Dan,
Thanks for the response.
I tried to new version, but the problem still exist.
I continued to debug it, and found that the error detected in btreeInitPage
(on decodeFlags).
I also found that if i run without enabling shared cache, the problem does
not happen.
Hope that it will help.
Yoni.

On Mon, Aug 2, 2010 at 2:33 PM, Dan Kennedy  wrote:

>
> On Aug 2, 2010, at 6:13 PM, Dan Kennedy wrote:
>
> >
> > On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote:
> >
> >> Hi,
> >> Forgot to mention that the inserting should be inside a
> >> transactions, so
> >> complete repro steps are:
> >> 1. open sqlite connection.
> >> 2. PRAGMA journal_mode=WAL
> >> 3. PRAGMA synchronous=full
> >> 4. PRAGMA temp_store=memory
> >> 5. sqlite3_enable_shared_cache(1)
> >> 6. begin transaction
> >> 7. while(1) loop - insert records to a table.
> >> 8. after 15 seconds, run a checkpoint on separate thread (using
> >> another
> >> sqlite connection with same settings as above).
> >> 9. get the above error AFTER the checkpoint finished.
> >
> > Thanks for this report.
> >
> > If possible, can you try your test with the latest from
> > fossil (newer than e75b52d156)? Thanks.
> >
> >
> http://www.sqlite.org/src/zip/SQLite-016486c7d544dcf9.zip?uuid=016486c7d544dcf9b7422cb0fb9804aa1c418f68
> >
> > Please post if you need an amalgamation instead of the
> > source package.
>
> Easier: Grab it from here:
>
>   http://www.sqlite.org/draft/download.html
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" while using WAL

[Dan Kennedy]

On Aug 2, 2010, at 6:13 PM, Dan Kennedy wrote:

>
> On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote:
>
>> Hi,
>> Forgot to mention that the inserting should be inside a
>> transactions, so
>> complete repro steps are:
>> 1. open sqlite connection.
>> 2. PRAGMA journal_mode=WAL
>> 3. PRAGMA synchronous=full
>> 4. PRAGMA temp_store=memory
>> 5. sqlite3_enable_shared_cache(1)
>> 6. begin transaction
>> 7. while(1) loop - insert records to a table.
>> 8. after 15 seconds, run a checkpoint on separate thread (using
>> another
>> sqlite connection with same settings as above).
>> 9. get the above error AFTER the checkpoint finished.
>
> Thanks for this report.
>
> If possible, can you try your test with the latest from
> fossil (newer than e75b52d156)? Thanks.
>
>   
> http://www.sqlite.org/src/zip/SQLite-016486c7d544dcf9.zip?uuid=016486c7d544dcf9b7422cb0fb9804aa1c418f68
>
> Please post if you need an amalgamation instead of the
> source package.

Easier: Grab it from here:

   http://www.sqlite.org/draft/download.html

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" while using WAL

[Dan Kennedy]

On Aug 2, 2010, at 1:42 PM, Yoni Londner wrote:

> Hi,
> Forgot to mention that the inserting should be inside a  
> transactions, so
> complete repro steps are:
> 1. open sqlite connection.
> 2. PRAGMA journal_mode=WAL
> 3. PRAGMA synchronous=full
> 4. PRAGMA temp_store=memory
> 5. sqlite3_enable_shared_cache(1)
> 6. begin transaction
> 7. while(1) loop - insert records to a table.
> 8. after 15 seconds, run a checkpoint on separate thread (using  
> another
> sqlite connection with same settings as above).
> 9. get the above error AFTER the checkpoint finished.

Thanks for this report.

If possible, can you try your test with the latest from
fossil (newer than e75b52d156)? Thanks.

   
http://www.sqlite.org/src/zip/SQLite-016486c7d544dcf9.zip?uuid=016486c7d544dcf9b7422cb0fb9804aa1c418f68

Please post if you need an amalgamation instead of the
source package.

Dan.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] "database disk image is malformed" while using WAL

[Yoni Londner]

Hi,
Forgot to mention that the inserting should be inside a transactions, so
complete repro steps are:
1. open sqlite connection.
2. PRAGMA journal_mode=WAL
3. PRAGMA synchronous=full
4. PRAGMA temp_store=memory
5. sqlite3_enable_shared_cache(1)
6. begin transaction
7. while(1) loop - insert records to a table.
8. after 15 seconds, run a checkpoint on separate thread (using another
sqlite connection with same settings as above).
9. get the above error AFTER the checkpoint finished.

On Mon, Aug 2, 2010 at 9:25 AM, Yoni Londner  wrote:

> Hi,
> I am getting "database disk image is malformed" error while using WAL.
> To reproduce:
> 1. open sqlite connection.
> 2. PRAGMA journal_mode=WAL
> 3. PRAGMA synchronous=full
> 4. PRAGMA temp_store=memory
> 5. sqlite3_enable_shared_cache(1)
> 6. while(1) loop - insert records to a table.
> 7. after 15 seconds, run a checkpoint on separate thread (using another
> sqlite connection with same settings as above).
> 8. get the above error AFTER the checkpoint finished.
>
> Reproduced on both linux (debian lenny) and win XP.
> Appreciate any help.
>
> Yoni.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] "database disk image is malformed" while using WAL

[Yoni Londner]

Hi,
I am getting "database disk image is malformed" error while using WAL.
To reproduce:
1. open sqlite connection.
2. PRAGMA journal_mode=WAL
3. PRAGMA synchronous=full
4. PRAGMA temp_store=memory
5. sqlite3_enable_shared_cache(1)
6. while(1) loop - insert records to a table.
7. after 15 seconds, run a checkpoint on separate thread (using another
sqlite connection with same settings as above).
8. get the above error AFTER the checkpoint finished.

Reproduced on both linux (debian lenny) and win XP.
Appreciate any help.

Yoni.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Database disk image is malformed

[Filipe Madureira]

Hi,

I have an application running on Windows CE4.1 PDAs with SD cards.

Sometimes I have errors executing commands on database that are:
11-database disk image is malformed

This happens for example on table MSTBP executing "Delete From MSTBP".
Or inserting into it.

The strange thing is, that after I restart my application (hence the 
connection) the error disappears.
Everything continues working normally and the previous SQL statements, 
that before generated this error, start working normally.

Any ideas how I can prevent this from happening?

I looked into pragma and specifically into:
PRAGMA journal_mode = /PERSIST

/Do you thing this may help? Do you think that maybe this problem is 
because at a given time, the sqlite engine could not "access" the 
journal file? Some strange hardware or OS problem that did not 
created/deleted the file immediately, mainly because it is on a SD Card?

Thanks

-- 
Cumprimentos / Best Regards

Filipe Madureira
-
SYSDEV, LDA - Mobile Solutions
(www.sysdevsolutions.com)
Tel: +351 234098066
Fax: +351 234188400
- 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Jeffrey Rennie (レニー）]

It was a typo.  I was setting fullsync when the correct pragma name is
fullfsync.

On Fri, Sep 26, 2008 at 9:50 AM, Jeffrey Rennie (レニー） <[EMAIL PROTECTED]>wrote:

>
> 2008/9/25 D. Richard Hipp <[EMAIL PROTECTED]>
>
>>
>> You are confusing the statement journal with the rollback journal.
>> The statement journal has nothing to do with database recovery - that
>> is the task of the rollback journal.  So the statement journal can be
>> deleted at will without damaging the database.  And, in fact, the
>> statement journal is opened with delete-on-close.
>>
>
> Indeed I was.
>
> I gained some more insight into the problem.  I set a breakpoint on line
> 950 of os_unix.cc:
>
>#if HAVE_FULLFSYNC
>  if( fullSync ){
> >>>rc = fcntl(fd, F_FULLFSYNC, 0);
>  }else{
>rc = 1;
>  }
>
>
> And the breakpoint never hits, no matter what pragmas I set!  Instead, it
> always executes the rc = 1; line.
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Jeffrey Rennie (レニー）]

2008/9/25 D. Richard Hipp <[EMAIL PROTECTED]>

>
> You are confusing the statement journal with the rollback journal.
> The statement journal has nothing to do with database recovery - that
> is the task of the rollback journal.  So the statement journal can be
> deleted at will without damaging the database.  And, in fact, the
> statement journal is opened with delete-on-close.
>

Indeed I was.

I gained some more insight into the problem.  I set a breakpoint on line 950
of os_unix.cc:

   #if HAVE_FULLFSYNC
 if( fullSync ){
>>>rc = fcntl(fd, F_FULLFSYNC, 0);
 }else{
   rc = 1;
 }


And the breakpoint never hits, no matter what pragmas I set!  Instead, it
always executes the rc = 1; line.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Gene Allen]

Sorry, mispost
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] database disk image is malformed

[Gene Allen]

That's an error that may be left over from 1.7.  If you're  filter is using a 
mapped drive, try using a UNC.

I'm not near a computer, so I can't verify the error message.  I will as soon 
as I get back to the office and kick on the generator, I will verify it.

If you could run the User Companion on the start menu, it will package up your 
rules and FTP them to me.

Gene
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users