记录一次 Spring JDBC MySQL 批量插入异常

Scroll Down

主要异常摘要

Caused by: java.sql.BatchUpdateException: Unexpected exception encountered during query

Caused by: java.sql.SQLException: Unexpected exception encountered during query.

Caused by: java.lang.NullPointerException  

主要异常详情

#### 2019-09-25 11:02:32.586 INFO  org.springframework.beans.factory.xml.XmlBeanDefinitionReader - Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
#### 2019-09-25 11:02:32.613 INFO  org.springframework.jdbc.support.SQLErrorCodesFactory - SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [insert into xxx(a,b,c) values(?,?,?)]; Unexpected exception encountered during query.; nested exception is java.sql.BatchUpdateException: Unexpected exception encountered during query.  
 at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:106)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
 at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:605)
 at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
 at org.springframework.jdbc.core.JdbcTemplate.batchUpdate ...

 ......

 Exception stack trace:
Caused by: java.sql.SQLException: Unexpected exception encountered during query.  
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2582)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1861)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2073)
    at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2009)
    at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5098)
    at com.mysql.jdbc.PreparedStatement.executeBatchedInserts(PreparedStatement.java:1543)
    ... 35 more
Caused by: java.lang.NullPointerException  
    at java.util.concurrent.ConcurrentHashMap.get(ConcurrentHashMap.java:936)
    at com.mysql.jdbc.StringUtils.findCharset(StringUtils.java:124)
    at com.mysql.jdbc.StringUtils.toString(StringUtils.java:2199)
    at com.mysql.jdbc.PreparedStatement$ParseInfo.getSqlForBatch(PreparedStatement.java:484)
    at com.mysql.jdbc.PreparedStatement.getPreparedSql(PreparedStatement.java:5049)
    at example.Interceptor.preProcess(Interceptor.java:26)
    at com.mysql.jdbc.MysqlIO.invokeStatementInterceptorsPre(MysqlIO.java:2859)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2580)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2549)
    ... 40 more

如何复现

  • 1.在 JDBC 连接中使用rewriteBatchedStatements=true参数
  • 2.使用 JDBC URL 注册语句拦截器,且语句拦截器调用PreparedStatement.getPreparedSql()
  • 3.使用connection.prepareStatement(),statement.addBatch()statement.executeBatch()执行批处理更新。

网友创建了一个可重现的测试项目(https://github.com/dmitry-at-genestack/mysql-jdbc-bug-report)

解决方案

  • 1.在方法getSqlForBatch(ParseInfo batchInfo)中添加对 null charEncoding 的检查
  • 2.JDBC 连接使用参数useServerPrepStmts=true可以解决此问题,但会对插入/更新性能产生负面影响,实际测试在项目中会报错(SpringMVC项目)。
  • 3.【推荐】使用PreparedStatement.asSql()代替PreparedStatement.getPreparedSql()编写自定义语句拦截器。

    典型的问题应用是使用了OpenZipkin/Brave库,该库的语句拦截器会引发该异常。

参考

MySQL Bugs: #84163: NPE inside statement interceptor with rewriteBatchedStatements=true