java.sql.SQLException: Could not retrieve transation read-only status serve

最新推荐文章于 2024-08-06 00:27:20 发布
最新推荐文章于 2024-08-06 00:27:20 发布
阅读量4.6k 收藏 4
点赞数
分类专栏： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/zhuwei_clark/article/details/107509844
版权

异常信息如下

### Cause: java.sql.SQLException: Could not retrieve transation read-only status server
; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server
	at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:110)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy105.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:64)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:58)
	at com.sun.proxy.$Proxy124.updatePushed(Unknown Source)
	at com.yzc.cms.data.back.sogou.task.SoGouTask.lambda$null$0(SoGouTask.java:63)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at com.yzc.cms.data.back.sogou.task.SoGouTask.lambda$sogouTask$2(SoGouTask.java:55)
	at java.util.ArrayList.forEach(ArrayList.java:1257)
	at com.yzc.cms.data.back.sogou.task.SoGouTask.sogouTask(SoGouTask.java:52)
	at sun.reflect.GeneratedMethodAccessor287.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84)
	at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
	at org.springframework.scheduling.concurrent.ReschedulingRunnable.run(ReschedulingRunnable.java:93)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:180)
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:293)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:545)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:505)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:479)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:489)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:2610)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:2576)
	at com.mysql.cj.jdbc.PreparedStatement.checkReadOnlySafeStatement(PreparedStatement.java:1082)
	at com.mysql.cj.jdbc.PreparedStatement.execute(PreparedStatement.java:1102)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3461)
	at com.alibaba.druid.wall.WallFilter.preparedStatement_execute(WallFilter.java:627)
	at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_execute(FilterChainImpl.java:3459)
	at com.alibaba.druid.proxy.jdbc.PreparedStatementProxyImpl.execute(PreparedStatementProxyImpl.java:167)
	at com.alibaba.druid.pool.DruidPooledPreparedStatement.execute(DruidPooledPreparedStatement.java:497)
	at io.shardingsphere.core.jdbc.core.statement.MasterSlavePreparedStatement.execute(MasterSlavePreparedStatement.java:111)
	at sun.reflect.GeneratedMethodAccessor109.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
	at com.sun.proxy.$Proxy159.execute(Unknown Source)
--
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76)
	at sun.reflect.GeneratedMethodAccessor297.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63)
	at com.sun.proxy.$Proxy157.update(Unknown Source)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:198)
	at sun.reflect.GeneratedMethodAccessor298.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:433)
	... 23 more
Caused by: com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure

The last packet successfully received from the server was 296,860 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
	at com.mysql.cj.jdbc.exceptions.SQLError.createCommunicationsException(SQLError.java:590)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:57)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1936)
	at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1422)
	at com.mysql.cj.jdbc.ConnectionImpl.isReadOnly(ConnectionImpl.java:2600)
	... 52 more
Caused by: com.mysql.cj.core.exceptions.CJCommunicationsException: Communications link failure

The last packet successfully received from the server was 296,860 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.
	at sun.reflect.GeneratedConstructorAccessor105.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:54)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:93)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createException(ExceptionFactory.java:133)
	at com.mysql.cj.core.exceptions.ExceptionFactory.createCommunicationsException(ExceptionFactory.java:149)
	at com.mysql.cj.mysqla.io.MysqlaProtocol.readPacket(MysqlaProtocol.java:527)
	at com.mysql.cj.mysqla.io.MysqlaProtocol.checkErrorPacket(MysqlaProtocol.java:723)
	at com.mysql.cj.mysqla.io.MysqlaProtocol.sendCommand(MysqlaProtocol.java:662)
	at com.mysql.cj.mysqla.io.MysqlaProtocol.sqlQueryDirect(MysqlaProtocol.java:950)
	at com.mysql.cj.mysqla.MysqlaSession.sqlQueryDirect(MysqlaSession.java:431)
	at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1974)
	... 55 more
Caused by: java.io.EOFException: Can not read response from server. Expected to read 4 bytes, read 0 bytes before connection was unexpectedly lost.
	at com.mysql.cj.core.io.FullReadInputStream.readFully(FullReadInputStream.java:61)
	at com.mysql.cj.mysqla.io.SimplePacketReader.readHeader(SimplePacketReader.java:60)
	at com.mysql.cj.mysqla.io.TimeTrackingPacketReader.readHeader(TimeTrackingPacketReader.java:48)
	at com.mysql.cj.mysqla.io.MultiPacketReader.readHeader(MultiPacketReader.java:51)
	at com.mysql.cj.mysqla.io.MysqlaProtocol.readPacket(MysqlaProtocol.java:521)
	... 60 more

网上其实有很多解决方案，我这里总计下吧：

1、mysql连接版本的问题

连接版本

<dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.48</version>
        </dependency>

mysql的数据库版本5.7.24

这里建议使用sql连接版本

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>6.0.6</version>
        </dependency>

备注：这里需要指定时区

&serverTimezone=GMT%2B8

2、事物隔离级别的问题

查完之后修改可能会出现该异常，变更事物隔离级别

SET GLOBAL tx_isolation='READ-COMMITTED';

3、仔细观察异常定位

com.alibaba.druid.pool.DruidDataSource   : discard connection

这个问题是mysql连接被关闭，而druid还认为连接有效导致的。

解决方法：

 用了ha吗？还是直连mysql，如果直连mysql查看wait_timeout修改为8小时。如果用了ha，数据库连接池的检测连接过期时间一定要小于ha和mysql的心跳超时。

zhuwei_clark
关注
专栏目录
java.sql.SQLException: 内部错误: Unable to construct a Datum from the specified input
01-21
Q: I am working with ... I am using updateBinaryStream method of resultset to update the BLOB field but it is failing after giving following exception java.sql.SQLException: Internal Error: Unable to
java.sql.SQLException:Could not retrieve transation read-only status server解决方法
yemuyouhan的博客
12-20 2万+
这个错误是无法检索只读服务，查询后发现，是jdbc驱动包和mysql的版本对不上，我用的mysql是8.0.10版本，而jdbc的版本是5.1.32，所以需要下载一个版本相同的jdbc驱动包。 下载链接：https://www.mysql.com/downloads/ 具体步骤如图： 1、 2、 3、 4、 5、因为我用的是Windows系统，所以选择对应版本的压缩包进行下载 6、直接下载...
java.sql.SQLException: Could not retrieve transaction read-only status from server 问题解决
weixin_30828379的博客
06-24 2138
网上有2种主要说法 第一种 问题描述：java代码在开始事务后，先做了一个查询，再insert，此时会报： java.sql.SQLException: could not retrieve transation read-only status server 解决过程： 查看mysql的事物隔离级别 SHOWVARIABLE...
解决mysql零时间问题
最新发布
zhangtao453的博客
08-06 150
查询发现，当mysql内数据格式为datetime，时间为'0000-00-00 00:00:00' 时，查询到此条数据，会报一个时间为Zero的错误，mysql无法映射此数据。解决办法，修改此条数据，改为正常时间，再次查询此条数据，发现已解决。查看日志，发现报了一个时间为零的错误。
Could not retrieve transation read-only status server 的解决办法
热门推荐
Spring Boot-Common On With You
06-12 4万+
问题描述：    在项目开发的过程中，使用Hibernate的ORM进行建表时，出现 “ Could not retrieve transation read-only status server ”的异常，无法进行表的创建的操作，具体异常内容如下：org.activiti.engine.ActivitiException: couldn't create db schema: create ta...
Could not retrieve transation read-only status server
阿飞云漫步
08-20 6360
背景 最近在部署一套完整的项目，部署过程中遇到很多的问题，在来总结一些如标题的这个错误！ 环境说明： 使用分布式数据库，使用的是mysql！ ### Cause: java.sql.SQLException: Could not retrieve transation read-only status server ; SQL []; Could not retrieve tran...
解决报错问题：java.sql.SQLException: Could not retrieve transation read-only status server
Koinu的博客
09-02 1071
java.sql.SQLException: Could not retrieve transation read-only status server 二、问题源码 package cn.tedu.test; import java.sql.*; import java.util.Scanner; public class Test3 { public static void main(String[] args) { method(); }....
java.sql.SQLException: null,  message from server: “Host ‘%’ is not allowed to connect to
12-14
java.sql.SQLException: null, message from server: “Host ‘223.72.41.7’ is not allowed to connect to this MySQL server” 客户端访问时报错： 解决方法： 1，登陆服务器 mysql> use mysql; //用mysql ...
java.sql.SQLException: 结果集已耗尽
08-02
在Java编程中，`java.sql.SQLException: 结果集已耗尽` 是一个常见的错误提示，通常出现在处理数据库查询结果集时。这个异常表明程序试图访问已经没有数据的结果集中下一行，即所有行已经被遍历完，尝试访问超出范围...
java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK
09-29
java.sql.SQLException: 不支持的字符集 (在类路径中添加 orai18n.jar): ZHS16GBK ……
Could not retrieve transation read-only status server 异常处理
weixin_39998576的博客
03-10 1118
java.sql.SQLException: Could not retrieve transation read-only status server 异常处理 原因：mysql与数据库版本不同引起 解决方式一：jar包引入指定mysql版本号 <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId>
java修改操作出错：could not retrieve transation read-only status server
格色情调的博客
12-20 1万+
##一、问题描述 我们一般在写新增、修改、删除操作业务代码时通常都比较简单，一般不会出现运行错误，但这次操作的是三张表（一张主表，二张关联表）出现了：could not retrieve transation read-only status server错误，具体修改操作业务逻辑是： 1、先修改主表，若修改成功即行数大于0则修改下面的关联表； 2、先找出第一张关联表要逻辑删除的数据及新增的数据，...
java.sql.SQLException: Could not retrieve transation read-only status server
hmg_monkey的专栏
07-08 1万+
org.springframework.dao.TransientDataAccessResourceException: <|### Error querying database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server<|### The error may exi
错误:java.util.Date cannot be cast to java.sql.Date
rj2017211811的博客
12-19 3425
错误信息: java.lang.ClassCastException: class java.util.Date cannot be cast to class java.sql.Date (java.util.Date is in module java.base of loader 'bootstrap'; java.sql.Date is in module java.sql of load...
mysql 报错Could not retrieve transation read-only status server
两只橙的博客
12-13 1万+
1、报错信息### Cause: java.sql.SQLException: Could not retrieve transation read-only status server ; SQL []; Could not retrieve transation read-only status server; nested exception is java.sql.SQLException:
: Failed to obtain JDBC Connection; nested exception is java.sql.SQLException: Could not retrieve transation read-only status server
12-20
nested exception is java.sql.SQLException: Could not retrieve transation read-only status server"的错误通常是由于无法获取JDBC连接引起的。这可能是由于以下原因之一导致的： 1. 数据库连接配置错误：请...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值