StarHuzy opened a new issue, #28691:
URL: https://github.com/apache/shardingsphere/issues/28691
## Bug Report
I used a single database sharding tables use local transaction mode
I have configured some tables
- `t_member`
- `t_order`
- `t_order_time`
- `t_product`
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:
- !TRANSACTION
defaultType: XA
providerType: Atomikos
- !SHARDING
tables:
t_member: # 分表，逻辑表名
# 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
actualDataNodes: testApp.t_member
tableStrategy: # 配置分表策略
standard: # 用于单分片键的标准分片场景
shardingColumn: tenant_id
shardingAlgorithmName: real-data-inline
t_product: # 分表，逻辑表名
# 节点表添加下初始的表,后续会在新增租户的时候新增表且刷新节点
actualDataNodes: testApp.t_product
tableStrategy: # 配置分表策略
standard: # 用于单分片键的标准分片场景
shardingColumn: tenant_id
shardingAlgorithmName: real-data-inline
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_member,t_product,t_order,t_order_item
props:
sql-show: true
mode:
type: Standalone
```
### 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
actualDataNodes Automatically loaded
2. I will `JOIN QUERY` with two tables with An exception has occurred
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
After execution, there will be no exceptions
But this `actualDataNode`s is the same as the previous method
### Reason analyze (If you can)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
