今天同事發現程式日誌有異常拋出，詢問原因，進過排查發現與java的連接參數有關係，具體處理過程如下： 一、錯誤信息 二、錯誤原因 資料庫版本是5.7.18-log，而程式mysql-connector 庫使用的版本是5.1.43,這裡用最新的mysql-connector-java-5.1.46-b ...
今天同事發現程式日誌有異常拋出，詢問原因，進過排查發現與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 database inside the URL, just a consequence. The problem come from JDBC parameters I use : cachePrepStmts=true prepStmtCacheSize=250 prepStmtCacheSqlLimit=2048 useServerPrepStmts=true 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.
