search

apache / shardingsphere

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

Oracle join query Exception but sample query success, how to deal ? #23511

Closed woshiluoshao closed 4 months ago

woshiluoshao commented 1 year ago

Execute ID : cn.ffcs.cty.modules.templatemanage.mapper.TemplateCfgMapper.selectList Execute SQL : select * from T_TEMPLATE_CFG T, readwrite_ds.zhsq.t_dc_org_entity_info B where T.org_code = B.org_code and T.IS_VALID = '1' Execute Time: 2023-01-11T10:47:21.711 ============== Sql End ==============

2023-01-11 10:47:25,649 INFO [http-nio-8889-exec-1] org.apache.shardingsphere.infra.executor.sql.log.SQLLogger(74): Logic SQL: SELECT FROM T_TEMPLATE_CFG T, readwrite_ds.zhsq.t_dc_org_entity_info B WHERE T.org_code = B.org_code AND T.IS_VALID = '1' 2023-01-11 10:47:25,649 INFO [http-nio-8889-exec-1] org.apache.shardingsphere.infra.executor.sql.log.SQLLogger(74): SQLStatement: OracleSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=7, projections=[ShorthandProjectionSegment(startIndex=7, stopIndex=7, owner=Optional.empty, alias=Optional.empty)], distinctRow=false), from=JoinTableSegment(startIndex=14, stopIndex=48, alias=Optional.empty, left=SimpleTableSegment(tableName=TableNameSegment(startIndex=14, stopIndex=27, identifier=IdentifierValue(value=T_TEMPLATE_CFG, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[T]), joinType=COMMA, right=SimpleTableSegment(tableName=TableNameSegment(startIndex=45, stopIndex=48, identifier=IdentifierValue(value=zhsq, quoteCharacter=NONE)), owner=Optional[OwnerSegment(startIndex=32, stopIndex=43, identifier=IdentifierValue(value=readwrite_ds, quoteCharacter=NONE), owner=Optional.empty)], alias=Optional.empty), condition=null, using=[]), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), lock=Optional.empty, modelSegment=Optional.empty, withSegment=Optional.empty) 2023-01-11 10:47:25,649 INFO [http-nio-8889-exec-1] org.apache.shardingsphere.infra.executor.sql.log.SQLLogger(74): Actual SQL: write_ds ::: SELECT FROM T_TEMPLATE_CFG T, zhsq.t_dc_org_entity_info B WHERE T.org_code = B.org_code AND T.IS_VALID = '1'

============== Sql Start ============== Execute ID : cn.ffcs.cty.modules.templatemanage.mapper.TemplateCfgMapper.selectList2 Execute SQL : select count(1) from T_TEMPLATE_CFG T join readwrite_ds.zhsq.t_dc_org_entity_info B on T.org_code = B.org_code where T.IS_VALID = '1' Execute Time: 2023-01-11T10:47:33.005 ============== Sql End ==============

2023-01-11 10:47:33,471 INFO [http-nio-8889-exec-1] org.apache.shardingsphere.infra.executor.sql.log.SQLLogger(74): Logic SQL: SELECT count(1) FROM T_TEMPLATE_CFG T JOIN readwrite_ds.zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1' 2023-01-11 10:47:33,472 INFO [http-nio-8889-exec-1] org.apache.shardingsphere.infra.executor.sql.log.SQLLogger(74): SQLStatement: OracleSelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=14, projections=[AggregationProjectionSegment(startIndex=7, stopIndex=14, type=COUNT, innerExpression=(1), parameters=[LiteralExpressionSegment(startIndex=13, stopIndex=13, literals=1)], alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=21, stopIndex=34, identifier=IdentifierValue(value=T_TEMPLATE_CFG, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[T]), where=Optional.empty, groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), lock=Optional.empty, modelSegment=Optional.empty, withSegment=Optional.empty) 2023-01-11 10:47:33,472 INFO [http-nio-8889-exec-1] org.apache.shardingsphere.infra.executor.sql.log.SQLLogger(74): Actual SQL: write_ds ::: SELECT count(1) FROM T_TEMPLATE_CFG T JOIN readwrite_ds.zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1' org.springframework.jdbc.BadSqlGrammarException:

Error querying database. Cause: java.sql.SQLSyntaxErrorException: ORA-00905: 缺失关键字 The error may exist in file [D:\workspace\FFCS\cty-project\cty\cty-service\target\classes\cn\ffcs\cty\modules\templatemanage\mapper\TemplateCfgMapper.xml] The error may involve defaultParameterMap The error occurred while setting parameters SQL: SELECT count(1) FROM T_TEMPLATE_CFG T JOIN readwrite_ds.zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1' Cause: java.sql.SQLSyntaxErrorException: ORA-00905: 缺失关键字 ; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: ORA-00905: 缺失关键字

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70) at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79) at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91) at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) at com.sun.proxy.$Proxy160.selectOne(Unknown Source) at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160) at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:89) 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.$Proxy217.selectList2(Unknown Source) at cn.ffcs.cty.modules.templatemanage.service.impl.TemplateCfgServiceImpl.searchList(TemplateCfgServiceImpl.java:64) at cn.ffcs.cty.modules.templatemanage.service.impl.TemplateCfgServiceImpl$$FastClassBySpringCGLIB$$1.invoke() at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:783) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753) at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:123) at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:388) at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119) at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:753) at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:698) at cn.ffcs.cty.modules.templatemanage.service.impl.TemplateCfgServiceImpl$$EnhancerBySpringCGLIB$$1.searchList() at org.apache.dubbo.common.bytecode.Wrapper46.invokeMethod(Wrapper46.java) at org.apache.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:47) at org.apache.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:84) at org.apache.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:56) at org.apache.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:56) at cn.ffcs.shequ.log.utils.exception.FFCSDubboExceptionFilter.invoke(FFCSDubboExceptionFilter.java:23) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:44) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:52) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:192) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:41) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:129) at org.apache.dubbo.rpc.protocol.FilterNode.invoke(FilterNode.java:61) at org.apache.dubbo.rpc.proxy.InvokerInvocationHandler.invoke(InvokerInvocationHandler.java:96) at org.apache.dubbo.common.bytecode.proxy39.searchList(proxy39.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 com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:302) at com.caucho.hessian.server.HessianSkeleton.invoke(HessianSkeleton.java:198) at org.apache.dubbo.rpc.protocol.hessian.HessianProtocol$HessianHandler.handle(HessianProtocol.java:206) at org.apache.dubbo.remoting.http.servlet.DispatcherServlet.service(DispatcherServlet.java:64) at javax.servlet.http.HttpServlet.service(HttpServlet.java:764) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:227) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at com.alibaba.druid.support.http.WebStatFilter.doFilter(WebStatFilter.java:124) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:96) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:117) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:189) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:162) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) at org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:97) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:41002) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:540) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:135) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:78) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:357) at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:382) at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:895) at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1732) at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) at org.apache.tomcat.util.threads.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1191) at org.apache.tomcat.util.threads.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:659) at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) at java.lang.Thread.run(Thread.java:745)

Before report a bug, make sure you have:

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

ShardingSphere-JDBC

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

oracle join query execute success

Expected behavior

oracle join query execute success

Actual behavior

oracle join query execute fail, the table add logic_db name; but sample table query success

Reason analyze (If you can)

i import ojdbc8 + shardingsphere5.2.1, oracle single table query can success ,but join other table execute fail and thow exception , is shardingsphere5.2.1 can not support oracle join query ?

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

1.application.properties

dbuser.xxx 修改,以下为mybatis-plus的配置方式,主要是value前面需要添加 readwrite_ds. 逻辑数据库名,非mybatis的配置方式如下:dbuser.gbp=readwrite_ds.gbp

mybatis-plus.configurationProperties.dbuser.workflow=readwrite_ds.workflow mybatis-plus.configurationProperties.dbuser.gbp=readwrite_ds.gbp mybatis-plus.configurationProperties.dbuser.zhsq=readwrite_ds.zhsq

oracle

jdbc.datasource.url=jdbc:oracle:thin:@ip:port:xxx jdbc.datasource.username=username jdbc.datasource.password=pwd

slave1.jdbc.datasource.url=jdbc:wrap-jdbc:filters=default:name=dragoon:jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=ip)(PORT=1521))(LOAD_BALANCE=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=betadb))) slave1.jdbc.datasource.username=username slave1.jdbc.datasource.password=pwd.123

一些属性配置 see org.apache.shardingsphere.infra.config.props.ConfigurationPropertyKey

spring.shardingsphere.enable=false spring.shardingsphere.props.sql-show=true spring.shardingsphere.props.check-table-metadata-enabled=false spring.shardingsphere.props.max-connections-size-per-query=50

spring.shardingsphere.props.sql-federation-type=NONE, ORIGINAL, ADVANCED

spring.shardingsphere.props.sql-federation-type=ORIGINAL spring.shardingsphere.props.sql-federation-enabled=true

模式配置,此处为单击模式,也支持集群,集群支持nacos或zookeeper为注册中心

spring.shardingsphere.mode.type=Standalone spring.shardingsphere.mode.repository.type=JDBC

逻辑数据库名称,默认值是logic_db

spring.shardingsphere.database.name=readwrite_ds

真实数据源,多个使用逗号分隔,支持一写多读

spring.shardingsphere.datasource.names=write_ds,read_ds1

数据源配置,写数据源

spring.shardingsphere.datasource.write_ds.url=${jdbc.datasource.url} spring.shardingsphere.datasource.write_ds.username=${jdbc.datasource.username} spring.shardingsphere.datasource.write_ds.password=${jdbc.datasource.password}

数据源配置,第1个读数据源

spring.shardingsphere.datasource.read_ds1.url=${slave1.jdbc.datasource.url} spring.shardingsphere.datasource.read_ds1.username=${slave1.jdbc.datasource.username} spring.shardingsphere.datasource.read_ds1.password=${slave1.jdbc.datasource.password}

其他的数据源配置参考durid,配置如下,请自行补充,建议这些配置放到yml文件

spring.shardingsphere.datasource.write_ds.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.write_ds.initial-size=${jdbc.datasource.initialSize} spring.shardingsphere.datasource.write_ds.min-idle=${jdbc.datasource.minIdle} spring.shardingsphere.datasource.write_ds.maxActive=${jdbc.datasource.maxActive} spring.shardingsphere.datasource.write_ds.maxWait=${jdbc.datasource.maxWait} spring.shardingsphere.datasource.write_ds.timeBetweenEvictionRunsMillis=${jdbc.datasource.timeBetweenEvictionRunsMillis} spring.shardingsphere.datasource.write_ds.minEvictableIdleTimeMillis=${jdbc.datasource.minEvictableIdleTimeMillis} spring.shardingsphere.datasource.write_ds.validationQuery=${jdbc.datasource.validationQuery} spring.shardingsphere.datasource.write_ds.testWhileIdle=${jdbc.datasource.testWhileIdle} spring.shardingsphere.datasource.write_ds.testOnBorrow=${jdbc.datasource.testOnBorrow} spring.shardingsphere.datasource.write_ds.testOnReturn=${jdbc.datasource.testOnReturn} spring.shardingsphere.datasource.write_ds.poolPreparedStatements=${jdbc.datasource.poolPreparedStatements} spring.shardingsphere.datasource.write_ds.maxPoolPreparedStatementPerConnectionSize=${jdbc.datasource.maxWait}

filters开启貌似有问题

spring.shardingsphere.datasource.write_ds.filters=${jdbc.datasource.filters}

spring.shardingsphere.datasource.write_ds.connectionProperties=config.decrypt=true

slave

spring.shardingsphere.datasource.read_ds1.dataSourceClassName=com.alibaba.druid.pool.DruidDataSource

spring.shardingsphere.datasource.read_ds1.type=com.alibaba.druid.pool.DruidDataSource spring.shardingsphere.datasource.read_ds1.initial-size=${jdbc.datasource.initialSize} spring.shardingsphere.datasource.read_ds1.min-idle=${jdbc.datasource.minIdle} spring.shardingsphere.datasource.read_ds1.maxActive=${jdbc.datasource.maxActive} spring.shardingsphere.datasource.read_ds1.maxWait=${jdbc.datasource.maxWait} spring.shardingsphere.datasource.read_ds1.timeBetweenEvictionRunsMillis=${jdbc.datasource.timeBetweenEvictionRunsMillis} spring.shardingsphere.datasource.read_ds1.minEvictableIdleTimeMillis=${jdbc.datasource.minEvictableIdleTimeMillis} spring.shardingsphere.datasource.read_ds1.validationQuery=${jdbc.datasource.validationQuery} spring.shardingsphere.datasource.read_ds1.testWhileIdle=${jdbc.datasource.testWhileIdle} spring.shardingsphere.datasource.read_ds1.testOnBorrow=${jdbc.datasource.testOnBorrow} spring.shardingsphere.datasource.read_ds1.testOnReturn=${jdbc.datasource.testOnReturn} spring.shardingsphere.datasource.read_ds1.poolPreparedStatements=${jdbc.datasource.poolPreparedStatements} spring.shardingsphere.datasource.read_ds1.maxPoolPreparedStatementPerConnectionSize=${jdbc.datasource.maxWait}

spring.shardingsphere.datasource.read_ds1.filters=${jdbc.datasource.filters}

spring.shardingsphere.datasource.read_ds1.connectionProperties=config.decrypt=false

读写分离配置

spring.shardingsphere.rules.readwrite-splitting.data-sources.logic_db.static-strategy.write-data-source-name=write_ds spring.shardingsphere.rules.readwrite-splitting.data-sources.logic_db.static-strategy.read-data-source-names=read_ds1

负载均衡算法名称

spring.shardingsphere.rules.readwrite-splitting.data-sources.logic_db.load-balancer-name=round_robin

负载均衡算法类型-轮询调度

spring.shardingsphere.rules.readwrite-splitting.load-balancers.round_robin.type=ROUND_ROBIN

h2数据库控制台输出,shardingphere默认使用h2数据库管理元数据

spring.h2.console.enabled=false

2.pom.xml

org.apache.shardingsphere shardingsphere-jdbc-core-spring-boot-starter ${shardingsphere.version} guava com.google.guava snakeyaml org.yaml org.yaml snakeyaml 1.33 com.oracle.ojdbc ojdbc8 19.3.0.0 com.oracle.ojdbc orai18n 19.3.0.0

Example codes for reproduce this issue (such as a github link).

execute success Sql(sample query) select id="selectList" resultMap="BaseResultMap"> select * from T_TEMPLATE_CFG T, ${dbuser.zhsq}.t_dc_org_entity_info B where T.org_code = B.org_code and T.IS_VALID = '1'

execute fial SQL(join query) select count(1) from T_TEMPLATE_CFG T join ${dbuser.zhsq}.t_dc_org_entity_info B on T.org_code = B.org_code where T.IS_VALID = '1'

please help me , how to deal this bug?

strongduanmu commented 1 year ago

Hi @woshiluoshao, can you try to remove readwrite_ds.zhsq. here?

Actual SQL: write_ds ::: SELECT count(1) FROM T_TEMPLATE_CFG T JOIN readwrite_ds.zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1'
woshiluoshao commented 1 year ago

Hi @woshiluoshao, can you try to remove readwrite_ds.zhsq. here?

Actual SQL: write_ds ::: SELECT count(1) FROM T_TEMPLATE_CFG T JOIN readwrite_ds.zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1'

zhsq is oracle database , join query ; readwrite_ds is shardingsphere auto add; i try to write inteceptor remove readwrite_ds but no effect; if properties not add logic_db name (readwrite_ds), join query exception【unkonw datasource】;

please help how to deal this bug ,thanks

strongduanmu commented 1 year ago

If you want to use the logical database name of ShardingSphere, please add readwrite_ds in front of all tables. zhsq is an object inside Oracle, please do not specify it in SQL, because ShardingSphere cannot know what it is.

You can try

SELECT count(1) FROM readwrite_ds.T_TEMPLATE_CFG T JOIN readwrite_ds.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1'

Or

SELECT count(1) FROM T_TEMPLATE_CFG T JOIN t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1'
woshiluoshao commented 1 year ago

If you want to use the logical database name of ShardingSphere, please add readwrite_ds in front of all tables. zhsq is an object inside Oracle, please do not specify it in SQL, because ShardingSphere cannot know what it is.

You can try

SELECT count(1) FROM readwrite_ds.T_TEMPLATE_CFG T JOIN readwrite_ds.t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1'

Or

SELECT count(1) FROM T_TEMPLATE_CFG T JOIN t_dc_org_entity_info B ON T.org_code = B.org_code WHERE T.IS_VALID = '1'

this method uneffect , has exception unkonwdatasource

terrymanu commented 4 months ago

shardingsphere-jdbc-core-spring-boot-starter is deprecated and removed in 5.3.0, please try new version and use ShardingSphere Driver.