MySQL Bugs: #114974: the SQL in batch will not clear after statement close

Bug #114974	the SQL in batch will not clear after statement close
Submitted:	13 May 2024 7:11	Modified:	4 Nov 19:41
Reporter:	rico rico (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	8.4.0	OS:	MacOS
Assigned to:		CPU Architecture:	ARM (M1)
Tags:	Contribution

Description:

When I connect MySQL with Properties "useServerPrepStmts=true&cachePrepStmts=true".

the connection will reuse the previous batch SQL even if I have closed the statement.
this behavior is confusing when reusing the connection in another thread. 

How to repeat:
you can use this code to reproduce:

class BatchBug extends BaseBugReport {
        Connection con;

        @Override
        public String getUrl() {
            return URL+"?useServerPrepStmts=true&cachePrepStmts=true";
        }

        @Override
        public void runTest() throws Exception {
            {
                final PreparedStatement preparedStatement = con.prepareStatement("insert into test values(?)");
                try {
                    preparedStatement.setInt(1, 2);
                    preparedStatement.addBatch();
                    boolean someCase = true;
                    if (someCase) {
                        throw new IllegalStateException("illegal");
                    }
                    //we will not run the following line 
                    preparedStatement.executeBatch();

                } catch (Exception e) {

                } finally {
                    //this will clear params， but it doesn't clear batch.
                    preparedStatement.close();
                }

            }
            {
                final PreparedStatement preparedStatement = con.prepareStatement("insert into test values(?)");
                preparedStatement.setInt(1, 3);
                preparedStatement.addBatch();
                preparedStatement.executeBatch();
                preparedStatement.close();
            }

            {
                final ResultSet resultSet = con.createStatement().executeQuery("select count(*) from test");
                if (resultSet.next()) {
                    assertTrue("not only one rows",resultSet.getInt(1) == 1);
                }
            }
            con.close();

        }
    }

expected result:
the table test has only one row with id=3.

actual result :
the table test has two rows with id=2 and id=3.

Suggested fix:
add "clearBatch" call inside "ServerPreparedStatement::close()" method

I will fix it if it is a bug.

Hello rico rico,

Thank you for the report and feedback.

regards,
Umesh

Excuse me， my Oracle Contribution Agreement (OCA) is still under review. Can someone help me?

Excuse me， my Oracle Contribution Agreement (OCA) is still under review. Can someone help me?

This patch fixes this bug on version 8.4.0 by adding the addBatch method (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 114974.patch (application/octet-stream, text), 633 bytes.

sorry.
typo on the description of contributions: "addBatch"->"clearBatch"

Posted by developer:
 
 Added the following note to the MySQL Connector/J 9.5.0 release notes:		
 
If useServerPrepStmts and cachePrepStmts were set to true, and a batched 
statement was added, but not executed, the driver would cache the batch and save it even
if the statement was closed, resulting in the program running the statement. 
As of this release. Connector/J clears the batch if the statement is closed.      
      
Our thanks to Chengyi Dong for the contribution.