Re: [sqlite] more on mac database corruption
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
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
> 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 Dyerwrote: > >> >>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
> >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
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
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 Dyerwrote: > 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
[sqlite] more on mac database corruption
I was able to reproduce 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