【Seata】AT模式常见报错及解决_jdbc commit failed; nested exception is java.sql.s

背景及版本

1、本文问题基于seata-1.4.2 到 1.5.1版本 AT 模式使用过程中遇到的问题
2、关于seata部署参考另外两篇博客：
seata-server-1.5.1-直接部署（整合nacos）
seata-server-1.5.2-直接部署,启动报错NoClassDefFoundError的坑

问题

1、java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

1.1、堆栈信息

14:43:27.559 [http-nio-9600-exec-8] INFO  i.s.t.a.DefaultGlobalTransaction - [begin,109] - Begin new global transaction [172.16.188.187:8091:3819348198384009577]
......省略......
14:43:27.938 [http-nio-9600-exec-8] ERROR druid.sql.Statement - [statementLogError,149] - {conn-10005, pstmt-20101} execute error. INSERT INTO undo_log (id,branch_id, xid, c
ontext, rollback_info, log_status, log_created, log_modified)VALUES (UNDO_LOG_SEQ.nextval, ?, ?, ?, ?, ?, sysdate, sysdate)
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:630)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:564)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1151)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:771)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:299)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:152)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:1052)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1531)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1311)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3746)
at oracle.jdbc.driver.OraclePreparedStatement.executeLargeUpdate(OraclePreparedStatement.java:3918)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:3897)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeUpdate(OraclePreparedStatementWrapper.java:992)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:3253)
at com.alibaba.druid.filter.FilterAdapter.preparedStatement_executeUpdate(FilterAdapter.java:1092)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeUpdate(FilterEventAdapter.java:491)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:3251)
at com.alibaba.druid.filter.FilterAdapter.preparedStatement_executeUpdate(FilterAdapter.java:1092)
at com.alibaba.druid.filter.FilterEventAdapter.preparedStatement_executeUpdate(FilterEventAdapter.java:491)
at com.alibaba.druid.filter.FilterChainImpl.preparedStatement_executeUpdate(FilterChainImpl.java:3251)
...... 省略......
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

1.2、原因

业务库中没有 undo_log 表，AT模式是基于undo_log回滚日志表实现的

1.3、解决

按seata版本和业务数据库类型选择 undo_log 建表脚本，在所有RM业务数据库库中都创建该表
1.5.1版本 脚本链接： https://gitee.com/mirrors/Seata/tree/v1.5.1/script/client/at/db

TIPS：
1、一定是所有业务服务的数据库中都要创建 undo_log表。
2、不同业务服务可以使用不同数据库，比如事务设计 订单-库存两个服务， 订单使用Oracle，库存使用 MySQL，ok的

2、Caused by: io.seata.common.exception.FrameworkException: No available service

2.1、堆栈信息

16:59:18.482 [http-nio-9700-exec-3] ERROR c.z.c.s.h.GlobalExceptionHandler - [handleRuntimeException,106] - 请求地址'/definition/startProcessInstance',发生未知异常.
org.springframework.transaction.TransactionSystemException: JDBC commit failed; nested exception is java.sql.SQLException: io.seata.core.exception.RmTransactionException: Runtime
        at org.springframework.jdbc.datasource.DataSourceTransactionManager.translateException(DataSourceTransactionManager.java:435)
        at org.springframework.jdbc.support.JdbcTransactionManager.translateException(JdbcTransactionManager.java:188)
...... 省略 ......
Caused by: io.seata.common.exception.FrameworkException: No available service
        at io.seata.core.rpc.netty.AbstractNettyRemotingClient.loadBalance(AbstractNettyRemotingClient.java:265)
        at io.seata.core.rpc.netty.AbstractNettyRemotingClient.sendSyncRequest(AbstractNettyRemotingClient.java:141)
        at io.seata.rm.AbstractResourceManager.branchRegister(AbstractResourceManager.java:67)
        ... 72 common frames xxx
1
2
3
4
5
6
7
8
9
10

2.2、原因

RM没有开启事务，seata 报错没有可用的服务，查看seata-server日志看不到flowable注册。
正常RM启动之后应该像seata-server注册，日志如下：

2.3、解决

配置文件中开启 seata 事务并重启。具体参考 seata-1.5.1整合nacos部署 ：三、Client端整合、使用

本文 是开发环境flowable的配置成 seata.enabled=false 导致的（用了 mybatis-plus 动态数据源 dynamic-datasource-spring-boot-starter）

spring: 
  datasource: 
    dynamic: 
      datasource: 
      # 开启seata代理，开启后默认每个数据源都代理，如果某个不需要代理可单独关闭
      seata: true
# seata配置
seata: 
  # spring.datasource.dynami.seata需要同时开启
  enabled: true
  # 关闭自动代理
  enable-auto-data-source-proxy: false
  # 其他配置省略
1
2
3
4
5
6
7
8
9
10
11
12
13

3、ShouldNeverHappenException: [xid:192.168.1.20:8091:7485266961695846527]get table meta failed, please check whether the table t_wq_contract exists.

3.1、堆栈信息

2022-07-15T07:44:15.909885469Z 15:44:15.909 [nacos-grpc-client-executor-475] INFO  c.a.n.c.r.client - [printIfInfoEnabled,60] - [db49c5d6-bb85-433b-b529-8af34a673fc5] Receive server push request, request = NotifySubscriberRequest, requestId = 8407
2022-07-15T07:44:15.911702598Z 15:44:15.911 [nacos-grpc-client-executor-475] INFO  c.a.n.c.r.client - [printIfInfoEnabled,60] - [db49c5d6-bb85-433b-b529-8af34a673fc5] Ack server push request, request = NotifySubscriberRequest, requestId = 8407
2022-07-15T07:44:15.960255054Z 15:44:15.959 [http-nio-9801-exec-3] ERROR i.s.r.d.s.s.c.AbstractTableMetaCache - [lambda$getTableMeta$0,63] - get table meta of the table `t_wq_contract` error: Failed to fetch schema of t_wq_contract
2022-07-15T07:44:15.960284036Z java.sql.SQLException: Failed to fetch schema of t_wq_contract
2022-07-15T07:44:15.960287256Z  at io.seata.rm.datasource.sql.struct.cache.OracleTableMetaCache.fetchSchema(OracleTableMetaCache.java:72)
2022-07-15T07:44:15.960291171Z  at io.seata.rm.datasource.sql.struct.cache.AbstractTableMetaCache.lambda$getTableMeta$0(AbstractTableMetaCache.java:61)
2022-07-15T07:44:15.960293932Z  at com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2406)
2022-07-15T07:44:15.960296931Z  at java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1853)
2022-07-15T07:44:15.960299751Z  at com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2404)
2022-07-15T07:44:15.960302535Z  at com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2387)
2022-07-15T07:44:15.960305176Z  at com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:108)
2022-07-15T07:44:15.960307738Z  at com.github.benmanes.caffeine.cache.LocalManualCache.get(LocalManualCache.java:62)
2022-07-15T07:44:15.960333487Z  at io.seata.rm.datasource.sql.struct.cache.AbstractTableMetaCache.getTableMeta(AbstractTableMetaCache.java:59)
2022-07-15T07:44:15.960336876Z  at io.seata.rm.datasource.exec.BaseTransactionalExecutor.getTableMeta(BaseTransactionalExecutor.java:260)
2022-07-15T07:44:15.960339688Z  at io.seata.rm.datasource.exec.BaseTransactionalExecutor.getTableMeta(BaseTransactionalExecutor.java:245)
2022-07-15T07:44:15.960342593Z  at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.isMultiPk(AbstractDMLBaseExecutor.java:111)
2022-07-15T07:44:15.960345272Z  at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitFalse(AbstractDMLBaseExecutor.java:96)
2022-07-15T07:44:15.960348017Z  at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.lambda$executeAutoCommitTrue$2(AbstractDMLBaseExecutor.java:143) 

...... 省略 ......

2022-07-15T07:44:15.960784034Z  at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java)
2022-07-15T07:44:15.960786798Z  at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
2022-07-15T07:44:15.960789489Z  at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78)
2022-07-15T07:44:15.960792115Z  at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357)
2022-07-15T07:44:15.960794696Z  at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382)
2022-07-15T07:44:15.960797295Z  at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
2022-07-15T07:44:15.960800009Z  at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895)
2022-07-15T07:44:15.960802617Z  at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732)
2022-07-15T07:44:15.960805307Z  at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
2022-07-15T07:44:15.960808053Z  at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191)
2022-07-15T07:44:15.960810770Z  at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659)
2022-07-15T07:44:15.960813400Z  at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
2022-07-15T07:44:15.960816343Z  at java.lang.Thread.run(Thread.java:750)
2022-07-15T07:44:15.960819337Z Caused by: io.seata.common.exception.ShouldNeverHappenException: Could not found any index in the table: T_WQ_CONTRACT
2022-07-15T07:44:15.960829752Z  at io.seata.rm.datasource.sql.struct.cache.OracleTableMetaCache.resultSetMetaToSchema(OracleTableMetaCache.java:155)
2022-07-15T07:44:15.960832505Z  at io.seata.rm.datasource.sql.struct.cache.OracleTableMetaCache.fetchSchema(OracleTableMetaCache.java:68)
2022-07-15T07:44:15.960835325Z  ... 147 common frames omitted
2022-07-15T07:44:15.964147378Z 15:44:15.961 [http-nio-9801-exec-3] ERROR i.s.r.d.e.AbstractDMLBaseExecutor - [executeAutoCommitTrue,149] - execute executeAutoCommitTrue error:[xid:192.168.1.20:8091:7485266961695846527]get table meta failed, please check whether the table `t_wq_contract` exists.

...... 省略 ......

2022-07-15T07:44:16.445290463Z 15:44:16.444 [http-nio-9801-exec-3] INFO  i.s.t.a.DefaultGlobalTransaction - [suspend,190] - Suspending current transaction, xid = 192.168.1.20:8091:7485266961695846527
2022-07-15T07:44:16.446881374Z 15:44:16.446 [http-nio-9801-exec-3] INFO  i.s.t.a.DefaultGlobalTransaction - [rollback,180] - [192.168.1.20:8091:7485266961695846527] rollback status: Rollbacked
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

3.2、原因

get table meta failed, please check whether the table t_wq_contract exists.，报错很清楚了，表中没有任何索引。

3.3、解决

ShouldNeverHappenException只看这个异常名称就很清楚了，永远不应该发生的错误。
按数据库三范式，表中至少要有主键，业务索引可以按需添加，添加主键即可

4、RmTransactionException: Response[ TransactionException[branch register request failed. xid=172.17.0.1:8091:6422420351288700552, msg=Unknown column ‘status’ in ‘field list’] ]

4.1、堆栈信息

忘了保留日志了，，，，
1

4.2、原因

排查过程：
1、客户端报错，所以排查发现客户端发现，业务方一切正常，业务表中有status字段，开启debug日志 打印的sql在到数据库中执行都ok。
2、客户端没问题那就看下seata-server的日志，然后发现server端同样报了Unknown column 'status' in 'field list'异常。
3、seata-server数据库版本为1.4.2的，seata-server应用为1.5.1版本，seata-server升级到1.5.1版本时，只处理了dev环境，test环境只是升级了seata-server应用，未升级数据库脚本。

原因
seata-1.5.0版本中，lock_table表新增了 status 字段

4.3、解决

根据seata-server版本和数据库 选择匹配的建表脚本 重新创建seata-server库
1.5.1版本脚本： https://gitee.com/mirrors/Seata/tree/v1.5.1/script/server/db

备注

目前遇到并记录的问题就这么多，还有oracle驱动版本和seata不兼容的问题，当时没有记录，，， 后续遇到其他问题会及时记录并持续更新