MySQL Bugs: #23383: mysql_affected_rows() returns different values than mysql_stmt_affected

Bug #23383	mysql_affected_rows() returns different values than mysql_stmt_affected_rows()
Submitted:	17 Oct 2006 17:42	Modified:	30 Nov 2006 20:21
Reporter:	Ulf Wendel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: C API (client library)	Severity:	S3 (Non-critical)
Version:	5.0.26/4.1/5.1BK	OS:	Linux (SuSE 10.1, 2.6.16.21-0.25-def)
Assigned to:	Tomash Brechko	CPU Architecture:	Any
Tags:	C_API

Description:
mysql_affected_rows() and mysql_stmt_affected_rows() seem to return different values for the following sequence of SQL statement.

DROP TABLE IF EXISTS test
CREATE TABLE test(id INT NOT NULL AUTO_INCREMENT, label VARCHAR(32), PRIMARY KEY(id))

[1] INSERT INTO test(id, label) VALUES (1, 'a')
[2] INSERT INTO test(id, label) VALUES (1, 'a')
[3] UPDATE TEST SET label = 'b' WHERE id = 2

It's planned that [1] works and [2], [3] fail. The functions return the following values:

mysql_affected_rows()       [1] 1   [2] -1   [3] -1
mysql_stmt_affected_rows()  [1] 1   [2] -1   [3] -1

As I understand the documentation, the functions should not behave differently. This is the output of the attached test program on the test computer:

nixnutz@linux-fupx:~> rm ./mysqlundoc; gcc -o mysqlundoc `/usr/local/mysql/bin/mysql_config --cflags` mysql_stmt_affected_rows.c `/usr/local/mysql/bin/mysql_config --libs` -Wall -pedantic; ./mysqlundoc
In file included from mysql_stmt_affected_rows.c:5:
/usr/local/mysql/include/mysql.h:114: warning: ISO C90 does not support ‘long long’

Starting ...

... mysql_get_client_info() = 5.0.26
... mysql_get_client_version() = 50026
... mysql_get_server_info() = 5.0.26-debug
... mysql_get_server_version() = 50026
... mysql_get_host_info() = Localhost via UNIX socket
... mysql_get_proto_info() = 10

... Test with mysql_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = -1
...    UPDATE unknown = -1
... Test with mysql_stmt_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = 1
...    UPDATE unknown = 1

Done!

The problem affects PHP ext/mysqli mysqli_stmt_affected_rows(). It's possible to write a test case for PHP as well. PHP return 0 instead of 1 for mysql_stmt_affected_rows() errors but that is wrong anyway. See attached test case, verified with PHP 5.1.6, MySQL 5.0.26, ext/mysqli from dev.mysql.com. 

The problem seems not to be version/OS dependent on 32bit. Didn't check 64bit.

Thanks!

How to repeat:
Tests attached files

PHP test case

Attachment: mysqli_stmt_affected_rows.php (application/x-php, text), 2.45 KiB.

C-API test case

Attachment: mysql_stmt_affected_rows.c (text/x-csrc), 4.48 KiB.

Uuups, cut&paste that should read:

It's planned that [1] works and [2], [3] fail. The functions return the
following values:

mysql_affected_rows()       [1] 1   [2] -1   [3] -1
mysql_stmt_affected_rows()  [1] 1   [2]  1   [3]  1

Thank you for the bug report.

miguel@hegel:~/dbs/5.1> ./bug23383 

Starting ...

... mysql_get_client_info() = 5.1.12-beta
... mysql_get_client_version() = 50112
... mysql_get_server_info() = 5.1.12-beta-debug
... mysql_get_server_version() = 50112
... mysql_get_host_info() = Localhost via UNIX socket
... mysql_get_proto_info() = 10

... Test with mysql_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = -1
...    UPDATE unknown = -1
... Test with mysql_stmt_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = 1
...    UPDATE unknown = 1

Done!
------------------------------------------------------
miguel@hegel:~/dbs/5.0> ./bug23383 

Starting ...

... mysql_get_client_info() = 5.0.27
... mysql_get_client_version() = 50027
... mysql_get_server_info() = 5.0.27-debug
... mysql_get_server_version() = 50027
... mysql_get_host_info() = Localhost via UNIX socket
... mysql_get_proto_info() = 10

... Test with mysql_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = -1
...    UPDATE unknown = -1
... Test with mysql_stmt_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = 1
...    UPDATE unknown = 1

Done!
------------------------------------------------------
miguel@hegel:~/dbs/4.1> ./bug23383 

Starting ...

... mysql_get_client_info() = 4.1.22
... mysql_get_client_version() = 40122
... mysql_get_server_info() = 4.1.22-debug
... mysql_get_server_version() = 40122
... mysql_get_host_info() = Localhost via UNIX socket
... mysql_get_proto_info() = 10

... Test with mysql_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = -1
...    UPDATE unknown = -1
... Test with mysql_stmt_affected_rows():
...    INSERT OK = 1
...    INSERT duplicate = 1
...    UPDATE unknown = 1

Done!

Return value of mysql_affected_rows() in case of an error is undefined, so the complaint that something behaves inconsistently when behaviour is undefined is questionable. 

I will see what can be done about it.

I'm fine with a different behaviour of the functions mysql_affected_rows() and mysql_stmt_affected_rows() although it does not seem very "beautiful" for me from an API users standpoint. 

However, can we update the documentation on http://dev.mysql.com/doc/refman/5.1/en/mysql-affected-rows.html ?

"An integer greater than zero indicates the number of rows affected or retrieved. Zero indicates that no records were updated for an UPDATE statement, no rows matched the WHERE clause in the query or that no query has yet been executed. -1 indicates that the query returned an error or that, for a SELECT  query, mysql_affected_rows() was called prior to calling mysql_store_result(). Because mysql_affected_rows() returns an unsigned value, you can check for -1 by comparing the return value to (my_ulonglong)-1 (or to (my_ulonglong)~0, which is equivalent)."

It seems that "-1 indicated that the query returned an error" was misleading both for me and Miguel. It is bad practice not to check for an error of mysql_query() etc. before calling mysql_affected_rows(), but I would appreciate an extra note in the documentation anyway. That should solve the problem for users like me which do not do their home work and forget about proper error handling.

Ulf

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15345

ChangeSet@1.2551, 2006-11-15 14:19:07+03:00, kroki@moonlight.intranet +2 -0
  BUG#23383: mysql_affected_rows() returns different values than
  mysql_stmt_affected_rows()
  
  The problem was that affected_rows for prepared statement wasn't updated
  in the client library on the error.  The solution is to always update
  affected_rows, which will be equal to -1 on the error.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15472

ChangeSet@1.2551, 2006-11-17 12:14:36+03:00, kroki@moonlight.intranet +3 -0
  BUG#23383: mysql_affected_rows() returns different values than
  mysql_stmt_affected_rows()
  
  The problem was that affected_rows for prepared statement wasn't updated
  in the client library on the error.  The solution is to always update
  affected_rows, which will be equal to -1 on the error.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/15473

ChangeSet@1.2551, 2006-11-17 12:21:32+03:00, kroki@moonlight.intranet +3 -0
  BUG#23383: mysql_affected_rows() returns different values than
  mysql_stmt_affected_rows()
  
  The problem was that affected_rows for prepared statement wasn't updated
  in the client library on the error.  The solution is to always update
  affected_rows, which will be equal to -1 on the error.

Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.

Fixed in 5.0.32 and 5.1.13

Noted in 5.0.32, 5.1.14 changelogs.

mysql_affected_rows() could return values different from 
mysql_stmt_affected_rows() for the same sequence of statements.