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事务类型和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)

相关文章

发表新评论