Bug #109418 batch insert threw an unexpected exception
Submitted: 17 Dec 2022 4:00 Modified: 24 Nov 2023 8:39
Reporter: william li Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.31 OS:Any
Assigned to: CPU Architecture:Any
Tags: batch insert;Operation not allowed after ResultSet closed

[17 Dec 2022 4:00] william li
Description:
Use the same connection running batch insert with rewriteBatchedStatements=true&useServerPrepStmts=true&cachePrepStmts=true and retrieveGeneratedKeys.Two batches insert transactions with the same code have different parameters,let the first transaction  succeed and the second transaction fail,but  
The second transaction always threw an unexpected exception.
Run the demo to reproduce.

How to repeat:
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
/**
 * <p>demo<br>
 * @author :  sheng.li
 * @date : 2022/12/16 21:05
 **/
public class TestBatchInsert {

    private static final String SQL = "insert into user(name,code) values(?,?)";

    private static final String[] KEY_COLUMN_NAMES = new String[]{"id"};

    public static void main(String[] args) throws Exception {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/demo");
        config.setUsername("root");
        config.setPassword("123456");
        config.setMinimumIdle(1);
        config.setMaximumPoolSize(1);
        config.setConnectionTestQuery("SELECT 1");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");
        config.addDataSourceProperty("nullCatalogMeansCurrent", "true");
        config.addDataSourceProperty("rewriteBatchedStatements", "true");
        config.addDataSourceProperty("useUnicode", "true");
        config.addDataSourceProperty("characterEncoding", "UTF8");
        config.addDataSourceProperty("mysqlEncoding", "utf8");
        config.addDataSourceProperty("zeroDateTimeBehavior", "convertToNull");
        config.addDataSourceProperty("allowMultiQueries", "true");
        config.addDataSourceProperty("useServerPrepStmts", "true");
        config.addDataSourceProperty("cachePrepStmts", "true");
        config.addDataSourceProperty("prepStmtCacheSize", "250");
        config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
        config.addDataSourceProperty("autoReconnect", "true");
        config.addDataSourceProperty("failOverReadOnly", "false");
        config.addDataSourceProperty("serverTimezone", "Asia/Shanghai");
        config.addDataSourceProperty("useSSL", "false");
        HikariDataSource ds = new HikariDataSource(config);
        try{
            try(Connection connection = ds.getConnection()){
                // create table
                {
                    Statement statement = connection.createStatement();
                    statement.execute("DROP TABLE IF EXISTS user");
                    statement.execute("create table user(id int(11) primary key auto_increment, name varchar(5),code varchar(5),CONSTRAINT unique_code UNIQUE KEY (code))");
                    statement.close();
                }
                //success
                executeBatch(connection,"user1","1","user2","2");
                //fail:An Duplicate entry '2' for key 'unique_code' exception should be thrown,but 'Operation not allowed after ResultSet closed' exception are thrown.
                executeBatch(connection,"user3","2","user4","4");
            }
        } catch (Exception e){
            throw e;
        } finally {
            ds.close();
        }
    }

    private static void executeBatch(Connection connection, String name1, String code1, String name2, String code2) throws SQLException {
        connection.setAutoCommit(false);
        PreparedStatement statement = connection.prepareStatement(SQL,KEY_COLUMN_NAMES);
        try{
            {
                statement.setString(1, name1);
                statement.setString(2, code1);
                statement.addBatch();
            }
            {
                statement.setString(1, name2);
                statement.setString(2, code2);
                statement.addBatch();
            }
            statement.executeBatch();
            statement.close();
            connection.commit();
        }catch (Exception e){
            connection.rollback();
            throw e;
        }finally {
            if(!statement.isClosed()){
                statement.close();
            }
        }
    }
}

Suggested fix:
Attribute results of com.mysql.cj.jdbc.ClientPreparedStatement is not null and attribute connection  of results is null,it's the key reason.Just solve it.
[17 Dec 2022 4:14] william li
If sql is "insert into user(name,code) values(?,?);",just add a string ';' at the end of sql, it is normal,because it is recognized as canServerPrepare=false,cache not used.
[24 Nov 2023 8:39] MySQL Verification Team
Hello william li,

Thank you for the report and test case.

regards,
Umesh