Dan Kennedy wrote:
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.
That may well be it. The box reboots every 24 hours but when it does it
uses a stable copy of the database and all the "operating" data is
replaced. However the stable copy is updated every hour by purging
records older than a certain timeframe, performing a VACUUM, and then
overwriting the stable copy. Looking at the cron scheduling I see that
both events have an opportunity to collide and may well have done so
particularly since the table that issued errors is the same one that is
updated during this process.
I wish there were a way to change these PRAGMA settings on a built
table. Apparently one has to start from scratch.
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?
No, there's plenty of room on the partition. I'll ask the author of
SQLite Administrator about it.
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?
Ran one this morning against the database.
Prior to VACUUM:
233472 bytes
After VACUUM:
168960 bytes
That's about 28% difference.
Matches your spec.
Thank you!
/m
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
