Odd "rows affected" values using DBI on MySQL

LassiLantar has asked for the wisdom of the Perl Monks concerning the following question:

I'm writing a script using a MySQL database. I'm running an update query like this:

$rows_affected = $dbh->do("UPDATE meters SET description=? WHERE name=
+?", undef, $info, $meter);
[download]

Sometimes this query is issued with $info equal to the value already stored in that description field. The strange part is that $rows_affected is getting set to 1 even if $info is exactly the same as the existing value! I logged into the database manually and ran the query, which returned:

Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0
[download]

MySQL and Perl for the Web (p.37, footnote) says that "affected by" means "changed", and that do() should indeed return 0 in this case, unless I have ";mysql_client_found_rows=1" set, which I do not.

Guesses? Clues? Answers? Berating? I wish I had more information to provide about the problem...

Many thanks,
LassiLantar

Update: Fixed it, as per suggestion, by making the WHERE clause read ...WHERE name=? AND description=?", undef, $info, $meter, $info... Which resulted in return values of 0E0. Anyone know why MySQL or DBI is refusing to return just plain 0 in this case, and why it's returning 1 without the extra check?

Comment on Odd "rows affected" values using DBI on MySQL Select or Download Code

Replies are listed 'Best First'.
Re: Odd "rows affected" values using DBI on MySQL by etcshadow (Priest) on Jul 24, 2004 at 19:57 UTC
Well, if that's what you want, you can just make your query be: `$rows_affected = $dbh->do("UPDATE meters SET description=? WHERE name= +? and description <> ? ", undef, $info, $meter, $info);` [download] At least in other databases I'm familiar with, the "rows affected" means the rows that fit the where clause. It does not actually perform a diff against the old values, and only report the changes. `------------ :Wq Not an editor command: Wq` [download]	[reply] [d/l] [select]
Re^2: Odd "rows affected" values using DBI on MySQL by LassiLantar (Monk) on Jul 24, 2004 at 20:08 UTC
Changed my query to `$rows_affected = $dbh->do("UPDATE meters SET description=? WHERE name= + ? and description <> ? ", undef, $info, $meter, $info);` [download] And started getting "0E0" as my return value for rows affected instead of 1. Anyone know why not just 0? Is that just "Zero to the Zero"? In any case, it'll do =) Thanks etcshadow! Peace, LassiLantar P.S. Your signature is brilliant. Every vi user should see that.	[reply] [d/l]
Re^3: Odd "rows affected" values using DBI on MySQL by ercparker (Hermit) on Jul 24, 2004 at 20:41 UTC
"do" returns "0E0" if no rows are affected. This will get your zero: `$rows_affected += 0;` [download]	[reply] [d/l]
Re^4: Odd "rows affected" values using DBI on MySQL by LassiLantar (Monk) on Jul 24, 2004 at 20:46 UTC
Re^3: Odd "rows affected" values using DBI on MySQL by heroin_bob (Sexton) on Jul 25, 2004 at 00:21 UTC
The DBI documentation on cpan might help. It returns the number of rows affected on a successful query, and undef in the event of an error. If there are zero affected rows it returns "0E0", thus returning true for all successful queries. I snagged this off the documentation, which it says the default do method is logically similar to: `sub do { my($dbh, $statement, $attr, @bind_values) = @_; my $sth = $dbh->prepare($statement, $attr) or return undef; $sth->execute(@bind_values) or return undef; my $rows = $sth->rows; ($rows == 0) ? "0E0" : $rows; # always return true if no error }` [download] Hope this helps... ~hb	[reply] [d/l]
Re^3: Odd "rows affected" values using DBI on MySQL by grantm (Parson) on Jul 24, 2004 at 21:49 UTC
'0E0' is zero in scientific notation (ie: 0 * 10^0) but it's special in Perl because in a boolean context it evaluates to True (whereas 0 would obviously be false). This allows you to do something like ... `$dbh->do($sql) or die "Update failed"` ... and it would not call die even if no rows were affected. In practice though when you're using DBI it's simpler and safer to enable RaiseError and use eval to catch the errors.	[reply] [d/l]
Re^4: Odd "rows affected" values using DBI on MySQL by tilly (Archbishop) on Jul 24, 2004 at 21:56 UTC
Re^2: Odd "rows affected" values using DBI on MySQL by CountZero (Bishop) on Jul 24, 2004 at 22:01 UTC
I think that deep down somewhere in the MySQL docs there is a mention that updating an existing record with the same data it already contains, get optimized away (and hence does not trigger an update of the TIMESTAMP field). CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply]