MySQL Bugs: #39426: executeBatch passes most recent PreparedStatement params to StatementInterceptor

Bug #39426	executeBatch passes most recent PreparedStatement params to StatementInterceptor
Submitted:	12 Sep 2008 19:10	Modified:	4 Aug 2009 15:39
Reporter:	Eric Jensen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.6	OS:	Any
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	executeBatch, PreparedStatement, StatementInterceptor

Description:
When using a PreparedStatement to do 

for i in 1 to n {setBlah; addBatch}; executeBatch

any StatementInterceptors defined will only be passed the most recent addBatch parameters, but they will be passed them n times, effectively intercepting  duplicates of the last statement values and never receiving the previous ones.

The call stack leading up to a "java.sql.BatchUpdateException: Duplicate entry" error due to this in my application is:
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1666)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1082)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)
        at org.apache.commons.dbcp.DelegatingStatement.executeBatch(DelegatingStatement.java:297)

How to repeat:
use executeBatch with prepared statements and a statement interceptor.

Suggested fix:
pass the right parameters, not just the last ones.

I've verified that not only is the String sql passed to my interceptor just duplicated for the last set of params, but even the ((com.mysql.jdbc.PreparedStatement)interceptedStatement).getParameterBindings() are the same across interceptions of an executeBatch

Hi Eric and thanks for your report.

Please attach small but self-sufficient test case proving your point.

the psuedocode testcase below is the best i can do within the next few weeks...it's pretty straightforward to reproduce from that.  i may get to a real code test case someday, but not anytime soon.  sorry...

guess i should have said "above" not "below"....i.e., the original test case i provided in the description.

Eric, without test case this is not a valid bug report... I will wait for you as I too am off to DevCon for next ~10 days or so.

I have to be fair to others too, especially ones that provided test cases and all the necessary info. If I'm to write everything by myself none's to get any answers...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

test case

Attachment: StatementInterceptorTest.java (text/plain), 3.52 KiB.

the test is attached, it fails as follows:

.Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.0.27-log
E
Time: 0.519
There was 1 error:
1) testStatementInterceptorPreparedStatementExecuteBatch(testsuite.simple.StatementInterceptorTest)java.sql.BatchUpdateException: Duplicate entry '2' for key 1
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1669)
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1085)
        at testsuite.simple.StatementInterceptorTest.testStatementInterceptorPreparedStatementExecuteBatch(StatementInterceptorTest.java:73)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at testsuite.simple.StatementInterceptorTest.main(StatementInterceptorTest.java:23)

FAILURES!!!
Tests run: 1,  Failures: 0,  Errors: 1

Hi Eric. I am unable to reproduce this behavior using following test case and latest c/J sources:

    	try {
    		
			createTable("bug39426", "(strdata1 VARCHAR(100) PRIMARY KEY, strdata2 VARCHAR(100))");
			
			this.pstmt = this.conn.prepareStatement("INSERT INTO "
					+ "bug39426 (strdata1, strdata2) VALUES (?,?)");

			for (int i = 0; i < 1000; i++) {
				this.pstmt.setString(1, "batch_" + i);
				this.pstmt.setString(2, "batch_" + i);
				this.pstmt.addBatch();
			}

			int[] updateCounts = this.pstmt.executeBatch();

			for (int i = 0; i < updateCounts.length; i++) {
				assertTrue("Update count must be '1', was '" + updateCounts[i]
						+ "'", (updateCounts[i] == 1));
			}
			System.out.println("Done");
    	} finally {
			closeMemberJDBCResources();
		}

Output:
Loading JDBC driver 'com.mysql.jdbc.Driver'
Done.

Done.

Connected to 5.1.31-log
java.vm.version         : 1.5.0_17-b04
java.vm.vendor          : Sun Microsystems Inc.
java.runtime.version    : 1.5.0_17-b04
os.name                 : Windows Server 2008
os.version              : null
sun.management.compiler : HotSpot Client Compiler
Done

General query log on server shows all correct updates.

I don't see a StatementInterceptor in your test?  Does the testcase I provided pass for you?  It fails for me.

Eric,
> I don't see a StatementInterceptor in your test? Does the testcase I provided pass for you?  It fails for me.

I am not allowed to do code reviews (you need support contract for that) and I'm obliged to boil-down each test case to it's simplest possible form, to the best of my knowledge. This version I posted is actually from our regression test suite and I think it covers the essentials completely.
Now, did you tried my test case? Do you have any objections to it?

Finally, do not be alarmed with "Can't repeat", I am monitoring this report regardless of ruling made.

your test case does not have a StatementInterceptor in it.  this bug is specific to StatementInterceptors.  i believe the test case i provided represents the simplest possible form.  thanks.

Eric, true, and I have already asked for consult.

verified as described

fix + test

Attachment: bug39426.diff (text/x-diff), 9.59 KiB.

Pushed for release in 5.1.8

An entry was added to the 5.1.8 changelog:

A statement interceptor received the incorrect parameters when used with a batched statement.

Server version: 5.1.37-1ubuntu5 (Ubuntu)

also has this issue

Han, I see no connection between server version and this bug...

hi,Tonci 
is it driver's issue ?