Skip to Content.
Sympa Menu

freetds - RE: [freetds] New BUG: update/delete returns -1 instead of correctaffected rows on Merge Publication on MSSQL 2000 SP3a

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO Frediano" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] New BUG: update/delete returns -1 instead of correctaffected rows on Merge Publication on MSSQL 2000 SP3a
  • Date: Fri, 9 Jan 2004 11:05:18 +0100

>
> On Fri, 09 Jan 2004, Stefano Coletta <s.coletta AT uni.it> wrote:
> > Lowden, James K wrote:
> >
> > >>I'm almost sure to have found a bug in the freetds lib (even
> > >>0.62rc3)
> > >>that occurs only when I try to do an update or delete against
> > >>Microsoft
> > >>SQL Server 2000 SP3a while the database is in Merge
> Replication. The
> > >>problem arises when I try to read the "rows affected"
> property which
> > >>returns always -1 instead of the correct number of affected
> > >>rows.
> > >>
...
> > >
> > Here they are. I've gzipped the files so they are very small.
>
> 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
>

This is not transaction state but statement executed (0xC5 update 0xC1
select)

> 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;
>

Here 0xF6 is raiserror, MS return failure executing this instruction
however raiserrror does not fail, it just raise an 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
>

0x01 is just more coming without row. In ODBC I ignore this token (as MS
does)

> ff <= TDS_DONEINPROC
> 11 00 <= 0x0011, done count valid,
> more coming
> c1 00 <= transaction state 0x00c1 ?? (OK....)
> 01 00 00 00 <= 0x0001 row affected
>

This is a count inside a procedure, also ignored by ODBC cause there is
no row.

> ff <= TDS_DONEINPROC
> 01 00 <= 0x0010, done count valid
> 00 00 <= transaction state zero
> 00 00 00 00 <= zero rows affected
>

0x00 ?? However there is no rowcount.

> 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
>

0xC5 is update, our statement and 0 rows are returned. So 0 would be
returned by ODBC.

> ========
>
> Not quite the same, you know? ;-)
>

No :) ODBC should return (correctly 0) however I don't know the CTLib
behaviour in this situation... our FreeTDS return even TDS_DONEINPROC
however I don't know the expected behavior (that is the Sybase CTLib
bahavior)...

> 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

It return -1 cause it catch TDS_DONEINPROC without row (so rows_affected
got set to TDS_NO_COUNT)

> 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.
>
> 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.
>

I expect Bill reply, he knows CTLib better than I do.

> 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.
>

...

freddy77



  • RE: [freetds] New BUG: update/delete returns -1 instead of correctaffected rows on Merge Publication on MSSQL 2000 SP3a, ZIGLIO Frediano, 01/09/2004

Archive powered by MHonArc 2.6.24.

Top of Page