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

[17 Oct 2006 17:42] Ulf Wendel
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
[17 Oct 2006 17:43] Ulf Wendel
PHP test case

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

[17 Oct 2006 17:43] Ulf Wendel
C-API test case

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

[17 Oct 2006 17:45] Ulf Wendel
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
[17 Oct 2006 23:30] MySQL Verification Team
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!
[25 Oct 2006 9:29] Konstantin Osipov
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.
[25 Oct 2006 9:46] Ulf Wendel
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
[15 Nov 2006 11:22] Bugs System
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.
[17 Nov 2006 9:17] Bugs System
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.
[17 Nov 2006 9:24] Bugs System
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.
[17 Nov 2006 12:26] Tomash Brechko
Queued to 4.1-runtime, 5.0-runtime, 5.1-runtime.
[30 Nov 2006 1:31] Konstantin Osipov
Fixed in 5.0.32 and 5.1.13
[30 Nov 2006 20:21] Paul DuBois
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.