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/12/22 15:12:05 UTC

[GitHub] [shardingsphere] singlemonkey opened a new issue #8722: Cannot use LocalDateTime as a sharding key

singlemonkey opened a new issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722


   ## Bug Report
   
   When using the localdatetime type as the partition key, the data will be changed to "yyyy-MM-ddTHH:mm:ss" after toString method,and then cannot parse to LocalDateTime with the defined "datetime-pattern" is "yyyy-MM-dd HH:mm:ss".Get error log like this:
   ```
   Caused by: java.time.format.DateTimeParseException: Text '1992-08-02T08:30:11' could not be parsed at index 10
   	at java.time.format.DateTimeFormatter.parseResolved0(DateTimeFormatter.java:1949)
   	at java.time.format.DateTimeFormatter.parse(DateTimeFormatter.java:1851)
   	at java.time.LocalDateTime.parse(LocalDateTime.java:492)
   	at org.apache.shardingsphere.sharding.algorithm.sharding.datetime.IntervalShardingAlgorithm.parseDateTime(IntervalShardingAlgorithm.java:149)
   	at org.apache.shardingsphere.sharding.algorithm.sharding.datetime.IntervalShardingAlgorithm.doSharding(IntervalShardingAlgorithm.java:136)
   	at org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:77)
   	at org.apache.shardingsphere.sharding.route.strategy.type.standard.StandardShardingStrategy.doSharding(StandardShardingStrategy.java:56)
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeTables(ShardingStandardRoutingEngine.java:214)
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route0(ShardingStandardRoutingEngine.java:194)
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditionsWithCondition(ShardingStandardRoutingEngine.java:114)
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.routeByShardingConditions(ShardingStandardRoutingEngine.java:107)
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.getDataNodes(ShardingStandardRoutingEngine.java:84)
   	at org.apache.shardingsphere.sharding.route.engine.type.standard.ShardingStandardRoutingEngine.route(ShardingStandardRoutingEngine.java:69)
   	at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:70)
   	at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:55)
   	at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:59)
   	at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:57)
   	at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:52)
   	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.createExecutionContext(ShardingSpherePreparedStatement.java:266)
   	at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:198)
   	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.$Proxy79.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 com.baomidou.mybatisplus.core.executor.MybatisSimpleExecutor.doQuery(MybatisSimpleExecutor.java:69)
   	at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325)
   	at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156)
   	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:165)
   	at com.baomidou.mybatisplus.core.executor.MybatisCachingExecutor.query(MybatisCachingExecutor.java:92)
   	at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:147)
   ```
   
   ### Which version of ShardingSphere did you use?
   5.0.0
   
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
    ShardingSphere-JDBC
   
   ### parse code like this:
   
   ```
   /org/apache/shardingsphere/sharding/algorithm/sharding/datetime/IntervalShardingAlgorithm.class
   ```
   
   ```
   public Collection<String> doSharding(Collection<String> availableTargetNames, RangeShardingValue<Comparable<?>> shardingValue) {
     boolean hasStartTime = shardingValue.getValueRange().hasLowerBound();
     boolean hasEndTime = shardingValue.getValueRange().hasUpperBound();
     if (!hasStartTime && !hasEndTime) {
         return availableTargetNames;
     } else {
         LocalDateTime startTime = hasStartTime ? this.parseDateTime(shardingValue.getValueRange().lowerEndpoint().toString()) : this.dateTimeLower;
         LocalDateTime endTime = hasEndTime ? this.parseDateTime(shardingValue.getValueRange().upperEndpoint().toString()) : this.dateTimeUpper;
         LocalDateTime calculateTime = startTime;
   
         HashSet result;
         for(result = new HashSet(); !calculateTime.isAfter(endTime); calculateTime = calculateTime.plus((long)this.stepAmount, this.stepUnit)) {
             result.addAll(this.getMatchedTables(calculateTime, availableTargetNames));
         }
   
         result.addAll(this.getMatchedTables(endTime, availableTargetNames));
         return result;
     }
   }
   ```
   ### my application-sharding.yml
   ```
   
   dataSources:
     person: !!com.alibaba.druid.pool.DruidDataSource
       driverClassName: com.mysql.jdbc.Driver
       url: jdbc:mysql://localhost:3306/person?serverTimezone=UTC&useSSL=false&useUnicode=true&characterEncoding=UTF-8
       username: root
       password: password
   
   rules:
     - !SHARDING
       tables:
         persons:
           actualDataNodes: person.persons
           tableStrategy:
             standard:
               shardingColumn: data_time
               shardingAlgorithmName: range_by_year
   
       shardingAlgorithms:
         range_by_year:
           type: INTERVAL
           props:
             datetime-pattern: "yyyy-MM-dd HH:mm:ss"
             datetime-lower: "1992-08-02 00:00:00"
             sharding-suffix-pattern: "yyyyMM"
             datetime-interval-amount: 1
             datetime-interval-unit: "YEARS"
   
       keyGenerators:
         snowflake:
           type: SNOWFLAKE
           props:
             worker-id: 123
   props:
     sql-show: true
   ```


----------------------------------------------------------------
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] singlemonkey edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749907735


   > yyyy-MM-ddTHH:mm:ss
   
   I have trid but it didn't work."yyyy-MM-ddTHH:mm:ss" will throw a new error "Unknown pattern letter: T",i had  set datetime-pattern as "yyyy-MM-dd'T'HH:mm:ss",but the length of 'datetime-pattern' is 21 and the length of  sharding value(localdatetime) is 19,it will throw a "outOfRange" error here:
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   ```
   
   I have saw the other issues but still couldn't find a way to solve this problem.[#8693](https://github.com/apache/shardingsphere/issues/8722) [#8470](https://github.com/apache/shardingsphere/issues/8470) just reported the same bug and commited code to solve this bug,but is there any solution in the current version?


----------------------------------------------------------------
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] singlemonkey edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749907735


   > yyyy-MM-ddTHH:mm:ss
   
   I have trid but it didn't work."yyyy-MM-ddTHH:mm:ss" will throw a new error "Unknown pattern letter: T",i had  set datetime-pattern as "yyyy-MM-dd'T'HH:mm:ss",but the length of 'datetime-pattern' is 21 and the length of  sharding value(localdatetime) is 19,it will throw a "outOfRange" error here:
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   ```
   
   I have saw the other issues but still couldn't find a way to solve this problem.[#8693](https://github.com/apache/shardingsphere/issues/8722)[#8470](url) just reported the same bug and commited code to solve this bug,but is there any solution in the current version?


----------------------------------------------------------------
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] yu199195 edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
yu199195 edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749914360


   @singlemonkey  
   1. can you ture  yyyy-MM-ddTHH:mm:ss can not format?
   2. you can format shardingValue to yyyy-MM-dd HH:mm:ss   or other pattern?  
   3. you javabean will used date?
   
   
   @tristaZero  this code maybe refactor...
   
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   
   to
   
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value, dateTimeFormatter);
   }
   
   ```
   
   
   


----------------------------------------------------------------
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] yu199195 commented on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749914360


   @singlemonkey  
   
   1. you can format shardingValue to yyyy-MM-dd HH:mm:ss   or other pattern?  
   2. you javabean will used date?
   
   
   @tristaZero  this code maybe refactor...
   
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   
   to
   
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value, dateTimeFormatter);
   }
   
   ```
   
   
   


----------------------------------------------------------------
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] singlemonkey commented on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey commented on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749934354


   I'll use date instead


----------------------------------------------------------------
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] singlemonkey edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749907735


   > yyyy-MM-ddTHH:mm:ss
   
   I have trid but it didn't work."yyyy-MM-ddTHH:mm:ss" will throw a new error "Unknown pattern letter: T",i had  set datetime-pattern as "yyyy-MM-dd'T'HH:mm:ss",but the length of 'datetime-pattern' is 21 and the length of  sharding value(localdatetime) is 19,it will throw a "outOfRange" error here:
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   ```
   
   I have saw the other issues but still couldn't find a way to solve this problem.[#8693 ](url)[#8470](url) just reported the same bug and commited code to solve this bug,but is there any solution in the current version?


----------------------------------------------------------------
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] yu199195 edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
yu199195 edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749898171


   1992-08-02T08:30:11  from you javabean ?  can you config datetime-pattern: yyyy-MM-ddTHH:mm:ss ?


----------------------------------------------------------------
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] yu199195 commented on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749901695


    you can see this :  #8693  #8470 


----------------------------------------------------------------
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] yu199195 edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
yu199195 edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749914360


   @singlemonkey  
   1. you can format shardingValue to yyyy-MM-dd HH:mm:ss   or other pattern?  
   2. you javabean will used date?
   
   
   @tristaZero  this code maybe refactor...
   
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   
   to
   
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value, dateTimeFormatter);
   }
   
   ```
   
   
   


----------------------------------------------------------------
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] yu199195 commented on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
yu199195 commented on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749898171


   1992-08-02T08:30:11  from you javabean ?


----------------------------------------------------------------
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] singlemonkey closed issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey closed issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722


   


----------------------------------------------------------------
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] singlemonkey commented on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey commented on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749907735


   > yyyy-MM-ddTHH:mm:ss
   
   I have trid but id didn't work."yyyy-MM-ddTHH:mm:ss" will throw a new error "Unknown pattern letter: T" here,i had to set datetime-pattern as "yyyy-MM-dd'T'HH:mm:ss",but the length of 'datetime-pattern' is 21 and the length of  sharding value(localdatetime) is 19,it will throw a "outOfRange" error here:
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   ```
   
   I have saw the other issues but still couldn't find a way to solve this problem.[#8693 ](url)[#8470](url) just reported the same bug and commited code to solve this bug,but is there any solution in the current version?


----------------------------------------------------------------
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] singlemonkey edited a comment on issue #8722: Cannot use LocalDateTime as a sharding key

Posted by GitBox <gi...@apache.org>.
singlemonkey edited a comment on issue #8722:
URL: https://github.com/apache/shardingsphere/issues/8722#issuecomment-749907735


   > yyyy-MM-ddTHH:mm:ss
   
   I have trid but id didn't work."yyyy-MM-ddTHH:mm:ss" will throw a new error "Unknown pattern letter: T",i had  set datetime-pattern as "yyyy-MM-dd'T'HH:mm:ss",but the length of 'datetime-pattern' is 21 and the length of  sharding value(localdatetime) is 19,it will throw a "outOfRange" error here:
   ```
    private LocalDateTime parseDateTime(final String value) {
       return LocalDateTime.parse(value.substring(0, dateTimePatternLength), dateTimeFormatter);
   }
   ```
   
   I have saw the other issues but still couldn't find a way to solve this problem.[#8693 ](url)[#8470](url) just reported the same bug and commited code to solve this bug,but is there any solution in the current version?


----------------------------------------------------------------
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