方便更快捷的说明问题,可以按需填写(可删除)
使用环境:
springboot 2.1.5
shardingsphere 4.1.1
mybatis-plus 3.5.1
场景、问题:
[2022-05-17 17:23:07.471 maxrocky-cloud-service-transfer ERROR ,, http-nio-5724-exec-5 com.maxrocky.common.tools.exception.GlobalExceptionHandler {"appName":"maxrocky-cloud-service-transfer","code":"SQL_ERR","id":"35491d9a197541fcb1a2a4c3c0b5c45d","msg":"SQL_ERROR"}
com.maxrocky.service.cornerston.impl.DataSourceTestService.saveBatch(DataSourceTestService.java:47)
com.maxrocky.controller.cornerstone.DataSourceTestController.saveBatch(DataSourceTestController.java:38)
com.maxrocky.common.tools.aop.ExampleAop.beforeCheckToken(ExampleAop.java:70)
com.maxrocky.common.tools.xss.MyXssFilter.doFilter(MyXssFilter.java:47)
[2022-05-17 17:23:07.472 maxrocky-cloud-service-transfer ERROR ,, http-nio-5724-exec-5 com.maxrocky.common.tools.exception.GlobalExceptionHandler org.springframework.jdbc.BadSqlGrammarException:
### Error updating database. Cause: java.sql.SQLSyntaxErrorException: Table 'shard_one.sys_user' doesn't exist
### The error may exist in com/maxrocky/repository/cornerstone/mapper/SysUserMapper.java (best guess)
### The error may involve com.maxrocky.repository.cornerstone.mapper.SysUserMapper.insert-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO sys_user ( name, phone ) VALUES ( ?, ? )
### Cause: java.sql.SQLSyntaxErrorException: Table 'shard_one.sys_user' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'shard_one.sys_user' doesn't exist
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:234)
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441)
com.sun.proxy.$Proxy129.insert(Unknown Source)
org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272)
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59)
com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148)
com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89)
com.sun.proxy.$Proxy139.insert(Unknown Source)
com.maxrocky.service.cornerston.impl.DataSourceTestService.saveBatch(DataSourceTestService.java:47)
com.maxrocky.service.cornerston.impl.DataSourceTestService$$FastClassBySpringCGLIB$$1.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
com.baomidou.dynamic.datasource.aop.DynamicDataSourceAnnotationInterceptor.invoke(DynamicDataSourceAnnotationInterceptor.java:50)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
com.maxrocky.service.cornerston.impl.DataSourceTestService$$EnhancerBySpringCGLIB$$1.saveBatch(<generated>)
com.maxrocky.controller.cornerstone.DataSourceTestController.saveBatch(DataSourceTestController.java:38)
com.maxrocky.controller.cornerstone.DataSourceTestController$$FastClassBySpringCGLIB$$1.invoke(<generated>)
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:749)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:88)
com.maxrocky.common.tools.aop.ExampleAop.beforeCheckToken(ExampleAop.java:70)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:644)
org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:633)
org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:70)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
org.springframework.aop.framework.adapter.MethodBeforeAdviceInterceptor.invoke(MethodBeforeAdviceInterceptor.java:56)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:93)
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:688)
com.maxrocky.controller.cornerstone.DataSourceTestController$$EnhancerBySpringCGLIB$$1.saveBatch(<generated>)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:892)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:797)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1039)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:942)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1005)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:897)
javax.servlet.http.HttpServlet.service(HttpServlet.java:634)
org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:882)
javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
com.github.xiaoymin.knife4j.spring.filter.ProductionSecurityFilter.doFilter(ProductionSecurityFilter.java:53)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
com.github.xiaoymin.knife4j.spring.filter.SecurityBasicAuthFilter.doFilter(SecurityBasicAuthFilter.java:90)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
com.maxrocky.common.tools.xss.MyXssFilter.doFilter(MyXssFilter.java:47)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:92)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:93)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:200)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:200)
org.apache.catalina.core.StandardContextValve.__invoke(StandardContextValve.java:96)
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:41002)
org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:490)
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:408)
org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:836)
org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1747)
org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
java.lang.Thread.run(Thread.java:748)
[2022-05-17 17:23:08.061 maxrocky-cloud-service-transfer INFO ,, com.alibaba.nacos.client.Worker.longPolling.fixed-192.168.1.99_8848 com.alibaba.nacos.client.config.impl.ClientWorker get changedGroupKeys:[]
[2022-05-17 17:23:37.590 maxrocky-cloud-service-transfer INFO ,, com.alibaba.nacos.client.Worker.longPolling.fixed-192.168.1.99_8848 com.alibaba.nacos.client.config.impl.ClientWorker get changedGroupKeys:[]
[2022-05-17 17:24:07.113 maxrocky-cloud-service-transfer INFO ,, com.alibaba.nacos.client.Worker.longPolling.fixed-192.168.1.99_8848 com.alibaba.nacos.client.config.impl.ClientWorker get changedGroupKeys:[]
[2022-05-17 17:24:36.641 maxrocky-cloud-service-transfer INFO ,, com.alibaba.nacos.client.Worker.longPolling.fixed-192.168.1.99_8848 com.alibaba.nacos.client.config.impl.ClientWorker get changedGroupKeys:[]
[2022-05-17 17:25:06.171 maxrocky-cloud-service-transfer INFO ,, com.alibaba.nacos.client.Worker.longPolling.fixed-192.168.1.99_8848 com.alibaba.nacos.client.config.impl.ClientWorker get changedGroupKeys:[]
已进行操作:
spring:
# shardingjdbc 配置
shardingsphere:
datasource:
names: ds0,ds1,ds2
ds0:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/shard_one?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
ds1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/shard_two?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
ds2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.jdbc.Driver
url: jdbc:mysql://localhost:3306/shard_three?allowMultiQueries=true&useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai
username: root
password: root
masterslave:
name: ms
load-balance-algorithm-type: round_robin
master-data-source-name: ds0
slave-data-source-names: ds1,ds2
sharding:
tables:
sys_user:
actualDataNodes: ds$->{0..2}.sys_user$->{0..2}
tableStrategy:
inline:
shardingColumn: id
algorithmExpression: sys_user$->{id % 2}
keyGenerator:
type: SNOWFLAKE
column: id
现状:
数据库有 shard_one,shard_two,shard_three 三个库,库里都有 sys_user0, sys_user1, sys_user2 三张表。现在这个配置,测试插入的时候报如上的错。