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 2020/07/06 03:22:59 UTC
[GitHub] [shardingsphere] lcx1989210 opened a new issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
lcx1989210 opened a new issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269
When i run a complex nested-query sql at shardingsphere 4.1.1, it occur exception of 'Unknown error 1146' !
Mysql Server version : 5.7.22;
shardingsphere version: 4.1.1
The maven info as follow:
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-core-common</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-orchestration-spring-namespace</artifactId>
<version>4.1.1</version>
</dependency>
The full exception stack is :
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
### The error may exist in file [/opt/mappers/resource/OrderMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select count(o1.id) total FROM ( select o.id from `order` as o join balance as b on o.id = b.order_id WHERE o.user_id = ? and b.user_id = ? and o.user_type = ? and b.user_type = ? and o.status in (15,21) and o.status in (15,21) and ( b.end_at <= unix_timestamp(current_timestamp(3))*1000 or b.refund_at > 0 or b.amount_left = 0) group by o.id ) as o1
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:230)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447)
at com.sun.proxy.$Proxy80.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:167)
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82)
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59)
at com.sun.proxy.$Proxy84.getOrdersCount(Unknown Source)
at resource.biz.newbiz.impl.OrderServiceBizImpl.getOrdersByPage(OrderServiceBizImpl.java:399)
at resource.biz.newbiz.impl.OrderServiceBizImpl$$FastClassBySpringCGLIB$$ce562327.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
at resource.biz.newbiz.impl.OrderServiceBizImpl$$EnhancerBySpringCGLIB$$ab732101.getOrdersByPage(<generated>)
at resource.components.OrderService.getOrders(OrderService.java:466)
at resource.components.OrderService$$FastClassBySpringCGLIB$$ab928388.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:736)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:84)
at resource.aop.ValidateAspect.around(ValidateAspect.java:62)
at sun.reflect.GeneratedMethodAccessor150.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:627)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:616)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:671)
at resource.components.OrderService$$EnhancerBySpringCGLIB$$44078b44.getOrders(<generated>)
at OrderServiceProxyStub1593674051850.getOrders(OrderServiceProxyStub1593674051850.java)
at OrderServiceProxyStub1593674051850.invoke(OrderServiceProxyStub1593674051850.java)
at server.core.proxy.InvokerBase.doInvoke(InvokerBase.java:110)
at server.core.proxy.AsyncInvokerHandle$1.run(AsyncInvokerHandle.java:101)
at server.util.expandasync.AsyncWorker.execTimeoutTask(AsyncWorker.java:139)
at server.util.expandasync.AsyncWorker.access$200(AsyncWorker.java:14)
at server.util.expandasync.AsyncWorker$executeThread.run(AsyncWorker.java:165)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:745)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:422)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
at com.mysql.jdbc.Util.getInstance(Util.java:408)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:943)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3973)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2527)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2490)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
at com.mysql.jdbc.PreparedStatement.execute(PreparedStatement.java:1197)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.execute(DelegatingPreparedStatement.java:94)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:62)
at org.apache.shardingsphere.shardingjdbc.executor.SQLExecuteCallbackFactory$2.executeSQL(SQLExecuteCallbackFactory.java:58)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute0(SQLExecuteCallback.java:82)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteCallback.execute(SQLExecuteCallback.java:58)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.syncExecute(ExecutorEngine.java:97)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.parallelExecute(ExecutorEngine.java:93)
at org.apache.shardingsphere.underlying.executor.engine.ExecutorEngine.execute(ExecutorEngine.java:76)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:68)
at org.apache.shardingsphere.sharding.execute.sql.execute.SQLExecuteTemplate.execute(SQLExecuteTemplate.java:51)
at org.apache.shardingsphere.shardingjdbc.executor.AbstractStatementExecutor.executeCallback(AbstractStatementExecutor.java:129)
at org.apache.shardingsphere.shardingjdbc.executor.PreparedStatementExecutor.execute(PreparedStatementExecutor.java:148)
at org.apache.shardingsphere.shardingjdbc.jdbc.core.statement.ShardingPreparedStatement.execute(ShardingPreparedStatement.java:145)
at sun.reflect.GeneratedMethodAccessor59.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy103.execute(Unknown Source)
at org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:63)
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:324)
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:83)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:148)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:141)
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:77)
at sun.reflect.GeneratedMethodAccessor115.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:497)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:434)
... 42 more
The sharding sql is :
ShardingSphere-SQL 5508e779-ecdc-46b0-a02b-2ec54ed35786 - Actual SQL: resourceReadDataSource_1 ::: select count(o1.id) total FROM (
select o.id
from `order` as o join balance as b on o.id = b.order_id
WHERE o.user_id = ?
and b.user_id = ?
and o.user_type = ?
and b.user_type = ?
and o.status in (15,21)
and o.status in (15,21)
and ( b.end_at <= unix_timestamp(current_timestamp(3))*1000 or b.refund_at > 0 or b.amount_left = 0)
group by o.id
) as o1 ::: [30802210603265, 30802210603265, 2, 2]
Why it not support nest-queries at version 4.1.1? What can I do to avoid this exception?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654035375
@lcx1989210 can you check if the rewritten sql is correct? if it is uncorrect, plz show the correct rewritten sql.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lcx1989210 commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654786047
> @lcx1989210 Maybe it is the same to #6228 , can you confirm that？
Yeah,I looked through it carefully, and these two are the same problem. And I've already Feedback on this issue by wechat-group.Thanks a lot.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu removed a comment on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
jingshanglu removed a comment on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654579355
@lcx1989210 Ok, How do i contact you?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu edited a comment on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
jingshanglu edited a comment on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-656557431
Ok,now it has been fixed on master branch, new version will fix it.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lcx1989210 commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654072518
I've simplified the execution of SQL like this :
select count(*) FROM (select o.id from `order` as o join balance as b on o.id = b.order_id WHERE o.user_id = ? and b.user_id = ? and o.user_type = ? and b.user_type = ? ) tbl
And it still occur exception of 'Unknown error 1146' !
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
### The error may exist in file [D:\project\TC_project\resourceService\service\target\classes\config\mappers\resource\OrderMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: select count(*) FROM (select o.id from `order` as o join balance as b on o.id = b.order_id WHERE o.user_id = ? and b.user_id = ? and o.user_type = ? and b.user_type = ? ) tbl
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown error 1146
at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:230) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.20.RELEASE.jar:4.3.20.RELEASE]
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:75) ~[mybatis-spring-1.3.0.jar:1.3.0]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:447) ~[mybatis-spring-1.3.0.jar:1.3.0]
at com.sun.proxy.$Proxy38.selectOne(Unknown Source) ~[?:?]
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:167) ~[mybatis-spring-1.3.0.jar:1.3.0]
at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:82) ~[mybatis-3.4.2.jar:3.4.2]
at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:59) ~[mybatis-3.4.2.jar:3.4.2]
at com.sun.proxy.$Proxy39.getOrdersCount(Unknown Source) ~[?:?]
at newbiz.impl.OrderServiceBizImpl.getOrdersByPage(OrderServiceBizImpl.java:399) ~[classes/:?]
....
The sql just have Logic SQL and Actual SQL，but have no rewritten sql. Colud you solve it ?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654653918
@lcx1989210 Maybe it is the same to #6228 , can you confirm that？
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lcx1989210 commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654056398
What do you mean the rewritten sql? This sql is execute normal at shardingsphere 3.1.1.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-656557431
Ok,now it has been fixed.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lcx1989210 commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
lcx1989210 commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654077678
So can we Communicate through Wechat group? Issue on git is too inefficient.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jingshanglu commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
jingshanglu commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654579355
@lcx1989210 Ok, How do i contact you?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] menghaoranss commented on issue #6269: Does shardingsphere not support nested-queries at version 4.1.1?
Posted by GitBox <gi...@apache.org>.
menghaoranss commented on issue #6269:
URL: https://github.com/apache/shardingsphere/issues/6269#issuecomment-654035020
@jingshanglu please follow up.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org