On Wed, Jun 30, 2010 at 5:37 PM, Steve Hurst <[email protected]>wrote:
> Hi all, not sure if this is a bug or user error - any insight appreciated! > > I have a schema that defines a two column primary key on a table - the docs > indicate this will create a unique composite key. However, code running > "INSERT OR REPLACE INTO ..." many times over a long period of time > eventually produces multiple rows that have the same primary key - see > below. > I'm guessing the database file went corrupt (see http://www.sqlite.org/lockingv3.html#how_to_corrupt and http://www.sqlite.org/atomiccommit.html#sect_9_0 for further discussion on how that can happen) and that as a result of this corruption, the index used to enforce uniqueness stopped working. > > SQLite 3.3.6 on RHEL 5.4 from the RHN repo, I have a gzip of the db if it > is helpful? > > Thanks - Steve > > > > ----- snip ---------- > > sqlite> .schema node_attributes > CREATE TABLE node_attributes > ( > node_id text, > attribute text, > value text, > PRIMARY KEY ( node_id, attribute ) > ); > CREATE INDEX node_attribute ON node_attributes (attribute); > sqlite> select count() as onlyone, node_id, attribute from node_attributes > group by node_id, attribute having onlyone>1; > 2|lcdre36821|CONTAINERNODE > 336|lcdre36821|CONTAINERNODE > 337|lcdre36821|STATE > 337|lcdre36889|CONTAINERNODE > 338|lcdre36889|STATE > 338|r03idpx12|STATE > 152|r03idpx14|STATE > 679|r06idpx27|STATE > 656|r06idpx29|STATE > sqlite> > > ----- snip ---------- > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- --------------------- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
