I decided to vacuum my places.sqlite database (used by Firefox to store its history and bookmarks) and got a primary key violation.
The database passes the analyzer and dumper with no errors. select count(*) from the different tables also shows no errors. However, a dump and attempted restore DOES find some anomalies, please see the log below. Scott #### Log cro...@dragonlight:~/.mozilla/firefox/default.q0z$ sqlite3 places.sqlite SQLite version 3.7.0 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> vacuum; Error: PRIMARY KEY must be unique cro...@functor:~/.mozilla/firefox/default.q0z$ /tmp/sqlite3_analyzer places.sqlite > /dev/null cro...@functor:~/.mozilla/firefox/default.q0z$ sqlite3 places.sqlite .dump > /dev/null cro...@dragonlight:~/.mozilla/firefox/default.q0z$ sqlite3 places.sqlite .dump | sqlite3 /tmp/test.sqlite3 Error: near line 82900: moz_places.hidden may not be NULL Error: near line 104665: moz_places.hidden may not be NULL Those two lines consist of: INSERT INTO "moz_places" VALUES(0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); INSERT INTO "moz_places" VALUES(0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL); >From a year-old backup and dates in visited webpages line #82900, was created about 18 months ago and altered sometime in the last year. Adjacent lines before and after show no errors and have consecutive sequence numbers. #### Schema CREATE TABLE moz_anno_attributes (id INTEGER PRIMARY KEY,name VARCHAR(32) UNIQUE NOT NULL); CREATE TABLE moz_annos (id INTEGER PRIMARY KEY,place_id INTEGER NOT NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT 0,lastModified INTEGER DEFAULT 0); CREATE TABLE moz_bookmarks (id INTEGER PRIMARY KEY,type INTEGER, fk INTEGER DEFAULT NULL, parent INTEGER, position INTEGER, title LONGVARCHAR, keyword_id INTEGER, folder_type TEXT, dateAdded INTEGER, lastModified INTEGER); CREATE TABLE moz_bookmarks_roots (root_name VARCHAR(16) UNIQUE, folder_id INTEGER); CREATE TABLE moz_favicons (id INTEGER PRIMARY KEY, url LONGVARCHAR UNIQUE, data BLOB, mime_type VARCHAR(32), expiration LONG); CREATE TABLE moz_historyvisits (id INTEGER PRIMARY KEY, from_visit INTEGER, place_id INTEGER, visit_date INTEGER, visit_type INTEGER, session INTEGER); CREATE TABLE moz_inputhistory (place_id INTEGER NOT NULL, input LONGVARCHAR NOT NULL, use_count INTEGER, PRIMARY KEY (place_id, input)); CREATE TABLE moz_items_annos (id INTEGER PRIMARY KEY,item_id INTEGER NOT NULL,anno_attribute_id INTEGER,mime_type VARCHAR(32) DEFAULT NULL,content LONGVARCHAR, flags INTEGER DEFAULT 0,expiration INTEGER DEFAULT 0,type INTEGER DEFAULT 0,dateAdded INTEGER DEFAULT 0,lastModified INTEGER DEFAULT 0); CREATE TABLE moz_keywords (id INTEGER PRIMARY KEY AUTOINCREMENT, keyword TEXT UNIQUE); CREATE TABLE moz_places (id INTEGER PRIMARY KEY, url LONGVARCHAR, title LONGVARCHAR, rev_host LONGVARCHAR, visit_count INTEGER DEFAULT 0, hidden INTEGER DEFAULT 0 NOT NULL, typed INTEGER DEFAULT 0 NOT NULL, favicon_id INTEGER, frecency INTEGER DEFAULT -1 NOT NULL, last_visit_date INTEGER); CREATE UNIQUE INDEX moz_annos_placeattributeindex ON moz_annos (place_id, anno_attribute_id); CREATE INDEX moz_bookmarks_itemindex ON moz_bookmarks (fk,type); CREATE INDEX moz_bookmarks_itemlastmodifiedindex ON moz_bookmarks (fk, lastModified); CREATE INDEX moz_bookmarks_parentindex ON moz_bookmarks (parent,position); CREATE INDEX moz_historyvisits_dateindex ON moz_historyvisits (visit_date); CREATE INDEX moz_historyvisits_fromindex ON moz_historyvisits (from_visit); CREATE INDEX moz_historyvisits_placedateindex ON moz_historyvisits (place_id, visit_date); CREATE UNIQUE INDEX moz_items_annos_itemattributeindex ON moz_items_annos (item_id, anno_attribute_id); CREATE INDEX moz_places_faviconindex ON moz_places (favicon_id); CREATE INDEX moz_places_frecencyindex ON moz_places (frecency); CREATE INDEX moz_places_hostindex ON moz_places (rev_host); CREATE INDEX moz_places_lastvisitdateindex ON moz_places (last_visit_date); CREATE UNIQUE INDEX moz_places_url_uniqueindex ON moz_places (url); CREATE INDEX moz_places_visitcount ON moz_places (visit_count); CREATE TRIGGER moz_bookmarks_beforedelete_v1_trigger BEFORE DELETE ON moz_bookmarks FOR EACH ROW WHEN OLD.keyword_id NOT NULL BEGIN DELETE FROM moz_keywords WHERE id = OLD.keyword_id AND NOT EXISTS (SELECT id FROM moz_bookmarks WHERE keyword_id = OLD.keyword_id AND id <> OLD.id LIMIT 1); END; _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
