Bug #29692 Single row inserts can incorrectly report a huge number of row insertions
Submitted: 10 Jul 2007 15:37 Modified: 18 Sep 2007 17:14
Reporter: Marc Mirandews Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:mysql-essential-5.1.21-beta OS:Any (Linux, WindowsXP)
Assigned to: CPU Architecture:Any

[10 Jul 2007 15:37] Marc Mirandews
Description:
I've just downloaded MySQL 5.1.20 today (I was using 5.1.18 or 19, I can't remember), and all my servlet application fails due to the same error. It seems like executeUpdate() function of ConnectorJ returns a bad value (for me the big number 7810893 wherever). I've been looking last changes and that is a point modified in that last one version of Mysql 5.1.
So I think that has broken collaterally due to one of the last changes. 

My reaction has been to upgrade Connector/J to the last version available in MySQL AB page, but it stills give me the error, so I definitely think it's a matter of MySQL last version. I've tried to insert manually the query into mysql, but it returns me the correct value "1 row affected". The query was as simple as "INSERT INTO LOGS (Message) VALUES (?)"
where ? = "The user has changed that".

I consider it critical, because I have a hundred lines of code of my application in the model of (Model-View-Controller architecture) like 

""""
statement=connection.prepareStatement("INSERT INTO LOGs (Message) VALUES ('the client has modified that register')");
if(statement.executeUpdate()!=1) throw new AppException("Logical Error. The number of rows inserted is not 1");

""""
.. so I'm seeing constantly that message as a nexception

Thank you all!!!  And sorry for my bad acaedemichal english

How to repeat:
Compile that class and pass as args the values of your user and pass MySQL account

TestBug.java
=============
import java.sql.*;

public class TestBug{
  public static void main(String [] args){
  
    try{
	  Class.forName("com.mysql.jdbc.Driver");
	  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test",args[0],args[1]); 
	  Statement s = con.createStatement();
	  s.executeUpdate("CREATE TABLE temp(value INT)");
	  System.out.println(s.executeUpdate("INSERT INTO temp SET Value=54")); //For me the output is 7810893
	  System.out.println(s.executeUpdate("UPDATE      temp SET Value=55")); //The output is 1 (CORRECT!)
	  s.executeUpdate("DROP TABLE temp");
	  s.close();
    }catch(Exception e){e.printStackTrace();}
  }
}
[10 Jul 2007 15:39] Marc Mirandews
I misunderstood the TAG field. I just wanted to append the following:
 
"Bug occur only with MySQL_5.1.20, no matter wich JDBC Connector/J version you use"
[12 Jul 2007 7:58] Sveta Smirnova
test case

Attachment: bug29692.c (text/plain), 1.33 KiB.

[12 Jul 2007 7:59] Sveta Smirnova
Thank you for the report.

Verified as described using attached test case. As bug is repeatable with C test case I reclassified report as "Server" bug
[16 Jul 2007 18:53] 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/30980

ChangeSet@1.2559, 2007-07-16 14:53:05-04:00, dkatz@damien-katzs-computer.local +3 -0
  Bug #29692  	Single row inserts can incorrectly report a huge number of row insertions
  
  This bug was caused by unitialized value that was the result of a bad 5.0 merge.
[17 Jul 2007 8:59] Sveta Smirnova
Bug #29787 was marked as duplicate of this one.

Bug #29787 shows prepared statements are affected as well.
[18 Jul 2007 9:31] Sveta Smirnova
Bug #29865 (repeatable with prepared statements) was marked as duplicate of this one.
[19 Jul 2007 15:42] Sveta Smirnova
Bug #29902 was marked as duplicate of this one.
[19 Jul 2007 15:48] Bugs System
Pushed into 5.1.21-beta
[20 Jul 2007 14:28] [ name withheld ]
i hope this fix will be appliet to all drivers (jdbc, c ..etc). By the way, is there any date for the next release ?
[20 Jul 2007 16:18] Mark Matthews
It's not a driver-level issue, so there is no fix to be applied to the drivers. The fix is in the server (it was sending bad update counts to any client who asked for FOUND rows rather than MODIFIED rows).
[23 Jul 2007 0:52] Paul DuBois
Noted in 5.1.21 changelog.

Single-row inserts could report a row count greater than one.
[20 Aug 2007 8:02] Victor Reus
Hi all,

When will be available this fix in a release that we can use?
Most of the applications might use this feature making 5.1.20 release pointless?

Regards,
Victor.
[22 Aug 2007 18:29] Marc Mirandews
Victor, em sembla que et conec...
[29 Aug 2007 15:16] Marc Mirandews
It still fails even on 5.1.21-beta ...
[18 Sep 2007 17:14] Marc Mirandews
It finally works. It must be pushed into 5.1.21-beta uring this week...