On 9/22/15, Hugues Bruant <hugues at aerofs.com> wrote: >> >> 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
The trace shows that you have this value set to 0, not 1. And apparently there is no row with cv_s=0 and cv_o=b8b9f4... > 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 > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp drh at sqlite.org
