SQLite, Found a table with a row that has a duplicate field for the primary key column

A

admin

Administrator
Staff member
I created a C++ application that updates a SQLite database. The application reads a row, does an action with the values in the row, then updates the "modified" column with the current time. I believe that I am only using SELECT, INSERT, CREATE commands.

The application runs correctly for the first few days before problems start to occur. When I checked the database settings with <a href="http://blogsandip.wordpress.com/2010/04/06/sqlite-administrator-great-sqlite-windows-client/" rel="nofollow">sqlite-administrator</a> I found that there was a table that has a row with a duplicate key values.

I created the table with this command

Code: 
CREATE TABLE 'XXXX_tasks' ( 'id' INTEGER PRIMARY KEY ,  'data_length' INTEGER,  'data_offset' INTEGER,  'data_table' TEXT,  'device_id' INTEGER,  'modified' INTEGER,  'name' TEXT,  'object_id' INTEGER,  'object_type' TEXT,  'object_units' INTEGER,  'property' INTEGER,  'scan' INTEGER,  'type' TEXT )

The data in the table looks like this (reduced for readability)

Code: 
ID | Name  | Object ID 
 5 | AV_33 | 33         &lt;===  
 2 | AV_34 | 34 
 3 | AV_35 | 35 
 4 | AV_36 | 36 
 5 | AV_33 | 33         &lt;===

It appears that the there are two rows with a duplicate KEY field. At first I thought that it was a corrupted database file, A fluke. I deleted the database file and restarted the application. After a week the same problem occurred. Its repeatable.

You can download/view the database file yourself here <a href="http://www.abluestar.com/files/uploads/database_jan312012.db" rel="nofollow">http://www.abluestar.com/files/uploads/database_jan312012.db</a>

<strong>My question is:</strong>

I can't think of any reason that you could create a create or update a row to have a duplicate primary key value. Any ideas how this could have happen?
 
You must log in or register to reply here.