On Tue, 2007-01-09 at 22:28 -0500, Mark Richards wrote: > Using sqlite in our embedded device has offered tremendous capabilities > and very conveniently, and I thank the developers and enthusiasts who > continue to further this excellent project. > > I've had one issue that I cannot explain and would ask for some input. > > sqlite 3.1.3 > linux kernel 2.6.12 > cris-axis-linux-gnu > > One field system began to issue "database disk image is malformed" for > reasons that I cannot yet explain. I ran a PRAGMA integrity_check, > which told me: > > sqlite> PRAGMA integrity_check; > *** in database main *** > Main freelist: 1 of 1 pages missing from overflow list starting at 0 > Page 46 is never used > rowid 1355980 missing from index timestamp > rowid 1356049 missing from index timestamp > ... > rowid 1356108 missing from index timestamp > wrong # of entries in index timestamp > wrong # of entries in index dataid > sqlite> > > VACUUM failed with the same "database disk image is malformed". > Attempts at deleting all records from the broken table failed. In the > end, I was able to repair the database by bringing it down to my > workstation and loading it in the SQLite Administrator windows GUI and > executing Database: cleanup. > > My database is built using the following PRAGMA statements: > > pragma PRAGMA auto_vacuum = 1; > pragma PRAGMA count_changes = 1; > pragma PRAGMA empty_result_callbacks = 1; > pragma PRAGMA legacy_file_format = OFF; > pragma PRAGMA synchronous = OFF;
With pragma synchronous set to "OFF", if the device lost power or the operating system crashed in the middle of a transaction database corruption can occur. > 1) are there any tools available in sqlite3 that will help me find the > cause of this type of issue? > 2) does anyone know what did SQLite Administrator do that VACUUM didn't? The integrity check shows problems with index structures only - so maybe SQLite Administrator issued queries that never used an index. Although that doesn't explain why the VACUUM failed, I would of thought the same reasoning would apply. Maybe it ran out of space in the file-system or something? > 3) since auto_vacuum is ON, I still need to do a VACUUM every so often. > Any ideas why? A vacuum recreates an entire database, more or less ensuring that records are packed into database pages with very little wasted space. By contrast, auto-vacuum mode automatically shrinks the file whenever one or more pages are completely empty. So in an auto-vacuum database file there are never empty pages but the packing of records may be sub-optimal. Hence it is possible (even likely) that a VACUUM operation will reduce the size of an auto-vacuum database a bit. The btree layer tries to keep every page at least 2/3 full during regular balancing, so I would guess a VACUUM could shrink an auto-vacuum database by at most 33%. Almost certainly less. Does this match up with what you're seeing? Dan. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
