Re: [sqlite] more on mac database corruption

[Dave Dyer]

We're not talking about reliability or robustness of networked file
systems - those caveats are valid of course - but not relevant for
the case in point.

The case in point is a simple sequence of operations that you can execute
one at a time, as slowly as you like, and results in a corrupt database
every time.

--

The "different locking strategies" explanation sounds plausible to me,
but its curious that the main database, which is accessed the same
way, is working fine.  Something different (and buggy) seems to be
happening with attached databases.

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

Re: [sqlite] more on mac database corruption

[Jay A. Kreibich]

On Thu, Mar 25, 2010 at 10:58:41AM -0700, Dave Dyer scratched on the wall:
> 
> >
> >AFAIK it's a general rule: don't use SQLite with database somewhere on
> >network shared file system, otherwise bad things can happen.
> 
> That's definitely not the general rule.

  Yes it is-- for *networked* file systems.

  http://www.sqlite.org/faq.html#q5

"People who have a lot of experience with Windows tell me that
file locking of network files is very buggy and is not
dependable. If what they say is true, sharing an SQLite
database between two or more Windows machines might cause
unexpected problems."

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

"...file locking logic of many network filesystems implementation
contains bugs (on both Unix and Windows). If file locking does
not work like it should, it might be possible for two or more
client programs to modify the same part of the same database at
the same time, resulting in database corruption. Because this
problem results from bugs in the underlying filesystem
implementation, there is nothing SQLite can do to prevent it."



  SQLite locked is very robust and dependable for multiple processes
  on the same machine accessing a single database on storage that is
  physically connected to that machine.

  All bets are off when talking about networked shares.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Our opponent is an alien starship packed with atomic bombs.  We have
 a protractor."   "I'll go home and see if I can scrounge up a ruler
 and a piece of string."  --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] more on mac database corruption

[Pavel Ivanov]

> That's definitely not the general rule.  Generally, you can open
> a sqlite database from multiple processes and all of them can modify
> at will without corrupting the database.

You didn't understand my words correctly. Of course SQLite database
can be opened from multiple processes and used safely. Unless your
database is *somewhere on network shared file system*. SQLite database
shared over network is a call for troubles. And _it's a general rule_
to not use SQLite database *on a network file system*.

Read please http://www.sqlite.org/whentouse.html, section "Situations
Where Another RDBMS May Work Better", first subsection "Client/Server
Applications".


Pavel

On Thu, Mar 25, 2010 at 1:58 PM, Dave Dyer  wrote:
>
>>
>>AFAIK it's a general rule: don't use SQLite with database somewhere on
>>network shared file system, otherwise bad things can happen.
>
> That's definitely not the general rule.  Generally, you can open
> a sqlite database from multiple processes and all of them can modify
> at will without corrupting the database.  Of course, if they're all
> modifying the same records, there's uncertainty what the final state
> will be, but the database is still intact and consistent.
>
> ___
> 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] more on mac database corruption

[Dave Dyer]

>
>AFAIK it's a general rule: don't use SQLite with database somewhere on
>network shared file system, otherwise bad things can happen.

That's definitely not the general rule.  Generally, you can open
a sqlite database from multiple processes and all of them can modify
at will without corrupting the database.  Of course, if they're all
modifying the same records, there's uncertainty what the final state
will be, but the database is still intact and consistent.

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

Re: [sqlite] more on mac database corruption

[Simon Slavin]

On 25 Mar 2010, at 11:42am, Pavel Ivanov wrote:

> For me your sequence of commands clearly shows database file is badly
> managed by your Mac file system and/or network sharing mechanism.

Or by the client computer (which is running Windows).

The setup described by Mr. Dyer appears to involve a Mac client using advisory 
locks and fsync(), and a Windows client using LockFile() and 
FlushFileBuffers(), both at the same time on the same file.  These systems may 
each be self-consistent but I wonder if they will interact in an appropriate 
manner.

Another thing which may be connected to this problem is that one access to the 
database is done via a network protocol but another access is done by directly 
accessing the file on the computer's hard disk.  Generally, if one is running a 
server the server only serves: all access to a shared file is performed through 
a network protocol, not via access to the hard disk itself.  If SQLite changes 
locking behaviour and uses different locking protocols the two clients are 
going to handle locking differently.  I don't know enough about SQLite to know 
if this is the case.  I started reading section 6 of lockingv3.html but my 
brain exploded.

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

Re: [sqlite] more on mac database corruption

[Pavel Ivanov]

For me your sequence of commands clearly shows database file is badly
managed by your Mac file system and/or network sharing mechanism.
Apparently when you try to delete rows from table on Mac SQLite didn't
understand that database was changed (probably because of some caching
either on Mac side or on PC side). So SQLite tried to change database
as if it wasn't changed already, hit some inconsistency with
assumption somewhere in the middle of the process and it resulted in
corrupted database...

AFAIK it's a general rule: don't use SQLite with database somewhere on
network shared file system, otherwise bad things can happen.


Pavel

On Wed, Mar 24, 2010 at 5:42 PM, Dave Dyer  wrote:
> I was able to reproduct the problem using a trivial set of commands
> to the standard sqlite command tool:
>
>
> On the Mac:
>
> gorp:~/2010 yeartech/yearbook tools/resource davedyer$ 
> /applications/utilities/sqlite3-shell actiontool2.sqlite
> SQLite version 3.6.10 with the Encryption Extension
> sqlite> attach database 'indexer.sqlite' as indexer;
> sqlite> begin transaction;
>
>
> On the PC:
>
> M:\2010 yeartech\yearbook tools\resource>sqlite3 actiontool2.sqlite
> sqlite> attach database 'indexer.sqlite' as indexer;
> sqlite> begin transaction;
> sqlite> delete from indexer.preference_table;
> sqlite> insert into indexer.preference_table select * from preference_table;
> sqlite> commit;
>
> On the Mac:
>
>
> sqlite> delete from indexer.preferences_table;
> SQL error: no such table: indexer.preferences_table
> sqlite> delete from indexer.preference_table;
> sqlite> insert into indexer.preference_table select * from preference_table;
> SQL error: database disk image is malformed
> sqlite>
>
> ___
> 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