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
• Status: Duplicate
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.6, 5.0.7
• OS: Any
• CPU Architecture: Any
• Tags: insert, rowcount, wrong

[23 Jul 2007 15:56] Gökhan Demir

Description:
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;
    try
    {
      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);
    }
    finally
    {
      if (psCreate != null) psCreate.close();
      if (psIns != null) psIns.close();
      if (psDrop != null) psDrop.close();
    }

Suggested fix:
None.

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