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

Hi!

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

to

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!

Regards,
Monty
Thread
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