> > If you can capture a malfunctioning trace, and send in the database > file and the SQL statement that is running, that should allow us to > localize the problem. >
Trace for the failing UPDATE: cv_s=1 cv_o=b8b9f4... cv_t=28 -> 29 SQL: [update cv set cv_t=? where cv_s=? and cv_o=?] VDBE Program Listing: 0 Init 0 25 0 00 Start at 25 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; sqlite_autoindex_cv_1 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 5 Variable 2 6 0 00 r[6]=parameter(2,) 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 7 Variable 3 7 0 00 r[7]=parameter(3,) 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 9 Affinity 6 1 0 D 00 affinity(r[6]) 10 SeekGE 1 15 6 2 00 key=r[6..7] 11 IdxGT 1 15 6 2 00 key=r[6..7] 12 IdxRowid 1 8 0 00 r[8]=rowid 13 Seek 0 8 0 00 intkey=r[8] 14 IdxRowid 1 2 0 00 r[2]=rowid 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 16 Column 0 0 3 00 r[3]=cv.cv_s 17 Column 0 1 4 00 r[4]=cv.cv_o 18 Variable 1 5 0 00 r[5]=parameter(1,) 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] 24 Halt 0 0 0 00 25 Transaction 0 1 56 0 01 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 27 Goto 0 1 0 00 VDBE Trace: 0 Init 0 25 0 00 Start at 25 SQL-trace: update cv set cv_t=? where cv_s=? and cv_o=? 25 Transaction 0 1 56 0 01 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 27 Goto 0 1 0 00 1 Null 0 1 2 00 r[1..2]=NULL REG[1] = NULL 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; sqlite_autoindex_cv_1 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 5 Variable 2 6 0 00 r[6]=parameter(2,) REG[6] = i:0 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 REG[6] = i:0 7 Variable 3 7 0 00 r[7]=parameter(3,) REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 9 Affinity 6 1 0 D 00 affinity(r[6]) 10 SeekGE 1 15 6 2 00 key=r[6..7] REG[6] = i:0 11 IdxGT 1 15 6 2 00 key=r[6..7] 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 REG[2] = NULL 24 Halt 0 0 0 00 Followed by a failing insert for the same row: sqlite:1555: abort at 16 in [insert into cv (cv_s,cv_o,cv_t) VALUES(?,?,?)]: UNIQUE constraint failed: cv.cv_s, cv.cv_o Trace for the previous successful UPDATE to the same row: cv_s=1 cv_o=b8b9f4... cv_t=27 -> 28 SQL: [update cv set cv_t=? where cv_s=? and cv_o=?] VDBE Program Listing: 0 Init 0 25 0 00 Start at 25 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; sqlite_autoindex_cv_1 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 5 Variable 2 6 0 00 r[6]=parameter(2,) 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 7 Variable 3 7 0 00 r[7]=parameter(3,) 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 9 Affinity 6 1 0 D 00 affinity(r[6]) 10 SeekGE 1 15 6 2 00 key=r[6..7] 11 IdxGT 1 15 6 2 00 key=r[6..7] 12 IdxRowid 1 8 0 00 r[8]=rowid 13 Seek 0 8 0 00 intkey=r[8] 14 IdxRowid 1 2 0 00 r[2]=rowid 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 16 Column 0 0 3 00 r[3]=cv.cv_s 17 Column 0 1 4 00 r[4]=cv.cv_o 18 Variable 1 5 0 00 r[5]=parameter(1,) 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] 24 Halt 0 0 0 00 25 Transaction 0 1 56 0 01 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 27 Goto 0 1 0 00 VDBE Trace: 0 Init 0 25 0 00 Start at 25 SQL-trace: update cv set cv_t=? where cv_s=? and cv_o=? 25 Transaction 0 1 56 0 01 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 27 Goto 0 1 0 00 1 Null 0 1 2 00 r[1..2]=NULL REG[1] = NULL 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; sqlite_autoindex_cv_1 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 5 Variable 2 6 0 00 r[6]=parameter(2,) REG[6] = i:1 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 REG[6] = i:1 7 Variable 3 7 0 00 r[7]=parameter(3,) REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 9 Affinity 6 1 0 D 00 affinity(r[6]) 10 SeekGE 1 15 6 2 00 key=r[6..7] REG[6] = i:1 11 IdxGT 1 15 6 2 00 key=r[6..7] 12 IdxRowid 1 8 0 00 r[8]=rowid REG[8] = i:1007 13 Seek 0 8 0 00 intkey=r[8] REG[8] = i:1007 14 IdxRowid 1 2 0 00 r[2]=rowid REG[2] = i:1007 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 REG[2] = i:1007 16 Column 0 0 3 00 r[3]=cv.cv_s REG[3] = i:1 17 Column 0 1 4 00 r[4]=cv.cv_o REG[4] = s16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 18 Variable 1 5 0 00 r[5]=parameter(1,) REG[5] = i:28 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt REG[3] = i:1 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt REG[4] = s16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt REG[5] = i:28 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) REG[9] = s21[04092C01B8B9F4D5F3E8432E86E248C2..,.......C...H.](8) 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] REG[9] = s21[04092C01B8B9F4D5F3E8432E86E248C2..,.......C...H.](8) REG[2] = i:1007 24 Halt 0 0 0 00 Trace for the next successful update of the same row: SQL: [update cv set cv_t=? where cv_s=? and cv_o=?] VDBE Program Listing: 0 Init 0 25 0 00 Start at 25 1 Null 0 1 2 00 r[1..2]=NULL 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; sqlite_autoindex_cv_1 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 5 Variable 2 6 0 00 r[6]=parameter(2,) 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 7 Variable 3 7 0 00 r[7]=parameter(3,) 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 9 Affinity 6 1 0 D 00 affinity(r[6]) 10 SeekGE 1 15 6 2 00 key=r[6..7] 11 IdxGT 1 15 6 2 00 key=r[6..7] 12 IdxRowid 1 8 0 00 r[8]=rowid 13 Seek 0 8 0 00 intkey=r[8] 14 IdxRowid 1 2 0 00 r[2]=rowid 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 16 Column 0 0 3 00 r[3]=cv.cv_s 17 Column 0 1 4 00 r[4]=cv.cv_o 18 Variable 1 5 0 00 r[5]=parameter(1,) 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] 24 Halt 0 0 0 00 25 Transaction 0 1 56 0 01 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 27 Goto 0 1 0 00 VDBE Trace: 0 Init 0 25 0 00 Start at 25 SQL-trace: update cv set cv_t=? where cv_s=? and cv_o=? 25 Transaction 0 1 56 0 01 26 TableLock 0 74 1 cv 00 iDb=0 root=74 write=1 27 Goto 0 1 0 00 1 Null 0 1 2 00 r[1..2]=NULL REG[1] = NULL 2 OpenWrite 0 74 0 3 00 root=74 iDb=0; cv 3 OpenWrite 1 75 0 k(2,nil,nil) 02 root=75 iDb=0; sqlite_autoindex_cv_1 4 Explain 0 0 0 SEARCH TABLE cv USING INDEX sqlite_autoindex_cv_1 (cv_s=? AND cv_o=?) 00 5 Variable 2 6 0 00 r[6]=parameter(2,) REG[6] = i:1 6 IsNull 6 15 0 00 if r[6]==NULL goto 15 REG[6] = i:1 7 Variable 3 7 0 00 r[7]=parameter(3,) REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 8 IsNull 7 15 0 00 if r[7]==NULL goto 15 REG[7] = t16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 9 Affinity 6 1 0 D 00 affinity(r[6]) 10 SeekGE 1 15 6 2 00 key=r[6..7] REG[6] = i:1 11 IdxGT 1 15 6 2 00 key=r[6..7] 12 IdxRowid 1 8 0 00 r[8]=rowid REG[8] = i:1007 13 Seek 0 8 0 00 intkey=r[8] REG[8] = i:1007 14 IdxRowid 1 2 0 00 r[2]=rowid REG[2] = i:1007 15 IsNull 2 24 0 00 if r[2]==NULL goto 24 REG[2] = i:1007 16 Column 0 0 3 00 r[3]=cv.cv_s REG[3] = i:1 17 Column 0 1 4 00 r[4]=cv.cv_o REG[4] = s16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 18 Variable 1 5 0 00 r[5]=parameter(1,) REG[5] = i:29 19 HaltIfNull 1299 2 3 cv.cv_s 01 if r[3]=null halt REG[3] = i:1 20 HaltIfNull 1299 2 4 cv.cv_o 01 if r[4]=null halt REG[4] = s16[B8B9F4D5F3E8432E86E248C2EBE14D5D......C...H...M]](8) 21 HaltIfNull 1299 2 5 cv.cv_t 01 if r[5]=null halt REG[5] = i:29 22 MakeRecord 3 3 9 DAD 00 r[9]=mkrec(r[3..5]) REG[9] = s21[04092C01B8B9F4D5F3E8432E86E248C2..,.......C...H.](8) 23 Insert 0 9 2 cv 05 intkey=r[2] data=r[9] REG[9] = s21[04092C01B8B9F4D5F3E8432E86E248C2..,.......C...H.](8) REG[2] = i:1007 24 Halt 0 0 0 00 Hope this helps. Let me know if there is anything I can do to gather more data. NB: I originally mentioned OS X being the only affected platform but this trace was actually obtained on Linux. uname: 3.2.0-23-generic #36-Ubuntu SMP Tue Apr 10 20:39:51 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
