SQLException:无法检索事务只读状态服务器 [英] SQLException: Could not retrieve transation read-only status server

问题描述

由于MySQL的问题，我来找你:

I come to you because of a problem with MySQL:

几天以来，我一直不加解释地接受这个例外:

Since some days, without explanation I take this 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

I don't understand this error ? The server version is 5.6.19 with mysql-connector 5.1.28

如果有人有我的想法. 谢谢

If someone have any idea I take. Thanks

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

The JDBC URL I use : jdbc:mysql://192.168.1.1/?autoReconnect=true&useUnicode=true&characterEncoding=utf-8

推荐答案

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

Finaly, I found the root cause of my problem. This is not the database inside the URL, just a consequence.

问题来自我使用的JDBC参数:

The problem come from JDBC parameters I use :

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

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

由于错误，实际上不建议使用MySQL 5.6.因此，我删除了所有语句缓存参数，现在我的代码正确运行了.

The last one, with MySQL 5.6 is really not recommended because of bugs. So I have remove all statement cache parameters and now my code run correctly.

感谢您的帮助.

这篇关于SQLException:无法检索事务只读状态服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！