You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2023/01/10 14:58:34 UTC
[GitHub] [shardingsphere] woshiluoshao opened a new issue, #23482: SQLSyntaxErrorException: ORA-00905: 缺失关键字
woshiluoshao opened a new issue, #23482:
URL: https://github.com/apache/shardingsphere/issues/23482
### 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(<generated>)
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(<generated>)
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)
Before report a bug, make sure you have:
- Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues).
- Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview).
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?
apache-shardingsphere-5.2.1
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC
### Expected behavior
SQL execute success
### Actual behavior
SQL execute fail
### Reason analyze (If you can)
when simply oracle table search ,it success, but table A JOIN table B ，it fail ; for example [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] this Sql execute success; but [SELECT count(1) FROM T_TEMPLATE_CFG T JOIN zhsq.t_dc_org_entity_info B ON T.org_code = B.org_code] it execute fail , throw exception [### Cause: java.sql.SQLSyntaxErrorException: ORA-00905: 缺失关键字]
### 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
<!-- 引入shardingsphere,注意这个starter不能和数据源启动的starter混用 -->
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc-core-spring-boot-starter</artifactId>
<version>${shardingsphere.version}</version>
<exclusions>
<exclusion>
<artifactId>guava</artifactId>
<groupId>com.google.guava</groupId>
</exclusion>
<exclusion>
<artifactId>snakeyaml</artifactId>
<groupId>org.yaml</groupId>
</exclusion>
</exclusions>
</dependency>
<!-- 依赖版本为1.33,这个版本是shardingsphere所依赖的 -->
<dependency>
<groupId>org.yaml</groupId>
<artifactId>snakeyaml</artifactId>
<version>1.33</version>
</dependency>
<!-- <!– 依赖版本为30.0-jre,这个版本是shardingsphere所依赖的 –>-->
<!-- <dependency>-->
<!-- <groupId>com.google.guava</groupId>-->
<!-- <artifactId>guava</artifactId>-->
<!-- <version>30.0-jre</version>-->
<!-- </dependency>-->
<!-- oracle驱动版本升级,如果旧的oracle是groupId=oracle,artifactId=ojdbc通常是需要替换的 -->
<dependency>
<groupId>com.oracle.ojdbc</groupId>
<artifactId>ojdbc8</artifactId>
<version>19.3.0.0</version>
</dependency>
<dependency>
<groupId>com.oracle.ojdbc</groupId>
<artifactId>orai18n</artifactId>
<version>19.3.0.0</version>
</dependency>
3.SQl
**execute success Sql**
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'
</select>
**execute fial SQL**
<select id="selectList2" resultType="long">
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'
</select>
### Example codes for reproduce this issue (such as a github link).
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] woshiluoshao closed issue #23482: SQLSyntaxErrorException: ORA-00905: `Missing key word`
Posted by GitBox <gi...@apache.org>.
woshiluoshao closed issue #23482: SQLSyntaxErrorException: ORA-00905: `Missing key word`
URL: https://github.com/apache/shardingsphere/issues/23482
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] woshiluoshao commented on issue #23482: SQLSyntaxErrorException: ORA-00905: `Missing key word`
Posted by GitBox <gi...@apache.org>.
woshiluoshao commented on issue #23482:
URL: https://github.com/apache/shardingsphere/issues/23482#issuecomment-1378092813
is shardingsphere5.2.1 support oracle join query? shardingshere + postgre database can execute success, but shardinsphere + oracle datab execute fail , why?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] woshiluoshao commented on issue #23482: SQLSyntaxErrorException: ORA-00905: 缺失关键字
Posted by GitBox <gi...@apache.org>.
woshiluoshao commented on issue #23482:
URL: https://github.com/apache/shardingsphere/issues/23482#issuecomment-1377405532
same properties postgre database can execute success ,but oracle database execute fail,
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #23482: SQLSyntaxErrorException: ORA-00905: `Missing key word`
Posted by GitBox <gi...@apache.org>.
RaigorJiang commented on issue #23482:
URL: https://github.com/apache/shardingsphere/issues/23482#issuecomment-1377478159
Hi @woshiluoshao ,
Thanks for your feedback, bud from the exception stack you provided, there is nothing related to ShardingSphere.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org