youn9yon9 opened a new issue, #29701:
URL: https://github.com/apache/shardingsphere/issues/29701
Which version of ShardingSphere did you use?
5.4.1
Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
Expected behavior
Order_info and config_store_info can be joined across databases.
Actual behavior
my yaml configļ¼
```yaml
dataSources:
ds0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl:
jdbc:mysql://xxxx:3306/order?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false
username: xxx
password: xxxx
ds1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl:
jdbc:mysql://xxxx:3306/order?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false
username: xxx
password: xxxx
ds2:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.jdbc.Driver
jdbcUrl:
jdbc:mysql://xxxx:3306/config?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai&allowMultiQueries=true&useSSL=false
username: xxx
password: xxxx
rules:
- !SHARDING
tables:
order_info:
actualDataNodes: ds${0..1}.order_info_${0..15}
databaseStrategy:
hint:
shardingAlgorithmName: hint-custom
tableStrategy:
standard:
shardingColumn: order_start_time
shardingAlgorithmName: auto-custom
order_invoice:
actualDataNodes: ds${0..1}.order_invoice_${0..15}
databaseStrategy:
hint:
shardingAlgorithmName: hint-custom
tableStrategy:
standard:
shardingColumn: order_start_time
shardingAlgorithmName: auto-custom
order_item:
actualDataNodes: ds${0..1}.order_item_${0..15}
tableStrategy:
standard:
shardingColumn: order_start_time
shardingAlgorithmName: auto-custom
order_invoice_item:
actualDataNodes: ds${0..1}.order_invoice_item_${0..15}
tableStrategy:
standard:
shardingColumn: order_start_time
shardingAlgorithmName: auto-custom
order_consume_log:
actualDataNodes: ds${0..1}.order_consume_log_${0..15}
tableStrategy:
standard:
shardingColumn: order_start_time
shardingAlgorithmName: auto-custom
shardingAlgorithms:
auto-custom:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName: xxx.OrderTimeShardingAlgorithm
hint-custom:
type: CLASS_BASED
props:
strategy: hint
algorithmClassName: xxx.OrderDBShardingAlgorithm
bindingTables:
-
order_info,order_item,order_invoice,order_invoice_item,order_consume_log
- !SINGLE
tables:
- ds2.config_store_info
props:
sql-show: true
sql-federation-enabled: true
```
Error messageļ¼
### SQL: select t0.order_id as orderId from order_info t0
left join config_store_info t2 on t0.store_id = t2.id and t2.deleted = 0
where t2.id = ? and t0.order_start_time between ? and ?
### Cause: java.sql.SQLException: Unsupported SQL operation: all tables must
be in the same compute node.
; uncategorized SQLException; SQL state [0A000]; error code [30001];
Unsupported SQL operation: all tables must be in the same compute node.; nested
exception is java.sql.SQLException: Unsupported SQL operation: all tables must
be in the same compute node.
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:89)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
at com.sun.proxy.$Proxy155.selectList(Unknown Source)
at
org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77)
at
com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
at
com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
at com.sun.proxy.$Proxy159.selectForTest(Unknown Source)
at
com.xiaowangtech.order.service.OrderInfoServiceImplTest.afsOrderPage(OrderInfoServiceImplTest.java:83)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
at
org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
at
org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
at
org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
at
org.springframework.test.context.junit4.statements.RunBeforeTestExecutionCallbacks.evaluate(RunBeforeTestExecutionCallbacks.java:74)
at
org.springframework.test.context.junit4.statements.RunAfterTestExecutionCallbacks.evaluate(RunAfterTestExecutionCallbacks.java:84)
at
org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:75)
at
org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at
org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at
org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at
org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at
org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at
org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at
org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at
com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at
com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at
com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:235)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:54)
Caused by: java.sql.SQLException: Unsupported SQL operation: all tables must
be in the same compute node.
at
org.apache.shardingsphere.infra.exception.core.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62)
at
org.apache.shardingsphere.infra.exception.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:425)
at
com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy248.execute(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
at
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
... 41 more
--
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]
