search

apache / shardingsphere

Empowering Data Intelligence with Distributed SQL for Sharding, Scalability, and Security Across All Databases.
Apache License 2.0
19.92k stars 6.73k forks source link

Major bugs！dynamic actual-data-nodes in join query is invalid ！ #28691

Closed StarHuzy closed 9 months ago

StarHuzy commented 1 year ago

Bug Report

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

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

  1. I customized the allocation calculation class

    
@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) {
}
}
2. `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:

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

Expected behavior

  1. I will add, delete, modify, and check a single table
  2. I will JOIN QUERY with two tables

Actual behavior

  1. SQL execution of a single table without any exceptions image

actualDataNodes Automatically loaded

image

image

  1. I will JOIN QUERY with two tables with An exception has occurred

image

I query tenant_id = 2 By viewing the print below ，actualDataNodeshas been dynamically loaded 

collection = [t_order_item, t_order_item_2]
2023-10-09 10:52:30.803 ERROR 11916 --- [io-9500-exec-10] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.DataIntegrityViolationException: 
### Error querying database.  Cause: java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.
### The error may exist in file [E:\hc-cloud\hc_cloud_app_test\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                       limit ?,?
### Cause: java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.
; Actual table `testApp.t_order_item_2` is not in table rule configuration.; nested exception is java.sql.SQLException: Actual table `testApp.t_order_item_2` is not in table rule configuration.] with root cause

java.sql.SQLException: Actual table `testApp.t_order_item_2` 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.10.jar:3.5.10]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.10.jar:3.5.10]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.10.jar:3.5.10]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[na:na]
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
    at java.base/java.lang.reflect.Method.invoke(Method.java:568) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring

But if I start configuring it

image After execution, there will be no exceptions But this actualDataNodes is the same as the previous method image

Reason analyze (If you can)

linghengqian commented 1 year ago
github-actions[bot] commented 11 months ago

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

github-actions[bot] commented 10 months ago

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

linghengqian commented 9 months ago