java sqlexception例子_MySQL案例04：Cause: java.sql.SQLException: Could not retrieve transaction read-only...

今天同事发现程序日志有异常抛出，询问原因，进过排查发现与java的连接参数有关系，具体处理过程如下：

一、错误信息

"message": "\n### Error updating database. Cause: java.sql.SQLException: Could not retrieve transaction read-only status from server\n### The error may involve com.longfor.io.sync.mapper.IoSyncMapper.insert-Inline\n### The error occurred while setting parameters\n###

SQL: INSERT INTO io_xxx ( io_id, sync_batch, sync_status, data_start_date, data_end_date ) VALUES ( ?, ?, ?, ?, ? )\n###

Cause: java.sql.SQLException: Could not retrieve transaction read-only status from server\n; SQL [];

Could not retrieve transaction read-only status from server; nested exception is java.sql.SQLException: Could not retrieve transaction read-only status from server",

二、错误原因

数据库版本是5.7.18-log，而程序mysql-connector 库使用的版本是5.1.43,这里用最新的mysql-connector-java-5.1.46-bin.jar替换掉mysql-connector-java-5.1.43-bin.jar

三、解决方案

1.下载最新的mysql-connector-java库，替换重启

https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.11-1.el6.noarch.rpm

https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.46.tar.gz

用最新的mysql-connector-java-5.1.46-bin.jar替换掉mysql-connector-java-5.1.43-bin.jar

goole用户解决方案：

老库是 mysql-connector-java-5.1.24-bin.jar 这个用在 mysql 5.6.19 版本没有问题，但是在 mysql 5.6.20 版本行就报告上面的异常。使用最新的 mysql-connector-java-5.1.32-bin.jar 后问题现象消除。

2.google发现有说在jdbc配置中直接修改掉useServerPrepStmts=false参数

https://stackoverflow.com/questions/32717417/sqlexception-could-not-retrieve-transation-read-only-status-server

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

The problem comefrom JDBC parameters I use:

cachePrepStmts=true

prepStmtCacheSize=250prepStmtCacheSqlLimit=2048useServerPrepStmts=true

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