java - SQL异常 : Could not retrieve transation read-only status server

Question

我来找你是因为 MySQL 有问题:

有些日子以来，没有解释我接受这个异常(exception):

java.io.IOException: java.sql.SQLException: Could not retrieve transation read-only status server
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
Caused by: java.sql.SQLException: REPLACE INTO `db`.`test_table` (`timestamp`,`database`,`table`,`columns`,`lines`,`before`,`write`,`wait`) VALUES (?,?,?,?,?,?,?,?)
        ... 4 common frames omitted
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:941) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3955) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3926) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1430) ~[mysql-connector-java-5.1.28.jar:na]
        at com.zaxxer.hikari.proxy.StatementProxy.executeBatch(StatementProxy.java:116) ~[HikariCP-java6-2.3.2.jar:na]
        at com.zaxxer.hikari.proxy.PreparedStatementJavassistProxy.executeBatch(PreparedStatementJavassistProxy.java) ~[HikariCP-java6-2.3.2.jar:na]
        ... 3 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_51]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_51]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_51]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:422) ~[na:1.8.0_51]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.Util.getInstance(Util.java:386) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2395) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2316) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2807) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2768) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1651) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:3949) ~[mysql-connector-java-5.1.28.jar:na]
        ... 8 common frames omitted
Caused by: java.sql.SQLException: No database selected
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1086) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2819) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.setCatalog(ConnectionImpl.java:5443) ~[mysql-connector-java-5.1.28.jar:na]
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2368) ~[mysql-connector-java-5.1.28.jar:na]
        ... 13 common frames omitted

我不明白这个错误？服务器版本是5.6.19，mysql-connector 5.1.28

如果有人有任何想法，我会采纳。谢谢

编辑:

我使用的 JDBC URL:jdbc:mysql://192.168.1.1/?autoReconnect=true&useUnicode=true&characterEncoding=utf-8

Answer 1

最后，我找到了问题的根本原因。这不是 URL 中的数据库，只是结果。

问题来 self 使用的 JDBC 参数:

• cachePrepStmts=true
• prepStmtCacheSize=250
• prepStmtCacheSqlLimit=2048
• useServerPrepStmts=true

最后一个，MySQL 5.6 真的不推荐，因为有bug。所以我删除了所有语句缓存参数，现在我的代码运行正常。

感谢您的帮助。