[freetds] New BUG: update/delete returns -1 instead of correct
affected rows on Merge Publication on MSSQL 2000 SP3a
Stefano Coletta
s.coletta at uni.it
Fri Jan 9 10:08:29 EST 2004
James K. Lowden wrote:
>On Fri, 09 Jan 2004, Stefano Coletta <s.coletta at uni.it> wrote:
>
>
>>Lowden, James K wrote:
>>Yes, you've found a bug. Definitely a post-release issue, and a hard one,
>>IMO. Thank you for the logs.
>>
>>The issue afaik isn't so much that one server is replicated, as one of how
>>it responds to your update statement. I can't explain what I'm seeing.
>>Let me parse the log for you, and show you what I mean.
>>
>> ========
>>
>>The good one, freetds.log.ok:
>>
>>Received packet @ 2004-01-08 20:06:13.782263
>>0000 fd <= TDS_DONE
>> 10 00 <= 0x0010, done count valid
>> c5 00 <= transaction state ??
>> 01 00 00 00 <= 0x0001 row affected
>>
>>The transaction state should be between 0 and 5, not 0xC5. Microsoft
>>evidently
>>uses different values, which we apparently half-understand; there's a bit
>>of
>>weirdness in tds_process_end():
>>
>> /* ignore error calling RAISERROR */
>> if (TDS_IS_MSSQL(tds) && state == 0xf6)
>> tmp &= ~TDS_DONE_ERROR;
>>
>>Interesting to note, by the way, that your log reflects an unimplemented
>>ct_options call, one that's related to transactions:
>>
>>2004-01-08 20:06:13.727183 ct_option: UNIMPLEMENTED 7
>>
>>>From include/cspublic:
>>
>>#define CS_OPT_CHAINXACTS 7
>>
>>However, I doubt this matters. IIRC, ct_options in 0.61.2 returned
>>CS_SUCCEED
>>all the time, and chained transactions are the default, so CS_SUCCEED was
>>accurate, even if accidentally.
>>
>>Anyway, the good log shows a normal TDS_DONE message, with 1 row affected.
>>
>>
>> ========
>>
>>The bad one, freetds.log.debug:
>>
>>Received packet @ 2004-01-08 19:52:58.992128
>>0000 ff <= TDS_DONEINPROC
>> 01 00 <= 0x0010, done count valid
>> c0 00 <= transaction state 0x00c0 ?? (OK....)
>> 00 00 00 00 <= zero rows affected
>>
>> ff <= TDS_DONEINPROC
>> 11 00 <= 0x0011, done count valid, more coming
>> c1 00 <= transaction state 0x00c1 ?? (OK....)
>> 01 00 00 00 <= 0x0001 row affected
>>
>> ff <= TDS_DONEINPROC
>> 01 00 <= 0x0010, done count valid
>> 00 00 <= transaction state zero
>> 00 00 00 00 <= zero rows affected
>>
>> 79 <= TDS_RETURNSTATUS
>> 00 00 00 00 <= procedure returned zero
>>
>>0020 fd <= TDS_DONE
>> 10 00 <= 0x0010, done count valid
>> c5 00 <= transaction state ??
>> 00 00 00 00 <= zero rows affected
>>
>> ========
>>
>>Not quite the same, you know? ;-)
>>
>>First, you tell me how an UPDATE statement results in not one but three
>>"done in
>>proc" responses (when no procedure was invoked). Then I'll tell you we
>>don't
>>expect that response for an UPDATE, and that's why our code falls down and
>>gives
>>you a -1 rows affected. We don't manage, it would seem, to notice that
>>the
>>second "done in proc" indicates there was one row affected. And to tell
>>the
>>truth, we don't know enough about how this is supposed to work to get it
>>right.
>>:-( Say I. Maybe someone else knows better.
>>
>>
As you guessed I don't know why it returns three replies :(
>
>
>>Bill Thompson and Frediano have done most of the work lately decoding the
>>stream. In working with cursors and dynamic SQL, they've seen some
>>oddball responses from the server, and they might well have some light to
>>shed. Me? I just fill your screen with words to keep your eyes busy.
>>
>>This is a hard one to do correctly. We don't know you sent an UPDATE, we
>>just parse the server's responses. It's totally unclear to me how we're
>>expected to divine that the second DONEINPROC carries good rows-affected
>>information, and to ignore the last TDS_DONE, which says no rows were.
>>
>>Peter says it might be triggers, which makes very good sense. IF triggers
>>always react this way, and produce a reliable stream of DONEINPROC
>>messages that carry predictable rows-affected information, then maybe
>>maybe we have a shot at this. But I know triggers can be nested and
>>chained and even IIRC recursive nowadays.
>>
>>I even tried a trivial trigger:
>>
>>
>>
>>create table t (t int not null)
>>go
>> create trigger tup on t for update
>>
>>
> as
> select * from inserted
>
>
>>go
>>insert t values (1)
>>go
>>
>>
>(1 row affected)
>
>
>>update t set t=2 where t=1
>>go
>>
>>
> t
> -----------
> 2
>
>(1 row affected, return status = 0)
>
>The packet?
>
>Received packet @ 23:05:45.709557
>0000 81 01 00 00 00 08 00 38-01 74 00 d1 02 00 00 00
>0010 ff 11 00 c1 00 01 00 00-00 79 00 00 00 00 fd 10
>0020 00 c5 00 01 00 00 00 -
>
>Briefly: TDS7_RESULT, ROW, DONEINPROC (1 row affected), RETURNSTATUS, DONE
>(1 row affected). I guess we got lucky; the DONE message carries the
>right answer.
>
>So, it's not merely triggers, but perhaps it's your particular
>triggers....
>
>
Here it is the update trigger from Micro$oft :)
create trigger upd_0237C092D1BD4D6994046F96544217FB on [dbo].[RADPOOL]
FOR UPDATE AS if sessionproperty('replication_agent') = 1 and (select
trigger_nestlevel()) = 1
return /* Declare variables */
declare @article_rows_updated int
select @article_rows_updated = count(*) from inserted
declare @contents_rows_updated int, @updateerror int
declare @bm varbinary(500), @missingbm varbinary(500), @lineage
varbinary(255), @cv varbinary(2048)
declare @tablenick int, @nick int, @ccols int, @partchange int,
@joinchange int
declare @partchangebm varbinary(500), @joinchangebm varbinary(500)
declare @oldmaxversion int
set nocount on
set @tablenick = 6522002 select @oldmaxversion=
maxversion_at_cleanup from dbo.sysmergearticles where nickname = @tablenick
/* Use intrinsic funtion to set bits for updated columns */
set @bm = columns_updated()
/* only do the map down when needed */
set @missingbm = 0x00
set @partchangebm = 0x00
set @joinchangebm = 0x00
if update([rowguid])
begin
if @@trancount > 0
rollback tran
RAISERROR (20062, 16, -1)
end
/* See if the partition might have changed */
if @partchangebm = 0x0
set @partchange = 0
else
set @partchange= { fn INTERSECTBITMAPS (@bm, @partchangebm) }
/* See if a column used in a join filter changed */
if @joinchangebm = 0x0
set @joinchange = 0
else
set @joinchange= { fn INTERSECTBITMAPS (@bm, @joinchangebm) }
execute master..xp_mapdown_bitmap 0x0001, @bm output
exec dbo.sp_MSgetreplnick @nickname = @nick output
select @ccols = 9 set @lineage = { fn
UPDATELINEAGE(0x0, @nick, @oldmaxversion+1) }
set @cv = { fn INITCOLVS(@ccols, @nick) }
if (@@error <> 0)
begin
goto FAILURE
end
set @cv = { fn UPDATECOLVBM(@cv, @nick, @bm, @missingbm, { fn
GETMAXVERSION(@lineage) }) }
update ctsv_0237C092D1BD4D6994046F96544217FB
set lineage = { fn UPDATELINEAGE(lineage, @nick, @oldmaxversion+1) },
generation = A.gen_cur,
joinchangegen = case when (@joinchange = 1) then A.gen_cur else
joinchangegen end,
partchangegen = case when (@partchange = 1) then A.gen_cur else
partchangegen end,
colv1 = { fn UPDATECOLVBM(colv1, @nick, @bm,
@missingbm, { fn GETMAXVERSION({ fn UPDATELINEAGE(lineage, @nick,
@oldmaxversion+1) }) }) } FROM inserted as I JOIN
ctsv_0237C092D1BD4D6994046F96544217FB as V
ON (I.rowguidcol=V.rowguid)
and V.tablenick = @tablenick
JOIN (select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @tablenick) as A
ON V.tablenick = A.nickname
select @updateerror = @@error, @contents_rows_updated = @@rowcount
if @article_rows_updated <> @contents_rows_updated
begin
insert into ctsv_0237C092D1BD4D6994046F96544217FB (tablenick,
rowguid, lineage, colv1, generation, partchangegen, joinchangegen)
select @tablenick, rowguidcol, @lineage, @cv, A.gen_cur,
case when (@joinchange = 1 or @partchange = 1) then A.gen_cur
else NULL end,
case when @joinchange = 1 then A.gen_cur else NULL end
from inserted,
(select top 1 nickname, gen_cur = isnull(gen_cur, 0) from
dbo.sysmergearticles where nickname = @tablenick) as A
where rowguidcol not in (select rowguid from
ctsv_0237C092D1BD4D6994046F96544217FB where tablenick = @tablenick)
if @@error <> 0
GOTO FAILURE
end
return
FAILURE:
if @@trancount > 0
rollback tran
raiserror (20041, 16, -1)
return
I think you have a similar one on another replicated database table.
>For the record, there is one distinguishing mark on your second
>DONEINPROC: the TDS_DONE_MORE_RESULTS bit (0x01) set in the status word.
>Obviously, I don't know if that's a reliable marker for anything, but it
>might be a clue.
>
>Thanks again for the logs. You've got Peter's attention, always a good
>omen.
>
>
I'm glad of this :) As always, if you need other infos just let me know.
Good luck!
>Regards,
>
>--jkl
>_______________________________________________
>FreeTDS mailing list
>FreeTDS at lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>
>
--
Stefano Coletta
Unidata S.p.a.
Via Portuense, 1555 - 00050 Roma ( Ponte Galeria )
Italy
Tel +39 06650951
Fax +39 0665095002
E-mail s.coletta at uni.it
http://www.mindcreations.com
More information about the FreeTDS
mailing list