MySQL "No operations allowed after statement closed"

Question

I'm running into a strange situation with a prepared statement hitting a MySQL database using MySQL Connector/J. In certain environments, I periodically have issues with longer existing (> 5 minutes) prepared statements. I frequently get an exception when calling executeBatch that reads:

"No operations allowed after statement closed"

However, there is no code that could be closing the statement that I can see. The code looks something like the following:

private void execute(MyClass myObj, List<MyThing> things) throws SQLException {
    Connection con = null;
    PreparedStatement pstmt = null;

    try {
        con = ConnectionHelper.getConnection();
        pstmt = con.prepareStatement(INSERT_SQL);
        int c = 0;

        for (MyThing thing : things) {
            pstmt.setInt(1, myObj.getA());
            pstmt.setLong(2, thing.getB());
            pstmt.addBatch();

            if (++c % 500 == 0) {
                pstmt.executeBatch();
            }
        }

        pstmt.executeBatch();
    }
    finally {
        ConnectionHelper.close(pstmt, con);
    }
}

ConnectionHelper.close essentially just calls close on the statement and the connection. ConnectionHelper.getConnection is a bit of a rabbit hole -- it roughly retrieves a connection from a pool using java.sql.DriverManager and proxool, then wraps it with Spring DataSourceUtils.

Usually it will fail on the last pstmt.executeBatch(), but will sometimes fail in other places. I've checked and wait_timeout and interactive_timeout are configured to defaults (definitely > 5 minutes). Moreover, in most cases, the connection and statement are used in the loop, but the a few seconds later the statement fails outside of the loop. The DB server and the app server are running on the same subnet, so network issues seem unlikely.

Looking for any tips on how to debug this issue -- at the moment, I'm trying to dig in to the MySQL Connector/J code to see if I can somehow get some additional debugging statements out. Unfortunately I can't attach a debugger, as it can only be reproduced in a select couple environments at the moment.

Answer 1

Take a look at the line:

            if (++c % 500 == 0) {
                pstmt.executeBatch();
            }

What happens if that gets executed, but the loop terminates. Then you call pstmt.executeBatch again with nothing in the batch.