Mysql出现Could not retrieve transaction read-only status from server报错

问题现象

mysql在java连接数据库的时候,偶发性的出现如下错误:

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

解决办法

mysql版本:5.7.25

mysql jdbc驱动包:mysql-connector-java-5.1.47.jar

mysql事务类型和java连接数据库的方法不匹配,根据网上搜索到的办法和自己实际使用的办法如下

1.查看mysql的事物隔离级别 SHOW VARIABLES LIKE '%iso%';

mysql> SHOW VARIABLES LIKE '%iso%';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| transaction_isolation  | REPEATABLE-READ |
| tx_isolation           | REPEATABLE-READ |
+------------------------+-----------------+
2 row in set (0.00 sec)

2.修改mysql事务隔离级别

mysql> SET GLOBAL tx_isolation='READ-COMMITTED'; 
Query OK,0 rows affected (0.00 sec)

mysql> SET GLOBAL transaction_isolation='READ-COMMITTED'; 
Query OK,0 rows affected (0.00 sec)

3.修改my.cnf,让配置永久生效

/etc/my.cnf(位置可能不一样)的[mysqld]后面添加transaction_isolation = READ-COMMITTED

4.检测事务隔离级别是否生效,第一步执行后几分钟后才能查询到;

mysql> SHOW VARIABLES LIKE '%iso%';
+------------------------+-----------------+
| Variable_name          | Value           |
+------------------------+-----------------+
| transaction_isolation  | READ-COMMITTED |
| tx_isolation           | READ-COMMITTED |
+------------------------+-----------------+
2 row in set (0.00 sec)

后续问题

经过上面的修改,一段时间后还是出现了该问题,偶发出现,尝试方法有两个:

  • 把驱动包降到5.1.30还是有问题,尝试把驱动包升到mysql-connector-java-8.0.17.jar
  • 在jdbc串后面加参数:failOverReadOnly=false 自动重连成功后,连接是否设置为只读

通过上面两种方式修改后,再观察下。/(ㄒoㄒ)/~~

相关文章

发表新评论