【Seata】AT模式常见报错及解决

小灰灰 2024-04-01 18:16 14阅读 0赞

## 背景及版本 ##

1、本文问题基于seata-`1.4.2` 到 `1.5.1`版本 `AT` 模式使用过程中遇到的问题  
2、关于seata部署参考另外两篇博客：  
[seata-server-1.5.1-直接部署（整合nacos）][seata-server-1.5.1-_nacos]  
[seata-server-1.5.2-直接部署,启动报错NoClassDefFoundError的坑][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、原因 ####

业务库中没有 `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][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

#### 2.2、原因 ####

RM没有开启事务，seata 报错没有可用的服务，查看seata-server日志看不到flowable注册。  
正常RM启动之后应该像seata-server注册，日志如下：  
![在这里插入图片描述][c2a3a50bf1fe45c397ae6114e42622db.png]

#### 2.3、解决 ####

配置文件中开启 seata 事务并重启。具体参考 [seata-1.5.1整合nacos部署][seata-server-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
      # 其他配置省略

### 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

#### 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、堆栈信息 ####

忘了保留日志了，，，，

#### 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` 字段  
![在这里插入图片描述][5cc6dcb0e66e4db4a343047fb0c3a95a.png]

#### 4.3、解决 ####

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

## 备注 ##

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

[seata-server-1.5.1-_nacos]: https://blog.csdn.net/weixin_43582081/article/details/126071674
[seata-server-1.5.2-_NoClassDefFoundError]: https://blog.csdn.net/weixin_43582081/article/details/125949859
[https_gitee.com_mirrors_Seata_tree_v1.5.1_script_client_at_db]: https://gitee.com/mirrors/Seata/tree/v1.5.1/script/client/at/db
[c2a3a50bf1fe45c397ae6114e42622db.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/21761e91798c4fca9e6dcfe2879bcd50.png
[5cc6dcb0e66e4db4a343047fb0c3a95a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/01/80ec9219da5a461d992685e3d20b51f2.png
[https_gitee.com_mirrors_Seata_tree_v1.5.1_script_server_db]: https://gitee.com/mirrors/Seata/tree/v1.5.1/script/server/db