dynamic actual-data-nodes refresh in join query is invalid

[StarHuzy]

Bug Report

I used a single database sharding tables use local transaction mode
I have configured some tables

t_order
t_order_time

What I designed is Automatically split tableStrategy by tenant_id
this is my configuration
my project address by github

I customized the allocation calculation class DataShardingAlgorithm implements StandardShardingAlgorithm

DataShardingAlgorithm.Java

@Component
@Slf4j
public class DataShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    @Override
    public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
        return collection;
    }

    @Override
    public String doSharding(Collection<String> collection, PreciseShardingValue<Long> preciseShardingValue) {
        StringBuilder resultTableName = new StringBuilder();
        String logicTableName = preciseShardingValue.getLogicTableName();
        // 拼接的tenantId，格式为 表名_{tenant_id}
        resultTableName.append(logicTableName).append("_").append(preciseShardingValue.getValue());
        String newTableName = resultTableName.toString().toLowerCase();
        if (!collection.contains(newTableName)) {
            // 动态新增节点
            ShardingAlgorithmTool.copyTable(logicTableName,newTableName);
            collection.add(newTableName);
        }
        System.out.println("collection = " + collection);
        return newTableName;
    }
    @Override
    public String getType() {
        return null;
    }
    @Override
    public void init(Properties properties) {
    }
}

sharding.yaml

dataSources:
  testApp:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/app_test
    username: root
    password: 123456
rules:
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos
  - !SHARDING
    tables:
      t_order: # 分表，逻辑表名
        # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
        actualDataNodes: testApp.t_order
        tableStrategy: # 配置分表策略
          standard: # 用于单分片键的标准分片场景
            shardingColumn: tenant_id
            shardingAlgorithmName: real-data-inline
      t_order_item: # 分表，逻辑表名
        # 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
        actualDataNodes: testApp.t_order_item
        tableStrategy: # 配置分表策略
          standard: # 用于单分片键的标准分片场景
            shardingColumn: tenant_id
            shardingAlgorithmName: real-data-inline
    # 分片算法配置
    shardingAlgorithms:
      real-data-inline: # 分片算法名称
        type: CLASS_BASED #自定义策略
        props:
          strategy: standard
          # 包名+类名
          algorithmClassName: com.hcyl.cloud.test.utils.DataShardingAlgorithm
    bindingTables:
      - t_order,t_order_item
props:
  sql-show: true
mode:
  type: Standalone

Expected behavior

1. I will add, delete, modify, and check a single table
   

I have added data for the t_order table with tenant_id = 1

My database has automatically added t_order_1

I haven't configured a actualDataNodes with t_order_1 , but I can query it

Similarly, this table t_order_item also has the same result

2. I will JOIN QUERY with two tables ， But the result is as expected

### Error querying database.  Cause: java.sql.SQLException: Actual table `testApp.t_order_item_1` is not in table rule configuration.
### The error may exist in file [E:\sharging-jdbc\target\classes\mapper\OrderMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT o.order_no, SUM(i.price * i.count) AS amount         FROM t_order o JOIN t_order_item i ON o.order_no = i.order_no         where o.tenant_id = ? and i.tenant_id = ?         GROUP BY o.order_no
### Cause: java.sql.SQLException: Actual table `testApp.t_order_item_1` is not in table rule configuration.
; Actual table `testApp.t_order_item_1` is not in table rule configuration.; nested exception is java.sql.SQLException: Actual table `testApp.t_order_item_1` is not in table rule configuration.] with root cause

java.sql.SQLException: Actual table `testApp.t_order_item_1` is not in table rule configuration.
	at org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62) ~[shardingsphere-infra-util-5.3.2.jar:5.3.2]
	at org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:51) ~[shardingsphere-dialect-exception-core-5.3.2.jar:5.3.2]
	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:448) ~[shardingsphere-jdbc-core-5.3.2.jar:5.3.2]
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44) ~[HikariCP-4.0.3.jar:na]
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java) ~[HikariCP-4.0.3.jar:na]
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.3.jar:3.5.3]
	at com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:67) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:324) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.3.jar:3.5.3]
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:163) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:90) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147) ~[mybatis-3.5.3.jar:3.5.3]
	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.3.jar:3.5.3]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:426) ~[mybatis-spring-2.0.3.jar:2.0.3]
	at com.sun.proxy.$Proxy67.selectList(Unknown Source) ~[na:na]
	at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:223) ~[mybatis-spring-2.0.3.jar:2.0.3]
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:177) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:78) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96) ~[mybatis-plus-core-3.3.1.jar:3.3.1]
	at com.sun.proxy.$Proxy68.getOrderAmount(Unknown Source) ~[na:na]
	at org.example.service.impl.OrderServiceImpl.getOrderAmount(OrderServiceImpl.java:68) ~[classes/:na]
	at org.example.service.impl.OrderServiceImpl$$FastClassBySpringCGLIB$$d2a06cee.invoke(<generated>) ~[classes/:na]
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) ~[spring-core-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy.invokeMethod(CglibAopProxy.java:386) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy.access$000(CglibAopProxy.java:85) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:704) ~[spring-aop-5.3.29.jar:5.3.29]
	at org.example.service.impl.OrderServiceImpl$$EnhancerBySpringCGLIB$$c745fd74.getOrderAmount(<generated>) ~[classes/:na]
	at org.example.controller.OrderController.getOrderAmount(OrderController.java:51) ~[classes/:na]
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_131]
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_131]
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_131]
	at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_131]
	at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:150) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:117) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:895) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:808) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1072) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:965) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:555) ~[tomcat-embed-core-9.0.78.jar:4.0.FR]
	at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.3.29.jar:5.3.29]
	at javax.servlet.http.HttpServlet.service(HttpServlet.java:623) ~[tomcat-embed-core-9.0.78.jar:4.0.FR]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:209) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:51) ~[tomcat-embed-websocket-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.29.jar:5.3.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.29.jar:5.3.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.3.29.jar:5.3.29]
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) ~[spring-web-5.3.29.jar:5.3.29]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:178) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:153) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:167) ~[tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:90) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:481) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:130) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:93) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:390) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:63) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:926) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1791) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:52) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-embed-core-9.0.78.jar:9.0.78]
	at java.lang.Thread.run(Thread.java:748) [na:1.8.0_131]

Reason analyze (If you can)

Which version of ShardingSphere did you use?

shardingsphere-jdbc 5.3.2 + JDK 17 + spring-boot 2.7.14

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Reason analyze (If you can)

java.sql.SQLException: Actual table testApp.t_order_item_1 is not in table rule configuration.

JOIN QUERY The actualDataNodes displaying the associated table is not configured
But the actualDataNodes of a single table seem to be automatically loaded

DataShardingAlgorithm.java doSharding(Collection<String> collection) with It seems that the current node can be obtained

Which version of ShardingSphere did you use?

shardingsphere-jdbc 5.3.2 + JDK 17 + spring-boot 2.7.14

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

[linghengqian]

• late reply. I won't have time to confirm the real problem until tomorrow. This seems to be related to some issues on the ShardingSphere JDBC 5.4.1 milestone.

[StarHuzy]

• late reply. I won't have time to confirm the real problem until tomorrow. This seems to be related to some issues on the ShardingSphere JDBC 5.4.1 milestone.

SpringBoot seems to have no DataSource management in ShardingSphere-jdbc 5.3.2。
How to obtain the DataSource object when adjusting the configuration reading of the Driver to the SPI interface in ShardingSphere-jdbc 5.3.2 ？
But can we refresh the table rule actualDataNodes by obtaining the dataSource？

[linghengqian]

• late reply. I won't have time to confirm the real problem until tomorrow. This seems to be related to some issues on the ShardingSphere JDBC 5.4.1 milestone.

SpringBoot seems to have no DataSource management in ShardingSphere-jdbc 5.3.2。 How to obtain the DataSource object when adjusting the configuration reading of the Driver to the SPI interface in ShardingSphere-jdbc 5.3.2 ？ But can we refresh the table rule actualDataNodes by obtaining the dataSource？

• You must consider that ShardingSphere JDBC 5.3.2 refactors the upstream and downstream ContextManager for SPI extensions to third-party metadata centers.

• I wrote an article at [Article Contribution] Welcome to Leave Your Article Link Here #12258 (comment), which mentioned how to get the real ShardingSphereDataSource to use ContextManager on ShardingSphere JDBC 5.3.0, which You need to actively define a Spring Bean. I am trying to help the downstream understand from a beginner's perspective how ContextManager serves dynamic update requirements like DistSQL.

• This article also mentions how to obtain the ContextManager through Connection when configuring through JDBC Driver. But not long after I noticed that this particular example had a bug at Fix the problem that JDBC Connection fails collectively after updating metadata in the example of shardingsphere-v530-jdk8-modern linghengqian/shardingsphere-contextmanager-explore#3. I haven't figured out how to fix it yet -- even though I'm a ShardingSphere Committer, I have my knowledge blind spots because the master branch's unit tests change very frequently.

• I plan to refactor this article in the foreseeable future and include Examples for ShardingSphere JDBC 5.3.1, 5.3.2, 5.4.0 and 5.4.1. This TODO is documented at https://www.yuque.com/linghengqian/meve2v/ykridze9nguvect5 . Considering that I'm a graduating college student, finding time is difficult for me.

• As a warning, I definitely don't think you should inject any Spring Beans into the algorithmic class implementation of ShardingSphere SPI, which conflicts with the definition of DistSQL. Because the algorithm class must actually be the final class, DistSQL can remove and recreate the algorithm class.

• As a further extension, I exposed the Row Value Expressions SPI at Make actualDataNodes expose SPI that can define expression with custom rules and add GraalVM Truffle implementation #22899. This allows for a more flexible way of defining ActualDataNodes. This feature is on the pre-release branch of ShardingSphere 5.4.1.

• Update: The article I mentioned touches on a very unfortunate problem that I have no energy to translate into English, which leads me to assume that you understand Chinese.

[StarHuzy]

• late reply. I won't have time to confirm the real problem until tomorrow. This seems to be related to some issues on the ShardingSphere JDBC 5.4.1 milestone.

SpringBoot seems to have no DataSource management in ShardingSphere-jdbc 5.3.2。 How to obtain the DataSource object when adjusting the configuration reading of the Driver to the SPI interface in ShardingSphere-jdbc 5.3.2 ？ But can we refresh the table rule actualDataNodes by obtaining the dataSource？

• You must consider that ShardingSphere JDBC 5.3.2 refactors the upstream and downstream ContextManager for SPI extensions to third-party metadata centers.
• I wrote an article at [Article Contribution] Welcome to Leave Your Article Link Here #12258 (comment), which mentioned how to get the real ShardingSphereDataSource to use ContextManager on ShardingSphere JDBC 5.3.0, which You need to actively define a Spring Bean. I am trying to help the downstream understand from a beginner's perspective how ContextManager serves dynamic update requirements like DistSQL.
• This article also mentions how to obtain the ContextManager through Connection when configuring through JDBC Driver. But not long after I noticed that this particular example had a bug at Fix the problem that JDBC Connection fails collectively after updating metadata in the example of shardingsphere-v530-jdk8-modern linghengqian/shardingsphere-contextmanager-explore#3. I haven't figured out how to fix it yet -- even though I'm a ShardingSphere Committer, I have my knowledge blind spots because the master branch's unit tests change very frequently.
• I plan to refactor this article in the foreseeable future and include Examples for ShardingSphere JDBC 5.3.1, 5.3.2, 5.4.0 and 5.4.1. This TODO is documented at https://www.yuque.com/linghengqian/meve2v/ykridze9nguvect5 . Considering that I'm a graduating college student, finding time is difficult for me.
• As a warning, I definitely don't think you should inject any Spring Beans into the algorithmic class implementation of ShardingSphere SPI, which conflicts with the definition of DistSQL. Because the algorithm class must actually be the final class, DistSQL can remove and recreate the algorithm class.
• As a further extension, I exposed the Row Value Expressions SPI at Make actualDataNodes expose SPI that can define expression with custom rules and add GraalVM Truffle implementation #22899. This allows for a more flexible way of defining ActualDataNodes. This feature is on the pre-release branch of ShardingSphere 5.4.1.
• Update: The article I mentioned touches on a very unfortunate problem that I have no energy to translate into English, which leads me to assume that you understand Chinese.

Hello, I tried many versions today and finally implemented this feature in ShardingSphere JDBC version 5.1.1. The actual data nodes only need to configure the dynamically loaded logical table names to execute SQL on single or JOIN QUERY multiple tables. However, there seems to be a new issue with @ transactional () annotation management transactions. When my msyql connection driver version is mysql connector java 8.0.33, the transaction fails, After I downgraded version 8.0.20, the transaction was successful. Is this the reason for the new features of the version? If I want to use distributed transactions in version 5.1.1, will the above issues cause this issue

[linghengqian]

Hello, I tried many versions today and finally implemented this feature in ShardingSphere JDBC version 5.1.1. The actual data nodes only need to configure the dynamically loaded logical table names to execute SQL on single or JOIN QUERY multiple tables. However, there seems to be a new issue with @ transactional () annotation management transactions. When my msyql connection driver version is mysql connector java 8.0.33, the transaction fails, After I downgraded version 8.0.20, the transaction was successful. Is this the reason for the new features of the version? If I want to use distributed transactions in version 5.1.1, will the above issues cause this issue

• Considering shardingSphere proxy 5.4.0 transaction not rolled back (transaction type:LOCAL) #28451 , I feel like this might be something that some of the ShardingSphere PRs synchronizing MySQL changes, but since I haven't finished investigating the current issue, it's all unknown.

[linghengqian]

@StarHuzy

1st

• Your Example has become a highly complicated step. Most of the classes have nothing to do with the issues mentioned in the issue, which makes it difficult to read.
• I deleted the Controller layer and Service layer, and deleted the parameters of the DTO layer and Mybatis Mapper layer to directly expose the core logic in the unit test.
• I deleted the configuration of Atomikos distributed transactions. What happens in Atomikos should be discussed separately, because it will even overlap with the transaction log automatically opened by Mybatis Plus, complicating error reporting.
• I used testcontainers instead of MySQL to expose problems earlier. If you really need MySQL, you should use testcontainers to create a MySQL container. I have previously added testcontainer url support in the ShardingSphere 5.3.1 milestone.
• Without Maven Wrapper, I have no way of knowing which version of Maven you are using. Initialize Wrapper on the spot.
• I didn't see the README.md explaining this project and I simply removed all JSON handling.
• Your app_test.sql makes no sense at all. It is just a SQL file for some logical table. It does not involve SQL for real database tables, and unit tests cannot be written.

2nd

• I simplified yours logic at https://github.com/linghengqian/sharging-jdbc-star-huzy-test/tree/fix-by-lingh by linghengqian-scratches/sharging-jdbc-star-huzy-test@c3b3db6 .
• Let's start discussing the problem in your example.
• Your 3 classes DataShardingMapper, ShardingAlgorithmTool and SpringUtils make no sense. These classes do not involve ShardingSphere's metadata changes at all, and naturally the metadata is not refreshed.
• All IMHO, your core logic only involves org.example.ShardingJdbcTest#testIssues28704.
• You must manually take out the ContextManager and complete a series of real metadata changes, which requires simulating the behavior of DistSQL. Just like the article I mentioned in the previous part of the issue.
• I did not include a simulated operation of DistSQL in the example, because if you don't understand how DistSQL is written, what you will see is a lot of magic code. I first need to know what you think in the context of example.

[github-actions]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[linghengqian]

• There is no response from the creator of the issue, so I think there is no point in continuing to open this issue.