Null pointer when trying to do rewriteBatchedStatements for MySQL and Java

1k views Asked by At

I am trying to do batch inserts into mysql at very high rates. I wanted to try the rewriteBatchedStatements config option as I have read it can make significantly affect performance. When I add the option however I get the following exception:

java.lang.NullPointerException
at com.mysql.jdbc.PreparedStatement.computeMaxParameterSetSizeAndBatchSize(PreparedStatement.java:1694)
at com.mysql.jdbc.PreparedStatement.computeBatchSize(PreparedStatement.java:1651)
at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1515)
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1272)
at com.zaxxer.hikari.proxy.StatementProxy.executeBatch(StatementProxy.java:116)
at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeBatch(PreparedStatementJavassistProxy.java)

This is my code that does the inserts:

try (Connection connection = DBUtil.getInstance().getConnection();
         PreparedStatement preparedStatement = connection.prepareStatement(query)) {
        connection.setAutoCommit(false);
        for (TransactionBatch batch : batches) {
            try {                   
                preparedStatement.setString(1, batch.getDeviceID());
                preparedStatement.setBinaryStream(2, new ByteArrayInputStream(dataArray));
                preparedStatement.addBatch();
            } catch (Exception e) {
               e.printStackTrace();
            }
        }

        preparedStatement.executeBatch();
    } catch (Exception e) {
        e.printStackTrace();
    }

This is my jdbc url:

jdbc:mysql://url:port/tableName?user=userame&password=password&useServerPrepStmts=false&rewriteBatchedStatements=true

Also I am using HikariCP as my connection pool.

EDIT: Update - looks like the problem relates to having a varbinary(10000) column in the table

1

There are 1 answers

0
Nath5 On

The solution was to stop using:

preparedStatement.setBinaryStream(inputstream)

instead I used

preparedStatement.setBytes(byte[])

In order to rewrite it must need to calculate the total size which it can't do upfront from an input stream. It is working great now and my write speeds are awesome!