dongoewang opened a new issue, #30032:
URL: https://github.com/apache/shardingsphere/issues/30032
Table Definition
table 1
CREATE TABLE `kq_attendance_daily_202401` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`organ_id` bigint NULL DEFAULT NULL COMMENT '项目id',
`employee_id` bigint NOT NULL COMMENT '员工ID',
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门id',
`post_id` bigint NULL DEFAULT NULL COMMENT '职位id 迁移用',
`attendance_plan_id` bigint NULL DEFAULT NULL COMMENT '考勤方案ID',
`attendance_date` date NOT NULL COMMENT '日期 yyyy-mm-dd',
PRIMARY KEY (`id`) USING BTREE
) ;
CREATE TABLE `kq_attendance_daily_202402` (
`id` bigint NOT NULL AUTO_INCREMENT COMMENT 'id',
`organ_id` bigint NULL DEFAULT NULL COMMENT '项目id',
`employee_id` bigint NOT NULL COMMENT '员工ID',
`dept_id` bigint NULL DEFAULT NULL COMMENT '部门id',
`post_id` bigint NULL DEFAULT NULL COMMENT '职位id 迁移用',
`attendance_plan_id` bigint NULL DEFAULT NULL COMMENT '考勤方案ID',
`attendance_date` date NOT NULL COMMENT '日期 yyyy-mm-dd',
PRIMARY KEY (`id`) USING BTREE
) ;
table 2
CREATE TABLE `kq_employee_clock_record_202401` (
`id` bigint NOT NULL AUTO_INCREMENT,
`attendance_date` date NULL DEFAULT NULL
PRIMARY KEY (`id`) USING BTREE
) ;
CREATE TABLE `kq_employee_clock_record_202402` (
`id` bigint NOT NULL AUTO_INCREMENT,
`attendance_date` date NULL DEFAULT NULL
PRIMARY KEY (`id`) USING BTREE
) ;
sharding-config.yaml
mode:
type: Cluster
repository:
type: ZooKeeper
props:
namespace: generator_demo
server-lists: localhost:2181
dataSources:
# ds_0:
# dataSourceClassName: com.zaxxer.hikari.HikariDataSource
# driverClassName: com.mysql.jdbc.Driver
# jdbcUrl:
jdbc:mysql://localhost:3306/demo_ds_0?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
# username: root
# password: 123456
# maxPoolSize: 10
datasource:
jdbcUrl:
jdbc:mysql://localhost:3306/yaj_oa_dev?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true
username: root
password: 123456
# dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
rules:
- !SHARDING
tables:
kq_employee_clock_record:
actualDataNodes: datasource.kq_employee_clock_record
tableStrategy:
standard:
shardingColumn: attendance_date
shardingAlgorithmName: clockRecordAutoCustom
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake_generator
kq_attendance_daily:
actualDataNodes: datasource.kq_attendance_daily
tableStrategy:
standard:
shardingColumn: attendance_date
shardingAlgorithmName: clockRecordAutoCustom
keyGenerateStrategy:
column: id
keyGeneratorName: snowflake_generator
bindingTables:
- kq_employee_clock_record, kq_attendance_daily
shardingAlgorithms:
clockRecordAutoCustom:
type: CLASS_BASED
props:
strategy: standard
algorithmClassName:
com.younike.oa.shardingAlgorithm.ClockRecordAutoShardingAlgorithm
keyGenerators:
snowflake_generator:
type: SNOWFLAKE
- !SINGLE
tables:
- datasource.*
props:
sql-show: true
java file：ClockRecordAutoShardingAlgorithm
@Log4j2
@Data
public class ClockRecordAutoShardingAlgorithm extends
ShardingCommonAlgorithm implements StandardShardingAlgorithm<Date>,
ShardingAutoTableAlgorithm {
private Properties props;
@Override
public void init(final Properties props) {
this.props = props;
}
/**
* 精确分片，节点配置表名
* @param availableTargetNames available data sources or table
names
* @param preciseShardingValue sharding value
* @return
*/
@Override
public String doSharding(final Collection<String>
availableTargetNames, final PreciseShardingValue<Date> preciseShardingValue) {
String logicTableName =
preciseShardingValue.getLogicTableName();
/// 打印分片信息
log.info("精确分片，节点配置表名：{}", availableTargetNames);
Date date = preciseShardingValue.getValue();
String datePath = DateUtils.formatDateByStyle(date,
DatePattern.CN_DATE_BASIC_STYLE6.getDatePattern());
String resultTableName = logicTableName + "_" + datePath;
// 检查是否需要初始化
initializeCacheTables(availableTargetNames,
logicTableName);
return getShardingTableAndCreate(logicTableName,
resultTableName, availableTargetNames);
}
/**
* 范围分片，节点配置表名
* @param availableTargetNames available data sources or table
names
* @param rangeShardingValue sharding value
* @return
*/
@Override
public Collection<String> doSharding(final Collection<String>
availableTargetNames, final RangeShardingValue<Date> rangeShardingValue) {
String logicTableName =
rangeShardingValue.getLogicTableName();
/// 打印分片信息
log.info("【INFO】范围分片，节点配置表名：{}", availableTargetNames);
// between and 的起始值
Range<Date> valueRange =
rangeShardingValue.getValueRange();
boolean hasLowerBound = valueRange.hasLowerBound();
boolean hasUpperBound = valueRange.hasUpperBound();
// 获取最大值和最小值
Timestamp min = null;
Timestamp max = null;
if(hasLowerBound){
Object stringTemp = valueRange.lowerEndpoint();
min = Timestamp.valueOf(stringTemp.toString());
// min = stringTemp;
} else {
min = getLowerEndpoint(availableTargetNames);
}
if(hasUpperBound){
// max
=Timestamp.valueOf(valueRange.upperEndpoint().toString());
Object stringTemp = valueRange.upperEndpoint();
max =
Timestamp.valueOf(stringTemp.toString().substring(0,10)+" 00:00:00");
} else {
max = getUpperEndpoint(availableTargetNames);
}
// 循环计算分表范围
Set<String> resultTableNames = new LinkedHashSet<>();
while (min.before(max) || min.equals(max)) {
LocalDateTime localDateTimeMin = min.toLocalDateTime();
String tableName = logicTableName + TABLE_SPLIT_SYMBOL
+ localDateTimeMin.format(TABLE_SHARD_TIME_FORMATTER);
resultTableNames.add(tableName);
min = Timestamp.valueOf(localDateTimeMin.plusDays(1));
}
initializeCacheTables(availableTargetNames,
logicTableName);
Set<String> shardingTablesAndCreate =
getShardingTablesAndCreate(logicTableName, resultTableNames,
availableTargetNames);
return shardingTablesAndCreate;
// return availableTargetNames;
}
@Override
public String getType() {
// return "AUTO_CUSTOM";
return "CLASS_BASED";
}
}
runtime error information
org.springframework.dao.DataIntegrityViolationException:
### Error querying database. Cause: java.sql.SQLException: Actual
table `datasource.kq_employee_clock_record_202401` is not in table rule
configuration.
### The error may exist in file
[D:\dongzh\workspace\UXianXin\Yaj\yaj-business\yaj-oa\target\classes\mapper\kq\EmployeeClockRecordMapper.xml]
### The error may involve
com.younike.oa.mapper.kq.EmployeeClockRecordMapper.selectOutsideByPage-Inline
### The error occurred while setting parameters
### SQL: SELECT COUNT(*) FROM kq_employee_clock_record a LEFT JOIN
hr_employee e ON a.employee_id = e.id LEFT JOIN hr_organ_position p ON
e.post_id = p.id LEFT JOIN kq_attendance_daily kad ON kad.id = (SELECT id FROM
kq_attendance_daily WHERE delete_flag = 0 AND employee_id = a.employee_id AND
attendance_date = date_format(a.clock_time, '%Y-%m-%d') LIMIT 1) WHERE a.type =
2 AND a.flow_flag = 1 AND (kad.id IS NULL OR e.is_overtime != '1' OR
kad.work_type != '0') AND ((a.clock_time >= ? AND a.clock_time <= ?)) AND
((a.attendance_date >= ? AND a.attendance_date <= ?))
### Cause: java.sql.SQLException: Actual table
`datasource.kq_employee_clock_record_202401` is not in table rule configuration.
; Actual table `datasource.kq_employee_clock_record_202401` is not
in table rule configuration.; nested exception is java.sql.SQLException: Actual
table `datasource.kq_employee_clock_record_202401` is not in table rule
configuration.
at
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
at
org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:91)
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.executeForIPage(MybatisMapperMethod.java:121)
at
com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:85)
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.$Proxy282.selectOutsideByPage(Unknown Source)
at
com.younike.oa.impl.kq.EmployeeClockRecordServiceImpl.outsidePage(EmployeeClockRecordServiceImpl.java:278)
at
com.younike.oa.impl.kq.EmployeeClockRecordServiceImpl$$FastClassBySpringCGLIB$$44c5fc93.invoke(<generated>)
at
org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at
io.seata.spring.annotation.GlobalTransactionalInterceptor.invoke(GlobalTransactionalInterceptor.java:128)
at
org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
at
org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
at
org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
at
com.younike.oa.impl.kq.EmployeeClockRecordServiceImpl$$EnhancerBySpringCGLIB$$ef2e0e6.outsidePage(<generated>)
at
com.younike.oa.ShardingTableTest.listTestOutsidePage(ShardingTableTest.java:110)
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:686)
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$6(TestMethodTestDescriptor.java:212)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.invokeTestMethod(TestMethodTestDescriptor.java:208)
at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:137)
at
org.junit.jupiter.engine.descriptor.TestMethodTestDescriptor.execute(TestMethodTestDescriptor.java:71)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:135)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at
org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at
org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at
org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at java.util.ArrayList.forEach(ArrayList.java:1259)
at
org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.invokeAll(SameThreadHierarchicalTestExecutorService.java:38)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$5(NodeTestTask.java:139)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$7(NodeTestTask.java:125)
at
org.junit.platform.engine.support.hierarchical.Node.around(Node.java:135)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.lambda$executeRecursively$8(NodeTestTask.java:123)
at
org.junit.platform.engine.support.hierarchical.ThrowableCollector.execute(ThrowableCollector.java:73)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.executeRecursively(NodeTestTask.java:122)
at
org.junit.platform.engine.support.hierarchical.NodeTestTask.execute(NodeTestTask.java:80)
at
org.junit.platform.engine.support.hierarchical.SameThreadHierarchicalTestExecutorService.submit(SameThreadHierarchicalTestExecutorService.java:32)
at
org.junit.platform.engine.support.hierarchical.HierarchicalTestExecutor.execute(HierarchicalTestExecutor.java:57)
at
org.junit.platform.engine.support.hierarchical.HierarchicalTestEngine.execute(HierarchicalTestEngine.java:51)
at
org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:248)
at
org.junit.platform.launcher.core.DefaultLauncher.lambda$execute$5(DefaultLauncher.java:211)
at
org.junit.platform.launcher.core.DefaultLauncher.withInterceptedStreams(DefaultLauncher.java:226)
at
org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:199)
at
org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:132)
at
com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at
com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at
com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at
com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
at
com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
Caused by: java.sql.SQLException: Actual table
`datasource.kq_employee_clock_record_202401` is not in table rule configuration.
at
org.apache.shardingsphere.infra.util.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:62)
at
org.apache.shardingsphere.dialect.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:51)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:416)
at
com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
at
com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.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
org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:59)
at com.sun.proxy.$Proxy187.execute(Unknown Source)
at
org.apache.ibatis.executor.statement.PreparedStatementHandler.query(PreparedStatementHandler.java:64)
at
org.apache.ibatis.executor.statement.RoutingStatementHandler.query(RoutingStatementHandler.java:79)
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.apache.ibatis.plugin.Plugin.invoke(Plugin.java:64)
at com.sun.proxy.$Proxy179.query(Unknown Source)
at
org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:63)
at
org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
at
org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
at
org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109)
at
com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor.willDoQuery(PaginationInnerInterceptor.java:141)
at
com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:75)
at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:62)
at com.sun.proxy.$Proxy178.query(Unknown Source)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145)
at
org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140)
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.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427)
... 84 more
--
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:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
