Bug #37658 bad updated rows count
Submitted: 26 Jun 2008 8:45 Modified: 26 Jun 2008 15:38
Reporter: Gal Levinsky Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:mysql-connector-java-5.1.6 OS:Windows
Assigned to: CPU Architecture:Any
Tags: count, executeUpdate, mysql-connector-5.1.6, MySQL5.1, rows, UPDATE

[26 Jun 2008 8:45] Gal Levinsky
Description:
Hi,

I'm using the following config:
MySQL JDBC driver version: 5.1.6
MySQL DB version: 5.1
Java version: 6

When using java.sql.statment to executeUpdate SQL, the returned value (the updated rows count) is invalid.
Instead of 1 row (the actual value in DB), the number is 7810893.

After debugging the JDBC driver a bit, I have noticed that the bad number assignment occurs in class MysqlIO and in the private method - buildResultSetWithUpdates.
In that method, the following code lines :
        try {
            if (this.useNewUpdateCounts) {
                updateCount = resultPacket.newReadLength(); <<<<This line returns bad values.
                updateID = resultPacket.newReadLength();
            } else {
                updateCount = resultPacket.readLength();
                updateID = resultPacket.readLength();
            }

After further debug, I noticed that resultPacket.newReadLength enter the case where sw equal to 253, and give the bad result.

*****
Please notice that this error occurs only on "INSERT" SQL statements and not with "DELETE" SQL statements.
*****

How to repeat:
With the above settings:
1. Prepare an "INSERT" SQL statement, with one row to insert.
2. Run the SQL with java.sql.statement
3. View the returned rows count.
[26 Jun 2008 12:36] Mark Matthews
Which version of the *Server* are you using? There was a particular version of 5.1 that returned garbage for update counts, when the client asked for "found rows", vs. "changed rows" (which the JDBC and ODBC drivers do).
[26 Jun 2008 12:48] Gal Levinsky
I'm using Tomcat 6.0.16 as an application/web server.

Please read my bug details.
As you know, when calling java.sql.Statement.executeUpdate(SQL), it should return the number of the affected rows.

During debugging I noticed that the method which responsible for the error is in the MySQL JDBC.
For some reason it is entering the case where sw=253, and causing the bad number.
I don't know what sw means, since lack of documentation, and I also don't know what is the purpose of that conversion.

It is most likely a bug.
All details should already appear in my bug description.
Please let me know if something is unclear.

Regards,
Gal.
[26 Jun 2008 12:50] Gal Levinsky
.
[26 Jun 2008 13:00] Mark Matthews
Sorry, I wasn't clear in my previous request. Please tell us what version of the *mysql server* you are using, not just major/minor, but also release. There were versions of 5.1 (that aren't that old) that returned *garbage* for update counts to clients like JDBC and ODBC.

My first reaction is that you're using one of these versions of the server.

If not, we'd need a repeatable testcase that demonstrates the bug before we could investigate this.

The method Buffer.newReadLength() is an "internal" function which is part of an implementation of the MySQL protocol. If it follows a switch path on 253, that's because it's what's required by the protocol. If the number it's reading ends up being larger than expected, that's either because the server is returning the wrong update count, or the driver is reading in the wrong part of the packet.

(without a testcase that reproduces the behavior I can only guess that it's the server sending back a bad update count, since we've seen it before, and you're in a major/minor release that has had this bug, but we don't yet know what *release* *you're* using).
[26 Jun 2008 13:09] Gal Levinsky
mysql> SELECT VERSION();
+--------------------------------+
| VERSION()                      |
+--------------------------------+
| 5.1.20-beta-community-nt-debug |
+--------------------------------+
1 row in set (0.04 sec)
[26 Jun 2008 13:28] Mark Matthews
You're more than likely experiencing Bug#29692, which was fixed in MySQL-5.1.21. MySQL-5.1 is currently at version 5.1.25rc, I'd suggest upgrading, and testing your application. If this fixes the bug, we'll close this.
[26 Jun 2008 14:11] Gal Levinsky
Yes. It was the version.

Thanks.
[26 Jun 2008 14:16] Gal Levinsky
Please close the bug.