insert 数据库插入更新 报错 Could not retrieve transation read-only status server

🪐 TiDB 技术问题 部署&运维管理
1

【 TiDB 使用环境】
【概述】：场景 + 问题概述
【背景】：做过哪些操作
【现象】：业务和数据库现象
【问题】：当前遇到的问题
【业务影响】：
【TiDB 版本】：
v5.2.3
【附件】：

  • 相关日志
    ERROR c.e.c.m.s.impl.AdLogServiceImpl - 保存到新表异常saveAdLog：
    org.springframework.dao.TransientDataAccessResourceException:

Error updating database. Cause: java.sql.SQLException: Could not retrieve transation read-only status server

The error may involve com.emar.cbd.mcn.mapper.AdLogMapper.insertLogNew-Inline

The error occurred while setting parameters

SQL: INSERT INTO tbl_new_ssp_ad_log_? (adslotid,dspid,ac,date,hour,cost,price,os,appId,appChannel,create_time,reqid, isNewUser,app_version_name,adType,imei,android_id,user_id,tag,planId,onlyFlg,first,second,third,promotion,adtime,imei1,mac,oaid,adCodeId,materialId)VALUES(?,?,?,?,?,?,?,?,?,?,now(),?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)

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: Could not retrieve transation read-only status server
at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:108)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
at com.sun.proxy.$Proxy97.insert(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:278)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:58)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy113.insertLogNew(Unknown Source)
at sun.reflect.GeneratedMethodAccessor268.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:52)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
at org.springframework.aop.aspectj.AspectJAfterAdvice.invoke(AspectJAfterAdvice.java:47)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:168)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213)
at com.sun.proxy.$Proxy114.insertLogNew(Unknown Source)
at com.emar.cbd.mcn.service.impl.AdLogServiceImpl.saveNewDb(AdLogServiceImpl.java:200)
at com.emar.cbd.mcn.service.impl.AdLogServiceImpl.saveAdLog(AdLogServiceImpl.java:149)
at com.emar.cbd.mcn.kafka.KafkaConsumer$2.run(KafkaConsumer.java:70)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.sql.SQLException: Could not retrieve transation read-only status server

3 
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid</artifactId>
        <version>1.1.10</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>6.0.6</version>
    </dependency>
4

tidb 5.1.2 就正常

5

参考下这个帖子的说明：请教下，TiDB 应用端报错 read-only 什么原因导致的？

6

是因为应用读写分离 select @@session.tx_read_only 这时候数据库连接断了 ，
所以jdbc 就直接提示 ：
java.sql.SQLException: Could not retrieve transation read-only status server
tidb相关的应用 可能不需要做读写分离 。

7

大佬后续是怎么解决的？

8

此话题已在最后回复的 1 分钟后被自动关闭。不再允许新回复。