blade-sharding-jdbc更换达梦数据库，分页查询出现异常

Blade 未结

关注举报

 3  511

yang 剑童 2023-08-18 18:05

一、该问题的重现步骤是什么？

1.yml配置如下

rules:
sharding:
#主键生成类型
key-generators:
snowflake:
type: SNOWFLAKE
tables:
#分表代号
blade_notice:
#实际节点名称，格式为库名$->{0..n1}.表名$->{0..n2}
#blade_notice_$->{1..2} 代表有 blade_notice_1和blade_notice_2两个表
actual-data-nodes: ds1.BLADE_NOTICE_$->{1..2}
#分表策略
table-strategy:
standard:
#分表列名
sharding-column: id
#分表算法名,不可用下划线
sharding-algorithm-name: blade-notice-inline

#主键生成策略
key-generate-strategy:
#主键名
column: id
#策略算法
key-generator-name: snowflake
#分库分表算法
sharding-algorithms:
#分表算法名
blade-notice-inline:
#算法类型
type: INLINE
props:
#算法表达式
#表达式写法非常自由，以下配置仅作为参考
#blade_notice_$->{id % 2 + 1} 代表对id进行取余，根据id的基偶来指向blade_notice_1和blade_notice_2
algorithm-expression: BLADE_NOTICE_$->{id % 2 + 1}

2.查询分页出现异常

2023-08-18 17:50:59.221 INFO 24636 --- [ main] ShardingSphere-SQL : Logic SQL: SELECT COUNT(*) AS total FROM blade_notice n LEFT JOIN (SELECT * FROM blade_dict WHERE CODE = 'notice') d ON n.category = d.dict_key WHERE n.is_deleted = 0 AND n.tenant_id = ?

2023-08-18 17:50:59.221 INFO 24636 --- [ main] ShardingSphere-SQL : SQLStatement: SQL92SelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=1, parameterMarkerSegments=[ParameterMarkerExpressionSegment(startIndex=174, stopIndex=174, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty)], commentSegments=[]), projections=ProjectionsSegment(startIndex=7, stopIndex=23, projections=[AggregationProjectionSegment(startIndex=7, stopIndex=14, type=COUNT, innerExpression=(*), parameters=[], alias=Optional[total])], distinctRow=false), from=JoinTableSegment(startIndex=30, stopIndex=131, alias=Optional.empty, left=SimpleTableSegment(tableName=TableNameSegment(startIndex=30, stopIndex=41, identifier=IdentifierValue(value=blade_notice, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional[n]), joinType=LEFT, right=SubqueryTableSegment(subquery=SubquerySegment(startIndex=55, stopIndex=102, select=SQL92SelectStatement(super=SelectStatement(super=AbstractSQLStatement(parameterCount=0, parameterMarkerSegments=[], commentSegments=[]), projections=ProjectionsSegment(startIndex=63, stopIndex=63, projections=[ShorthandProjectionSegment(startIndex=63, stopIndex=63, owner=Optional.empty, alias=Optional.empty)], distinctRow=false), from=SimpleTableSegment(tableName=TableNameSegment(startIndex=70, stopIndex=79, identifier=IdentifierValue(value=blade_dict, quoteCharacter=NONE)), owner=Optional.empty, alias=Optional.empty), where=Optional[WhereSegment(startIndex=81, stopIndex=101, expr=BinaryOperationExpression(startIndex=87, stopIndex=101, left=ColumnSegment(startIndex=87, stopIndex=90, identifier=IdentifierValue(value=CODE, quoteCharacter=NONE), owner=Optional.empty), right=LiteralExpressionSegment(startIndex=94, stopIndex=101, literals=notice), operator==, text=CODE = 'notice'))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), limit=Optional.empty), subqueryType=TABLE_SUBQUERY), alias=Optional[d]), condition=BinaryOperationExpression(startIndex=109, stopIndex=131, left=ColumnSegment(startIndex=109, stopIndex=118, identifier=IdentifierValue(value=category, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=109, stopIndex=109, identifier=IdentifierValue(value=n, quoteCharacter=NONE), owner=Optional.empty)]), right=ColumnSegment(startIndex=122, stopIndex=131, identifier=IdentifierValue(value=dict_key, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=122, stopIndex=122, identifier=IdentifierValue(value=d, quoteCharacter=NONE), owner=Optional.empty)]), operator==, text=n.category = d.dict_key), using=[]), where=Optional[WhereSegment(startIndex=133, stopIndex=174, expr=BinaryOperationExpression(startIndex=139, stopIndex=174, left=BinaryOperationExpression(startIndex=139, stopIndex=154, left=ColumnSegment(startIndex=139, stopIndex=150, identifier=IdentifierValue(value=is_deleted, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=139, stopIndex=139, identifier=IdentifierValue(value=n, quoteCharacter=NONE), owner=Optional.empty)]), right=LiteralExpressionSegment(startIndex=154, stopIndex=154, literals=0), operator==, text=n.is_deleted = 0), right=BinaryOperationExpression(startIndex=160, stopIndex=174, left=ColumnSegment(startIndex=160, stopIndex=170, identifier=IdentifierValue(value=tenant_id, quoteCharacter=NONE), owner=Optional[OwnerSegment(startIndex=160, stopIndex=160, identifier=IdentifierValue(value=n, quoteCharacter=NONE), owner=Optional.empty)]), right=ParameterMarkerExpressionSegment(startIndex=174, stopIndex=174, parameterMarkerIndex=0, parameterMarkerType=QUESTION, alias=Optional.empty), operator==, text=n.tenant_id = ?), operator=AND, text=n.is_deleted = 0 AND n.tenant_id = ?))], groupBy=Optional.empty, having=Optional.empty, orderBy=Optional.empty, combine=Optional.empty), limit=Optional.empty)

2023-08-18 17:50:59.222 INFO 24636 --- [ main] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT COUNT(*) AS total FROM BLADE_NOTICE_1 n LEFT JOIN (SELECT * FROM blade_dict WHERE CODE = 'notice') d ON n.category = d.dict_key WHERE n.is_deleted = 0 AND n.tenant_id = ? ::: [000002]

2023-08-18 17:50:59.222 INFO 24636 --- [ main] ShardingSphere-SQL : Actual SQL: ds1 ::: SELECT COUNT(*) AS total FROM BLADE_NOTICE_2 n LEFT JOIN (SELECT * FROM blade_dict WHERE CODE = 'notice') d ON n.category = d.dict_key WHERE n.is_deleted = 0 AND n.tenant_id = ? ::: [000002]

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.sql.parser.exception.SQLParsingException

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:96)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)

at com.sun.proxy.$Proxy171.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.$Proxy172.selectNoticePage(Unknown Source)

at org.springblade.sharding.service.impl.NoticeServiceImpl.selectNoticePage(NoticeServiceImpl.java:42)

at org.springblade.sharding.service.impl.NoticeServiceImpl$$FastClassBySpringCGLIB$$50f0a885.invoke(<generated>)

at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)

at org.springframework.validation.beanvalidation.MethodValidationInterceptor.invoke(MethodValidationInterceptor.java:123)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)

at com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50)

at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)

at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)

at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:708)

at org.springblade.sharding.service.impl.NoticeServiceImpl$$EnhancerBySpringCGLIB$$cc6c499b.selectNoticePage(<generated>)

at org.springblade.sharding.ShardingDemoTest.contextLoads(ShardingDemoTest.java:41)

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.platform.commons.util.ReflectionUtils.invokeMethod(ReflectionUtils.java:725)

at org.junit.jupiter.engine.execution.MethodInvocation.proceed(MethodInvocation.java:60)

at org.junit.jupiter.engine.execution.InvocationInterceptorChain$ValidatingInvocation.proceed(InvocationInterceptorChain.java:131)

at org.junit.jupiter.engine.extension.TimeoutExtension.intercept(TimeoutExtension.java:149)

at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestableMethod(TimeoutExtension.java:140)

at org.junit.jupiter.engine.extension.TimeoutExtension.interceptTestMethod(TimeoutExtension.java:84)

at org.junit.jupiter.engine.execution.ExecutableInvoker$ReflectiveInterceptorCall.lambda$ofVoidMethod$0(ExecutableInvoker.java:115)

at org.junit.jupiter.engine.execution.ExecutableInvoker.lambda$invoke$0(ExecutableInvoker.java:105)

at org.junit.jupiter.engine.execution.InvocationInterceptorChain$InterceptedInvocation.proceed(InvocationInterceptorChain.java:106)

at org.junit.jupiter.engine.execution.InvocationInterceptorChain.proceed(InvocationInterceptorChain.java:64)

at org.junit.jupiter.engine.execution.InvocationInterceptorChain.chainAndInvoke(InvocationInterceptorChain.java:45)

at org.junit.jupiter.engine.execution.InvocationInterceptorChain.invoke(InvocationInterceptorChain.java:37)

at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:104)

at org.junit.jupiter.engine.execution.ExecutableInvoker.invoke(ExecutableInvoker.java:98)

at org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.lambda$invokeTestMethod$7(TestMethodTestDescriptor.java:214)