MySQL Bugs: #25606: getUpdateCount() unreliable?

Bug #25606	getUpdateCount() unreliable?
Submitted:	13 Jan 2007 2:22	Modified:	14 Feb 2007 18:02
Reporter:	P Eger	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.1.14	OS:	Windows (Windows Server 2003 x64)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
Running against Mysql 5.0.24a-community-nt-log and 5.1.12-beta both, running the below test program excepts unexpectedly. This is a result of getUpdateCount() being seemingly unreliable. Note that this bug seems to be data dependant, as changing the below constant from "3646" to "10000" makes the exception go away.

output log for below program
----------------------------------------
added 1000
added 2000
added 3000
added 4000
added 5000
added 6000
added 7000
added 8000
added 9000
added 10000
added 1000
added 2000
added 3000
deleted 100 between 0 and 100
deleted 400 between 100 and 500
deleted 1600 between 500 and 2100
deleted 1546 between 2100 and 3646
Exception in thread "main" java.sql.SQLException: nrows_changed==-1
	at com.automotive.app.content.search.TestMysql.main(TestMysql.java:103)

How to repeat:
package com.automotive.app.content.search;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class TestMysql {
	
	public static void main(String[] args) throws Exception
	{
		com.mysql.jdbc.jdbc2.optional.MysqlDataSource nds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
		
		nds = new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();
		nds.setServerName(args[0]);
		nds.setDatabaseName(args[1]);
		nds.setUser(args[2]);
    	nds.setPassword(args[3]);
    	nds.setCharacterEncoding("ascii");
    	nds.setAllowMultiQueries(true);
    	
    	Connection conn = nds.getConnection(args[2],args[3]);
		
		conn.createStatement().execute("drop table if exists testtable");
		conn.createStatement().execute("create table testtable(c0 int not null) engine=myisam");
		conn.createStatement().execute("alter table testtable add unique index testtable_i(c0)");
		
		conn.createStatement().execute("create temporary table testtable_td(_batch int not null,c0 int not null)");
		conn.createStatement().execute("create unique index testtable_td_b on testtable_td(_batch)");
		
		
		PreparedStatement ps;
		int num_changes = 0;
		
		ps = conn.prepareStatement("insert into testtable(c0) values(?)");
		while(num_changes<10000)
		{
			ps.setInt(1, num_changes);
			
			ps.addBatch();
			
			num_changes++;
			
			if((num_changes%1000) == 0)
			{
				ps.executeBatch();
				
				System.out.println("added "+num_changes);
			}
		}
		
		ps.close();
		
		int num_deletes = 3646;
		
		int i=0;
		ps = conn.prepareStatement("insert into testtable_td(_batch,c0) values(?,?)");
		while(i<num_deletes)
		{
			ps.setInt(1, i);
			ps.setInt(2, i);
			
			ps.addBatch();
			
			i++;
			
			if((i%1000) == 0)
			{
				ps.executeBatch();
				
				System.out.println("added "+i);
			}
		}
		
		ps.executeBatch();
		
		ps.close();
		
		
		
		String sql = "lock tables testtable write;\n"+
			"delete testtable from testtable_td force index(testtable_td_b) straight_join testtable on testtable.c0=testtable_td.c0 where testtable_td._batch>=? and testtable_td._batch<?;\n"+
			"unlock tables;";
			    		
		ps = conn.prepareStatement(sql);
		
		int bsize=100;
		
		for(int start_index=0, end_index=Math.min(bsize,num_deletes); start_index<num_changes; start_index=end_index, end_index=Math.min(start_index+bsize,num_deletes))
		{
			ps.clearParameters();
			ps.setInt(1, start_index);
			ps.setInt(2, end_index);
			ps.execute();
		
		    //ignore the results from the "lock_tables"
		    int c1 = ps.getUpdateCount();
		    boolean b1 = ps.getMoreResults();
		    
		    //should always be the results from the "delete"
		    int nrows_changed = ps.getUpdateCount();
		    
		    if(nrows_changed==-1)
				throw new SQLException("nrows_changed==-1"); //????
		    
			System.out.println("deleted "+nrows_changed+" between "+start_index+" and "+end_index);
		    
			bsize*=4;
		}
		
		conn.close();
	}
}

Suggested fix:
???

Hi and thanks for your report.
Please provide me with:
  - JDK version
  - Results of the same test run in mysql command line client

Are you by any chance using "update ignore" syntax anywhere?

I must admit I don't quite get it...

    /**
     *  Retrieves the current result as an update count;
     *  if the result is a <code>ResultSet</code> object or there are no more results, -1
     *  is returned. This method should be called only once per result.
     */
    int getUpdateCount() throws SQLException; 

Last SQL to be issued before *you* raise an exception is:
... where _batch>=3646 and _batch<3646; 
thus satisfying condition "or there are no more results". I would have called it a bug if it returned anything *but* -1.

1) Testest the same behaviour in 2 JDK versions:

1.6.0-rc-b104
1.5.0_10-b03

2) Yes, "update ignore" is used elsewhere in the program, but not in this test case.

3) I think i see an error in my logic in that "start_index<num_changes" should be "start_index<num_deletes", but regardless of what the _batch numbers are, shouldn't "DELETE ... where _batch>=3646 and _batch<3646" produce a getUpdateCount() of 0 (zero)? IE it was a valid SQL statement that i am expecting a result from, + the fact that "DELETE ... where _batch>=0 and _batch<100" always does return >=0?

Hi.

"3) I think i see an error in my logic in that "start_index<num_changes"
should be "start_index<num_deletes", but regardless of what the _batch
numbers are, shouldn't "DELETE ... where _batch>=3646 and _batch<3646"
produce a getUpdateCount() of 0 (zero)?"

I dissagree, the second condition, "no more result", is satisfied thus it should return -1. Otherwise you would not be able to tell that you reached the end of data.

Respectfully, I must disagee. I think there is a fundamental difference between "no more results" and zero length results, that with the current behaviour, i am unable to distinguish. The *real* problem, IMO is that the same sequence of "simple" SQL statements produces very different output for getUpdateCount(), depending on what data is passed in. This is very counterintuitive/surprising and i would argue incorrect. There *was* an update that happened, it just also happened to not effect any rows. No?

Hi. You might have a point here. Reopening.

I can't repeat this with C/J 5.0 head. I run your test program, and once you reach the "block" of deletes that terminates at 3646, and go one "block" past that, the program loops endlessly deleting (and reporting as such) 0 rows deleted.

Would you mind testing with a nightly snapshot of 5.0 from http://downloads.mysql.com/snapshots.php#connector-j

There's been at least one bug fix related to multistatement results, so I'd like to know if that fixes your issue too.

Thanks.

  -Mark

Results: 

mysql-connector-java-3.1.14-bin.jar: broken
mysql-connector-java-5.0.4-bin.jar: broken
mysql-connector-java-5.0-nightly-20070214-bin.jar: working

P.S. This test code was distilled (improperly, hence the infinite looping) from the *real* code, i will give it a shot with the real code as well & report back. Any idea when next 5.0.X stable will be out?