List:MySQL and Perl« Previous MessageNext Message »
From:Michael Widenius Date:May 31 1999 10:25am
Subject:Re: Drivers giving different results
View as plain text  
>>>>> "Jochen" == Jochen Wiedmann <joe@stripped> writes:

Jochen> Hi, Monty,

Jochen> I recently had a discussion with some DBD::mysql users, in particular
Jochen> Tim Bunce the DBI maintainer, about the following:

Jochen> If I do a

Jochen> 	UPDATE foo SET col=col

Jochen> then the MySQL optimizer detects that no work needs to be done, which
Jochen> is of course a good thing. The problem is that mysql_affected_rows
Jochen> seems to return the value 0, in other words the number of *physically*
Jochen> affected rows. This seems to break some peoples reliabiliy checks, which
Jochen> expects the number of rows being returned.

Jochen> On the other hand the MySQL server obviously knows the number of matched
Jochen> rows:

mysql> update user set Host=Host;
Jochen> 	Query OK, 0 rows affected (0.00 sec)
Jochen> 	Rows matched: 11  Changed: 0  Warnings: 0

Jochen> It seems this information is now returned by mysql_info(). (I didn't
Jochen> verify.)

Jochen> I am now asked to change the DBD::mysql driver not to return the result
Jochen> of mysql_affected_rows(), but read mysql_info() and parse the output to
Jochen> return the matched rows. This is something I definitely don't like,
Jochen> because it is slow and unsafe.

Jochen> My question is whether you can either

Jochen>   - supply the same info via a new function, mysql_matched_rows(), say or
Jochen>   - change mysql_affected_rows to return the number of matched rows


You can currently get the above behaviour if you add the flag
CLIENT_FOUND_ROWS to the last argument of mysql_real_connect().

One problem is that ANY SQL server is allowed to optimize the query:

UPDATE foo SET col=col


UPDATE foo SET col=col WHERE col<>col

I think that one should strongly discourage the use of affected rows in the
above manner as any SQL server may add the above optimisation in any
updated version!

Re: Drivers giving different resultsJochen Wiedmann25 May
  • Re: Drivers giving different resultsVivek Khera25 May
  • Re: Drivers giving different resultsTim Bunce26 May
  • Re: Drivers giving different resultsDan Busarow29 May
    • Re: Drivers giving different resultsJochen Wiedmann29 May
  • Re: Drivers giving different resultsMichael Widenius2 Jun
    • Re: Drivers giving different resultsTim Bunce2 Jun
      • Re: Drivers giving different resultsMichael Widenius3 Jun
        • Re: Drivers giving different resultsTim Bunce3 Jun
          • Re: Drivers giving different resultsMichael Widenius3 Jun
            • Re: Drivers giving different resultsTim Bunce3 Jun
Re: Drivers giving different resultsJochen Wiedmann26 May