MySQL Bugs: #49797: deleteRow() does not work

Bug #49797	deleteRow() does not work
Submitted:	18 Dec 2009 8:55	Modified:	23 Dec 2009 7:24
Reporter:	emma law	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.10	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	deleteRow

Description:
   I found that deleteRow fail to delete the record with non-string primary key. 

Scenerio:

Create the table and jsp file below.  Insert a few records in the testtable.  Then run the jsp/java code.  After that, execute "select * from testtable" in mysql and the records are still there.  This is incorrect.

The code works fine with connector/j 5.1.7.

The code also works if the primary key field (id) is changed to varchar().

This is likely a regression introduced by the fix to 27431.

How to repeat:

  jsp/java : 

    Statement Stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,  ResultSet.CONCUR_UPDATABLE );
    ResultSet rs =null;
    //get queue
    String sql ="select * from testtable ";
    rs = Stmt.executeQuery(sql);
    while(rs.next()){
         System.out.println(rs.getString("Id") + "\n");
         rs.deleteRow();
    }
    Stmt.close();
    rs.close();

   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   table : 
   CREATE TABLE `testtable` (
    `Id` int(2) not null auto_increment,
    `abc` char(50) ,
    PRIMARY KEY (`Id`)
   ) ENGINE=MyISAM DEFAULT CHARSET=utf8 
   

Suggested fix:
deleteRow should work.

Hi Emma and thanks for your report.

Could it be due to fact that your table is empty or you haven't posted all of DDL/DML statements?

I have added data into the table 

        INSERT INTO `testtable` VALUES (1,'1'),(2,'2'),(3,'3');

After executing the code, I checked the table and found that all data is still here. 

mysql> select * from testtable ;
+----+------+
| Id | abc  |
+----+------+
|  1 | 1    |
|  2 | 2    |
|  3 | 3    |
+----+------+

There are related fixes for 5.1.11 in the patch for  BUG#49745, with those fixes I'm not able to reproduce this bug.

Feel free to re-open this bug if the latest nightly snapshot for 5.1 (from http://downloads.mysql.com/snapshots.php) doesn't solve this problem for you.

I've went ahead and added a testcase to the regression suite to make sure we don't go backwards for your use case, in any case.

Thanks Mark.

Emma, can you please remove private flag from your last comment?

Now, there is always a fresh snapshot of c/J available on snapshots page. Being that c/J has fairly complete test suite, any patch that passes it is likely to make it to final release too. So, please use snapshot for now as we haven't set new version release date yet.