Sqlite and bug with restrictions while selecting and pasting at the same time

Question

I am working on a migration function. It reads data from the old table and inserts it into the new one. Everything that works in the background thread with low priority.

My steps are in pseudo code.

sqlite3_prepare_stmt (select statement) sqlite3_prepare_stmt (insert statement) while (sqlite3_step (select statement) == SQLITE_ROW) { get data from select row results sqlite3_bind select results to insert statement sqlite3_step (insert statement) sqlite3_reset (insert statement) } sqlite3_reset (select statement) 

I always get an error with restrictions on sqlite3_step (insert statement) . Why did this happen and how could I fix it?

UPD: As I understand it, this happened because the background thread uses the db handle opened in the main thread. Checking out this hunch.

UPD2:

 sqlite> select sql from sqlite_master where tbl_name = 'tiles'; CREATE TABLE tiles('pk' INTEGER PRIMARY KEY, 'data' BLOB, 'x' INTEGER, 'y' INTEGER, 'z' INTEGER, 'importKey' INTEGER) sqlite> select sql from sqlite_master where tbl_name = 'tiles_v2'; CREATE TABLE tiles_v2 (pk int primary key, x int, y int, z int, layer int, data blob, timestamp real) 

Answer 1

Your insert statement is likely to violate the constraint in the new table. There may be a primary key constraint, a unique constraint, a foreign key constraint (if you use PRAGMA foreign_keys = ON; ), etc.

You fix it either by removing the restriction, fixing the data, or deleting the data. Removing a constraint is usually bad, but it depends on the application.

Is there a good reason to copy data one row at a time, and not as a set?

 INSERT INTO new_table SELECT column_list FROM old_table; 

If you need help defining a constraint, edit the original question and post the result of these two SQLite queries.

 select sql from sqlite_master where tbl_name = 'old_table_name'; select sql from sqlite_master where tbl_name = 'new_table_name'; 

---

Update:. Based on the results of these two queries, I see only one restriction - the primary key restriction in each table. If you have not created triggers in these tables, the only restriction that may fail is the primary key restriction. And the only way a constraint can fail is to try inserting two lines that have the same meaning for pk.

I guess this can happen in several different ways.

• The old table has duplicate values ​​in the "pk" column.
• The code that performs your migration modifies or enters a duplicate value before inserting data into a new table.
• Another process, possibly another computer running, inserts or updates data without your knowledge.
• Other reasons I haven't thought about yet. :-)

You can determine if there are duplicate pk values ​​in the old table by running this query.

 select pk from old_table_name group by pk having count() > 1; 

Perhaps you should try to manually transfer data using INSERT INTO . . . SELECT . . . INSERT INTO . . . SELECT . . . If this fails, add a WHERE clause to reduce the size of the set until you isolate the bad data.

Answer 2

Just in case, if someone lands here looking for the "Constraint failed" error message, make sure your column type is Id INTEGER , not INTEGER (0, 15) or something else.

Background

If the table has a column named Id with type INTEGER and is set as the primary key, SQLite treats it as an alias for the built-in RowId column. This column works like an auto-increment column. In my case, this column worked fine until some table designer (perhaps the one created by the SQLite guys for Visual Studio) changed the column type from INTEGER to INTEGER (0, 15) , and suddenly my application started throwing Constraint failed exception.