springboot2.7.x 整合 sharding 的问题
springboot版本 2.7.18
shardingsphere-jdbc-core-spring-boot-starter 版本 5.2.1
snakeyaml版本 1.33
druid 版本 1.2.18
配置信息如下
spring:
datasource:
druid:
# 初始化大小
initial-size: 1
# 最小
min-idle: 1
# 最大
max-active: 5
# 配置获取连接等待超时的时间
max-wait: 60000
# 配置间隔多久才进行一次检测，检测需要关闭的空闲连接，单位是毫秒
time-between-eviction-runs-millis: 3000
# 配置一个连接在池中最小生存的时间，单位是毫秒
min-evictable-idle-time-millis: 30000
# 用来检测连接是否有效的sql
validation-query: select 'x'
# 建议配置为true，不影响性能，并且保证安全性。申请连接的时候检测，
# 如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效。
test-while-idle: true
# 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能。
test-on-borrow: false
# 归还连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
test-on-return: false
# 打开PSCache
pool-prepared-statements: true
# 指定每个连接上PSCache的大小
max-pool-prepared-statement-per-connection-size: 20
# druid监控配置
# 配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
filters: stat,wall,slf4j
# 通过connectProperties属性来打开mergeSql功能；慢SQL记录
connection-properties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
# 合并多个DruidDataSource的监控数据
# useGlobalDataSourceStat: true
# 是否启用StatFilter默认值true
web-stat-filter:
# 是否开启拦截统计
enabled: true
# 拦截所有
url-pattern: /*
# 排除不统计哪些URL
exclusions: "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*"
# session统计功能
session-stat-enable: true
# 最大session数
session-stat-max-count: 100000
# 你可以配置principalSessionName，使得druid能够知道当前的session的用户是谁
principal-session-name: ${system-default.username}
# 你可以配置principalSessionName，使得druid能够知道当前的cookie的用户是谁
principal-cookie-name: ${system-default.username}
# 置profileEnable能够监控单个url调用的sql列表。
profile-enable: true
# StatViewServlet配置，说明请参考Druid Wiki，配置_StatViewServlet配置
stat-view-servlet:
# 启用
enabled: true
# 视图
url-pattern: /druid/*
# 禁用HTML页面上的“Reset All”功能, 禁止手动重置监控数据
reset-enable: false
# druid 管理中心用户名
login-username: ${system-default.username}
# druid 管理中心密码
login-password: ${system-default.password}
# IP白名单(没有配置或者为空，则允许所有访问)
allow:
# IP黑名单 (存在共同时，deny优先于allow)
deny:
# Spring监控，对内部各接口调用的监控
aop-patterns: ${project-group-id}.service.*.*,${project-group-id}.dao.*.*,${project-group-id}.client.controller.*.*,classpath:mybatis.mapper.*.*
# 配置wall filter
filter:
wall:
enabled: true
db-type: mysql
# 对被认为是攻击的SQL进行LOG.error输出
log-violation: true
# 对被认为是攻击的SQL抛出SQLExcepton
throw-exception: true
# https://github.com/alibaba/druid/wiki/配置-wallfilter
config:
# 是否允许执行Alter Table语句
alter-table-allow: false
# truncate语句是危险，缺省打开，若需要自行关闭
truncate-allow: false
# 是否允许修改表
drop-table-allow: false
# 是否允许非以上基本语句的其他语句，缺省关闭，通过这个选项就能够屏蔽DDL。
none-base-statement-allow: false
# 检查UPDATE语句是否无where条件，这是有风险的，但不是SQL注入类型的风险
update-where-none-check: true
# SELECT ... INTO OUTFILE 是否允许，这个是mysql注入攻击的常见手段，缺省是禁止的
select-into-outfile-allow: false
# 是否允许调用Connection.getMetadata方法，这个方法调用会暴露数据库的表信息
metadata-allow: true
# 是否允许调用Connection/Statement/ResultSet的isWrapFor和unwrap方法，
# 这两个方法调用，使得有办法拿到原生驱动的对象，绕过WallFilter的检测直接执行SQL。
wrapAllow: false
# 这个选项是防御程序通过调用select *获得数据表的结构信息。是否允许执行SELECT * FROM T这样的语句。
# 如果设置为false，不允许执行select * from t，但select * from (select id, name from t) a。
select-all-column-allow: false
# Connection.getMetadata
do-privileged-allow: false
multi-statement-allow: true
shardingsphere:
# 打印日志
props:
sql:
show: true
datasource:
enabled: true
# 数据库名称
names: master,slave1,slave2
master:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.0.215:8001/test_1?serverTimezone=Asia/Shanghai&useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useAffectedRows=true
username: test
password: 321321
slave1:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.0.215:8001/test_2?serverTimezone=Asia/Shanghai&useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useAffectedRows=true
username: test
password: 321321
slave2:
type: com.alibaba.druid.pool.DruidDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
jdbc-url: jdbc:mysql://192.168.0.215:8001/test_3?serverTimezone=Asia/Shanghai&useUnicode=true&allowMultiQueries=true&characterEncoding=UTF-8&useAffectedRows=true
username: test
password: 321321
# 读写策略
masterslave:
load-balance-algorithm-type: round_robin
name: ms
master-data-source-name: master
slave-data-source-names: slave1,slave2
启动报错信息如下
org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SYS_APP" not found (this database is empty); SQL statement:
SELECT
COUNT(0)
FROM `sys_app`
WHERE delete_status = 0
AND `name` = ? [42104-214]
### The error may exist in file [H:\eclipse-workspace\teaching-project\teaching-platform\integrated-modules\mysql-dao\target\classes\mybatis\mapper\system\SysAppMapper.xml]
### The error may involve com.teaching.platform.dao.system.SysAppDao.findTotalRow
### The error occurred while executing a query
### SQL: SELECT COUNT(0) FROM `sys_app` WHERE delete_status = 0 AND `name` = ?
### Cause: org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SYS_APP" not found (this database is empty); SQL statement:
SELECT
COUNT(0)
FROM `sys_app`
WHERE delete_status = 0
AND `name` = ? [42104-214]
; bad SQL grammar []; nested exception is org.h2.jdbc.JdbcSQLSyntaxErrorException: Table "SYS_APP" not found (this database is empty); SQL statement:
SELECT
COUNT(0)
FROM `sys_app`
WHERE delete_status = 0
AND `name` = ? [42104-214]
at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:93)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73)
at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:82)
at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
at com.sun.proxy.$Proxy130.selectOne(Unknown Source)
at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:160)
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:87)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:149)
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:90)
at com.sun.proxy.$Proxy169.findTotalRow(Unknown Source)
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.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:344)
at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:198)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:89)
at com.teaching.platform.back.admin.aspect.DaoParamFillAspect.doAround(DaoParamFillAspect.java:83)
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.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:634)
at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:624)
at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:72)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:97)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:241)
at com.sun.proxy.$Proxy170.findTotalRow(Unknown Source)
at com.teaching.platform.service.system.extend.impl.SysAppServiceExtendImpl.findOne(SysAppServiceExtendImpl.java:58)
at com.teaching.platform.service.system.extend.impl.SysAppServiceExtendImpl$$FastClassBySpringCGLIB$$cb0a92c2.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
at com.alibaba.druid.support.spring.stat.DruidStatInterceptor.invoke(DruidStatInterceptor.java:70)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)
at com.teaching.platform.service.system.extend.impl.SysAppServiceExtendImpl$$EnhancerBySpringCGLIB$$1fc2fcaf.findOne(<generated>)
at com.teaching.platform.back.admin.listener.StartListener.intiSysApp(StartListener.java:1069)
at com.teaching.platform.back.admin.listener.StartListener.onApplicationEvent(StartListener.java:222)
at com.teaching.platform.back.admin.listener.StartListener.onApplicationEvent(StartListener.java:1)
at com.teaching.platform.back.admin.listener.StartListener$$FastClassBySpringCGLIB$$7859671b.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:792)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:762)
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:762)
at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:707)
at com.teaching.platform.back.admin.listener.StartListener$$EnhancerBySpringCGLIB$$96dc4914.onApplicationEvent(<generated>)
at org.springframework.context.event.SimpleApplicationEventMulticaster.doInvokeListener(SimpleApplicationEventMulticaster.java:178)
at org.springframework.context.event.SimpleApplicationEventMulticaster.invokeListener(SimpleApplicationEventMulticaster.java:171)
at org.springframework.context.event.SimpleApplicationEventMulticaster.multicastEvent(SimpleApplicationEventMulticaster.java:145)
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:429)
at org.springframework.context.support.AbstractApplicationContext.publishEvent(AbstractApplicationContext.java:386)
at org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:949)
at org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:594)
at org.springframework.boot.web.servlet.context.ServletWebServerApplicationContext.refresh(ServletWebServerApplicationContext.java:147)
at org.springframework.boot.SpringApplication.refresh(SpringApplication.java:732)
at org.springframework.boot.SpringApplication.refreshContext(SpringApplication.java:409)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:308)
at com.teaching.platform.back.admin.PlatformBackAdminService.main(PlatformBackAdminService.java:38)
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.springframework.boot.devtools.restart.RestartLauncher.run(RestartLauncher.java:50)
数据库表真实存在
报错输出的sql拿去执行，结果如下
增加分库分表策略后，配置如下
# 分库配置,默认分库策略
rules:
sharding:
default-database-strategy:
standard.sharding-column: sys_user_id
standard.sharding-algorithm-name:
# 分表配置
# user表配置
tables:
sys_user:
actual-data-nodes: db$->{0..1}.sys_user_$->{0..9}
table-strategy:
standard:
sharding-column: user_id
sharding-algorithm-name:
# 不分库分表配置规则
sys_app:
actual-data-nodes: master.sys_app
table-strategy:
inline:
sharding-column: id
algorithm-expression: sys_app
报错如下
2024-07-05 20:05:21.258[0;39m [31m[ ERROR ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.s.b.web.embedded.tomcat.TomcatStarter [0;39m 61 [2m:[0;39m Error starting Tomcat context. Exception: org.springframework.beans.factory.UnsatisfiedDependencyException. Message: Error creating bean with name 'webMvcMetricsFilter' defined in class path resource [org/springframework/boot/actuate/autoconfigure/metrics/web/servlet/WebMvcMetricsAutoConfiguration.class]: Unsatisfied dependency expressed through method 'webMvcMetricsFilter' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'simpleMeterRegistry' defined in class path resource [org/springframework/boot/actuate/autoconfigure/metrics/export/simple/SimpleMetricsExportAutoConfiguration.class]: Initialization of bean failed; nested exception is org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'dataSourcePoolMetadataMeterBinder' defined in class path resource [org/springframework/boot/actuate/autoconfigure/metrics/jdbc/DataSourcePoolMetricsAutoConfiguration$DataSourcePoolMetadataMetricsConfiguration.class]: Unsatisfied dependency expressed through method 'dataSourcePoolMetadataMeterBinder' parameter 0; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'shardingSphereDataSource' defined in class path resource [org/apache/shardingsphere/spring/boot/ShardingSphereAutoConfiguration.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method 'shardingSphereDataSource' threw exception; nested exception is java.lang.IllegalArgumentException: Type is required.
[2m2024-07-05 20:05:21.303[0;39m [32m[ INFO ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.apache.catalina.core.StandardService [0;39m 173 [2m:[0;39m Stopping service [Tomcat]
[2m2024-07-05 20:05:21.307[0;39m [33m[ WARN ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.a.c.loader.WebappClassLoaderBase [0;39m 173 [2m:[0;39m The web application [ROOT] appears to have started a thread named [Druid-ConnectionPool-Create-1401391801] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.park(LockSupport.java:175)
java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.await(AbstractQueuedSynchronizer.java:2039)
com.alibaba.druid.pool.DruidDataSource$CreateConnectionThread.run(DruidDataSource.java:2917)
[2m2024-07-05 20:05:21.307[0;39m [33m[ WARN ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.a.c.loader.WebappClassLoaderBase [0;39m 173 [2m:[0;39m The web application [ROOT] appears to have started a thread named [Druid-ConnectionPool-Destroy-1401391801] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
java.lang.Thread.sleep(Native Method)
com.alibaba.druid.pool.DruidDataSource$DestroyConnectionThread.run(DruidDataSource.java:3016)
[2m2024-07-05 20:05:21.307[0;39m [33m[ WARN ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.a.c.loader.WebappClassLoaderBase [0;39m 173 [2m:[0;39m The web application [ROOT] appears to have started a thread named [HikariPool-1 housekeeper] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:1093)
java.util.concurrent.ScheduledThreadPoolExecutor$DelayedWorkQueue.take(ScheduledThreadPoolExecutor.java:809)
java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1067)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
java.lang.Thread.run(Thread.java:745)
[2m2024-07-05 20:05:21.308[0;39m [33m[ WARN ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.a.c.loader.WebappClassLoaderBase [0;39m 173 [2m:[0;39m The web application [ROOT] appears to have started a thread named [HikariPool-1 connection adder] but has failed to stop it. This is very likely to create a memory leak. Stack trace of thread:
sun.misc.Unsafe.park(Native Method)
java.util.concurrent.locks.LockSupport.parkNanos(LockSupport.java:215)
java.util.concurrent.locks.AbstractQueuedSynchronizer$ConditionObject.awaitNanos(AbstractQueuedSynchronizer.java:2078)
java.util.concurrent.LinkedBlockingQueue.poll(LinkedBlockingQueue.java:467)
java.util.concurrent.ThreadPoolExecutor.getTask(ThreadPoolExecutor.java:1066)
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1127)
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
java.lang.Thread.run(Thread.java:745)
[2m2024-07-05 20:05:21.308[0;39m [32m[ INFO ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mcom.alibaba.druid.pool.DruidDataSource [0;39m 2170 [2m:[0;39m {dataSource-1} closing ...
[2m2024-07-05 20:05:21.311[0;39m [32m[ INFO ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mcom.alibaba.druid.pool.DruidDataSource [0;39m 2243 [2m:[0;39m {dataSource-1} closed
[2m2024-07-05 20:05:21.368[0;39m [31m[ ERROR ][0;39m [35m[platform-back-admin-service,,,,][48516][0;39m [2m[restartedMain][0;39m [36mo.s.boot.SpringApplication [0;39m 818 [2m:[0;39m Application run failed
求解决，报酬可谈
