MySQL Bugs: #29692: Single row inserts can incorrectly report a huge number of row insertions

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

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();}
  }
}

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"

test case

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

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

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.

Bug #29787 was marked as duplicate of this one.

Bug #29787 shows prepared statements are affected as well.

Bug #29865 (repeatable with prepared statements) was marked as duplicate of this one.

Bug #29902 was marked as duplicate of this one.

Pushed into 5.1.21-beta

i hope this fix will be appliet to all drivers (jdbc, c ..etc). By the way, is there any date for the next release ?

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).

Noted in 5.1.21 changelog.

Single-row inserts could report a row count greater than one.

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.

Victor, em sembla que et conec...

It still fails even on 5.1.21-beta ...

It finally works. It must be pushed into 5.1.21-beta uring this week...