[GitHub] [incubator-shardingsphere] liangcd commented on issue #2389: sharding-proxy fail to handle '0000-00-00 00:00:00' Timestamp result

[GitBox]

liangcd commented on issue #2389: sharding-proxy fail to handle '0000-00-00 
00:00:00' Timestamp result
URL: 
https://github.com/apache/incubator-shardingsphere/issues/2389#issuecomment-492981111
 
 
   Which version of ShardingSphere did you use?       sharding-proxy-4.0.0-RC1
   
   Which project did you use? Sharding-JDBC or Sharding-Proxy?    Sharding-Proxy
   
   Expect behavior:
   Sharding-proxy should be compatible with mysql 5.6 legacy timestamp field .
   
   Actual behavior:
   running sharding-proxy with  mysql 5.6 legacy timestamp field (value = 
'0000-00-00 00:00:00') got follow error.
    Error:
    Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
   
   Reason analyze
     Mysql 5.6 allows timestamp field  with default value '0000-00-00 
00:00:00', and it seems the proxy can not convert value '0000-00-00 00:00:00' 
to a java Timestamp.
   
   Steps to reproduce the behavior, such as: SQL to execute, sharding rule 
configuration, when exception occur etc.
   
   CREATE TABLE `tb_user_info_0` (
     `USER_CODE` varchar(50) NOT NULL,
     `CREATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
     `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
     `USER_NAME` varchar(200) DEFAULT NULL,
     PRIMARY KEY (`USER_CODE`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   CREATE TABLE `tb_user_info_1` (
     `USER_CODE` varchar(50) NOT NULL,
     `CREATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
     `UPDATE_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
CURRENT_TIMESTAMP,
     `USER_NAME` varchar(200) DEFAULT NULL,
     PRIMARY KEY (`USER_CODE`)
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
   INSERT INTO `tb_user_info_0` (`USER_CODE`, `CREATE_TIME`, `UPDATE_TIME`, 
`USER_NAME`)
   VALUES ('0', '0000-00-00 00:00:00', '2019-05-16 15:11:02', 'Bob');
   
   INSERT INTO `tb_user_info_1` (`USER_CODE`, `CREATE_TIME`, `UPDATE_TIME`, 
`USER_NAME`)
   VALUES ('1', '0000-00-00 00:00:00', '2019-05-16 14:44:41', 'Alice');
   
   # config-sharding-test.yaml
   schemaName: test
   
   dataSources:
     ds_0:
       url: jdbc:mysql://172.16.4.253:3306/test?serverTimezone=UTC&useSSL=false
       username: aaa
       password: bbb
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
   
   shardingRule:
     tables:
       tb_user_info:
         actualDataNodes: ds_0.tb_user_info_${0..1}
         tableStrategy:
           inline:
             shardingColumn: USER_CODE
             algorithmExpression: 
tb_user_info_${Integer.parseInt(USER_CODE.substring(USER_CODE.length() - 1)) % 
2}
         keyGenerator:
           type: SNOWFLAKE
           column: USER_CODE
     bindingTables:
       - tb_user_info
     defaultDatabaseStrategy:
       none:
     defaultTableStrategy:
       none:
   
   SQL:
   select * from tb_user_info;
   
   

----------------------------------------------------------------
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:
us...@infra.apache.org


With regards,
Apache Git Services