Bug #29979 PreparedStatement.executeUpdate returns wrong rowcount for an insert statement.
Submitted: 23 Jul 2007 15:56 Modified: 23 Jul 2007 16:43
Reporter: Gökhan Demir Email Updates:
Status: Duplicate Impact on me:
Category:MySQL Server Severity:S1 (Critical)
Version:5.0.6, 5.0.7 OS:Any
Assigned to: CPU Architecture:Any
Tags: insert, rowcount, wrong

[23 Jul 2007 15:56] Gökhan Demir
Connector/J reports wrong number of affected rows on PreparedStatement.executeUpdate() for an insert statement against mysql-server 5.1. It is working correctly against 5.0.41 server however. 

I am not sure if this is a server of connector/J error. When testing the same sql from the command line tool, 5.1 reports correct rowcount after an insert statement. Therefore, I assume it is a Connector/J issue.

How to repeat:
Here is a code snippet to repeat (attached test case includes full source code):
    PreparedStatement psCreate = null, psIns = null, psDrop = null;
      psCreate = conn.prepareStatement( "create table test.t1 (a int, vv varchar(10), primary key (a)) engine=myisam" );
      psIns = conn.prepareStatement( "insert into test.t1 (a,vv) values (1,'test')" );
      psDrop = conn.prepareStatement( "drop table test.t1" );
      int c = psCreate.executeUpdate();
      int i = psIns.executeUpdate();
      int i2 = conn.createStatement().executeUpdate("insert into test.t1 (a,vv) values (2,'tttt')");
      int d = psDrop.executeUpdate();
      System.out.println("create ps returned = " + c);
      System.out.println("insert ps returned = " + i);
      System.out.println("insert statement returned = " + i2);
      System.out.println("drop ps returned = " + d);
      if (psCreate != null) psCreate.close();
      if (psIns != null) psIns.close();
      if (psDrop != null) psDrop.close();

Suggested fix:
[23 Jul 2007 15:58] Gökhan Demir
Results of the "how to repeat" section:

create ps returned = 0
insert ps returned = 7810893
insert statement returned = 7810893
drop ps returned = 0
[23 Jul 2007 16:02] Mark Matthews
This is a duplicate of Bug#29692, which is a server bug.

The reason it is not reproducible with the command-line client is because it asks for affected rows, while Connector/J asks for "found" rows in update counts, as that's what the JDBC specification requires.
[23 Jul 2007 16:39] Gökhan Demir
Test case to produce the wrong rowcount.

Attachment: WrongAffectedRows.java (text/x-java), 1.96 KiB.

[23 Jul 2007 16:43] Gökhan Demir
Sorry that I missed the previous 'duplicate' bug report.