mysqlmariadbamazon-aurora

Aurora 2.12.3 timing out on JDBC batch executions


Upgrading from Mysql 5.7 to Aurora 2.12.3 before jumping to Aurora MySQL 8 equivalent. Every previous process works except for any queries that utilize PreparedStatement .addBatch() and executeBatch(). The process consistently timeouts and there are no locks when looking at the processlist.

Example: ** code modified to be concise. It is correctly in a try catch and the connection is being closed correctly after the process finishes. **

            
            con.setAutoCommit(false);
            stmt = con.prepareStatement("insert into messagequeue (bmid, mid, attempt) values (?, ?, ?)");
            for (MessageQueue mq : messages) {
                stmt.setInt(1, bmid);
                stmt.setInt(2, mq.getID());
                stmt.setInt(3, 0);
                stmt.addBatch();
                if (++sent >= batchSize) {
                    stmt.executeBatch();
                    con.commit();
                    sent = 0;
                }
            }
            stmt.executeBatch();
            con.commit();
            
            sent = 0;
            con.setAutoCommit(true);

This all previously worked correctly under MySQL 5.7 and other instances of .addBatch are having similar timeout issues. Nothing in logs to help point to the issue. Have not found anyone else running into this issue with aurora and these methods either.

If it helps my db param is: jdbc:mariadb://url/mydb?rewriteBatchedStatements=true

Any ideas?


Solution

  • Looks like mariadb stopped supporting aurora starting with version 3.0.0 due to errors piling up and no fixes coming in. For the issue of batch processes hanging they added an option "useBatchMultiSend" which defaults to false if using aurora. However, starting at 3.0.0 they removed that option all together. Using mariadb 2.7.12 resolved my issues and if the issue persists try adding useBatchMultiSend=false.