On Mon, Feb 14, 2011 at 7:48 PM, Jim Wilcoxson <pri...@gmail.com> wrote:
> On Mon, Feb 14, 2011 at 10:56 AM, Pavel Ivanov <paiva...@gmail.com> wrote: > >> So my question is, does it maintain the other 3 parts of ACID, so that > >> the database will never be in a corrupted state after a power loss, > >> even though it may be missing some writes that were confirmed? > > > > Jim, I think the answer to your question is already in Max's tests: > > the USB drive is completely unreliable and can easily lead to database > > corruption. I'll explain. Max's tests showed that there were > > situations when database and journal were different not by one > > transaction but by several ones. So if one makes changes to several > > database pages (located in different disk sectors) and/or makes > > transactions touching several database pages (which makes multi-page > > journal files) then these pages can be way out of sync with each other > > (including pages inside journal). And this will easily lead to > > database corruption. > > > > > > Pavel > > Maybe Max would be kind enough to re-run his test with an insert > instead of an update and see if there ever is corruption or if just > the last n inserts are missing. > > > Jim, I agree it's interesting to know more about usb drive consistency in context of sqlite, at least the testing is easy. Unfortunately when I first started the tests there was already malformed db error but just once. I just managed to repeat this now. The db contains a single empty table CREATE TABLE [TestTable] ([Id] INTEGER PRIMARY KEY AUTOINCREMENT) The repeated query is: INSERT INTO TestTable DEFAULT VALUES I suppose the journal should be present othewise ACID test makes not sense pragma journal_mode=delete; The version performing insert was Sqlite 3.7.5 just in case some things were fixed recently So after about 500 inserts I pulled the cord, get 'can't insert #511' and first there was an error in system tray (Windows 7) about postponed writing error for journal file, it surprised a little since it partly breaks OS innocence that officially states it works for "quick removal", but actually complains on a not-finished operation. Maybe this means interrupting in the middle of a writing to a journal file that routes to this tray error, don't know Ok, I replug the drive. No journal file was present. On open: 3.7.5 shell said "Error: database disk image is malformed" for both pragma integrity_check; and pragma encoding; 3.6.10 correctly said UTF-8 on pragma encoding; and on integrity_check; said *** in database main *** On page 2 at right child: invalid page number 7 Btw, some of the tests (malformed and no) made system itself suspecting errors on this NTFS drive suggesting checking the disk and in the report there was a line about "fixing volume bit map" (can't say in exactly in english since the error was on russian windows). This part returns some innocence since probably even windows ntfs driver suffers from this postponed writing inside the drive. Max _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
