mysql-Cannot convert value '2020-02-17 17:00:56' from column 3 to TIMESTAMP，nanos > 999999999 or 0

场景：将activiti工作流数据库从oracle转换成mysql，使用List comlist = taskService.getTaskComments(task_id_);查询ACT_HI_COMMENT历史信息,当id_为6位数字时（例如100036），tomcat报错，小于6位数字的id_不会报错。

页面报错信息

Error querying database. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column ‘TIME_’ from result set. Cause: java.sql.SQLException: Cannot convert value ‘2020-02-17 17:00:56.000000’ from column 3 to TIMESTAMP. ### The error may exist in org/activiti/db/mapping/entity/Comment.xml ### The error may involve org.activiti.engine.impl.persistence.entity.CommentEntity.selectCommentsByTaskId ### The error occurred while handling results ### SQL: select * from ACT_HI_COMMENT where TASK_ID_ = ? and TYPE_ = ‘comment’ order by TIME_ desc ### Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column ‘TIME_’ from result set. Cause: java.sql.SQLException: Cannot convert value ‘2020-02-17 17:00:56.000000’ from column 3 to TIMESTAMP.

idea报错信息主要包含2种

Cannot convert value '2020-02-17 17:00:56.000000' from column 3 to TIMESTAMP.
 
Caused by: java.lang.IllegalArgumentException: nanos > 999999999 or < 0

处理方法

根据报错信息可以看出是数据类型转换错误，项目中使用的MySQL JDBC driver版本是mysql-connector-java-5.1.10-bin，切换为高版本mysql-connector-java-8.0.15.jar之后问题成功解决。
相应的db.driverClassName=com.mysql.jdbc.Driver，要改为db.driverClassName=com.mysql.cj.jdbc.Driver。

对应Cannot convert value ‘0000-00-00 00:00:00’ from column 1 to TIMESTAMP的解决办法
如果数据库中日期字段值为’0000-00-00 00:00:00"时 是无效的时间
在jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8 后面加上一个参数zeroDateTimeBehavior=convertToNull
即更改为：
jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull