Can you send me the moz_places.sqlite file you are using to my private email? Or get it to me by some other means. So that we can try to reproduce this?
On 11/2/16, Ben Hearsum <bhear...@mozilla.com> wrote: > Hi there, > > I recently had an issue with an index in a large-ish (>100,000 rows) table > becoming corrupted. I tried to issue a REINDEX on it, but it complained of > duplicated values. I dug into that a bit, and couldn't find any duplicate > entries: > sqlite> select count(*) from moz_places; > 100575 > sqlite> select count(distinct guid) from moz_places; > 100575 > sqlite> select guid from moz_places group by guid having count(*) >1; > sqlite> > > I then tried to bisect to find which entry was actually causing a problem by > creating a new table + index: > CREATE TABLE moz_places_bhearsum ( 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 , guid TEXT, foreign_count INTEGER DEFAULT 0 NOT > NULL, url_hash INTEGER DEFAULT 0 NOT NULL); > sqlite> CREATE UNIQUE INDEX moz_places_bhearsum_guid_uniqueindex ON > moz_places_bhearsum (guid); > > I then did some inserts, and discovered that the order in which the rows are > inserted appears to be affecting the uniqueness of the index: > sqlite> insert into moz_places_bhearsum select * from moz_places order by > guid; > sqlite> reindex moz_places_bhearsum; > sqlite> delete from moz_places_bhearsum; > sqlite> insert into moz_places_bhearsum select * from moz_places; > sqlite> reindex moz_places_bhearsum; > Error: UNIQUE constraint failed: moz_places_bhearsum.guid > > > Is this a real bug, or am I misunderstanding something about the way things > are supposed to work? > > - Ben > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
