MySQL Bugs: #24582: PreparedStatement's executeUpdate or getUpdateCount Return bad value

Bug #24582	PreparedStatement's executeUpdate or getUpdateCount Return bad value
Submitted:	25 Nov 2006 4:04	Modified:	16 Feb 2007 14:54
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	mysql-connector-java-5.0.4	OS:	Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	executeUpdate, getUpdateCount, PreparedStatement, Update Ignore

Description:
When Update Ignore is used with the Update operation via the PreparedStatement and there is nothing change in the database.

The executeUpdate and getUpdateCount always return 1

How to repeat:
Create a table with some unique key
Try to update a row using some of the key being used by other row for the same field (using update ignore and PreparedStatement)

Suggested fix:
The methods should return the number of rows affected.

Do you use that UPDATE IGNORE in stored procedure? Outside SP I have:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.0.36 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tu(c1 int unique);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tu values (1), (2), (3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> update ignore tu set c1=1 where c1<3;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> prepare stmt from 'update ignore tu set c1=1 where c1<?';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @a=3;
Query OK, 0 rows affected (0.01 sec)

mysql> execute stmt using @a;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 2  Changed: 0  Warnings: 0

mysql> deallocate prepare stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //

...

mysql> select * from tu//
+------+
| c1   |
+------+
|    2 |
|    3 |
|   10 |
+------+
3 rows in set (0.00 sec)

mysql> prepare stmt from 'update ignore tu set c1=1 where c1=?'//
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> set @id=2//
Query OK, 0 rows affected (0.00 sec)

mysql> execute stmt using @id//
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

So, correct numbers of rows matched and changed are reported. With SP though:

mysql> create procedure psp(id int) 
begin 
prepare stmt from 'update ignore tu set c1=10 where c1=?'; 
set @id=id; 
execute stmt using @id; 
deallocate prepare stmt; 
end//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tu//
+------+
| c1   |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

mysql> call psp(1)//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tu//
+------+
| c1   |
+------+
|    2 |
|    3 |
|   10 |
+------+
3 rows in set (0.00 sec)

mysql> call psp(2)//
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tu//
+------+
| c1   |
+------+
|    2 |
|    3 |
|   10 |
+------+
3 rows in set (0.00 sec)

We do always have 0 rows affected.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".