今天在处理Spring JDBC 批处理过程中,出现了以下出错
三月 08, 2018 10:14:49 下午 org.springframework.jdbc.support.SQLErrorCodesFactory <init>
信息: SQLErrorCodes loaded: [DB2, Derby, H2, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase, Hana]
Exception in thread "main" org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL [update Student set age = ? where id = ?]; Could not retrieve transaction read-only status from server; nested exception is java.sql.SQLException: Could not retrieve transaction read-only status from server
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:657)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:670)
at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:986)
at com.ray.StudentJDBCTemplate.batchUpdate(StudentJDBCTemplate.java:40)
at com.ray.MainApp.main(MainApp.java:43)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:147)
Caused by: java.sql.SQLException: Could not retrieve transaction read-only status from server
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:861)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:878)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:874)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3552)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3521)
at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1230)
at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:1002)
at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:986)
at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:641)
... 9 more
Caused by: java.sql.SQLException: Unknown system variable 'tx_read_only'
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1381)
at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3546)
... 15 more
Process finished with exit code 1
问题:
Caused by: java.sql.SQLException: Could not retrieve transaction read-only status from server
解决方案:
可能是你的 mysql 的驱动包的问题。
我今天使用了最新的mysql的驱动包版本号是:mysql-connector-java-5.1.45.jar
结果报错,无法获得dbcp的连接
我果断换成以前的老的jar包:
mysql-connector-java-5.1.8-bin.jar
之后,连接池运行正常。
5.1.8-5.1.21 测试没问题