【Kettle】异常：Unable to get value 'Date' from database resultset

一、背景

电脑安装的kettle工具版本是7.1，由于前一个项目的使用的Mysql8.0的数据库，所有将lib目录下mysql连接驱动升级到mysql-connector-java-8.0.26.jar，当前使用的数据版本是5.7.24。

在整合kettle脚本，发现脚本好多都报错，具体异常如下：

2022/01/18 23:06:36 - dysjcq.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : Unexpected error
2022/01/18 23:06:36 - dysjcq.0 - ERROR (version 7.1.0.0-12, build 1 from 2017-05-16 17.18.02 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2022/01/18 23:06:36 - dysjcq.0 - Couldn't get row from result set
2022/01/18 23:06:36 - dysjcq.0 - 
2022/01/18 23:06:36 - dysjcq.0 - Unable to get value 'Date' from database resultset, index 19
2022/01/18 23:06:36 - dysjcq.0 - HOUR_OF_DAY: 0 -> 1
2022/01/18 23:06:36 - dysjcq.0 - 
2022/01/18 23:06:36 - dysjcq.0 - 
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.database.Database.getRow(Database.java:2546)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.database.Database.getRow(Database.java:2516)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:148)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2022/01/18 23:06:36 - dysjcq.0 - 	at java.lang.Thread.run(Thread.java:745)
2022/01/18 23:06:36 - dysjcq.0 - Caused by: org.pentaho.di.core.exception.KettleDatabaseException: 
2022/01/18 23:06:36 - dysjcq.0 - Unable to get value 'Date' from database resultset, index 19
2022/01/18 23:06:36 - dysjcq.0 - HOUR_OF_DAY: 0 -> 1
2022/01/18 23:06:36 - dysjcq.0 - 
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.row.value.ValueMetaBase.getValueFromResultSet(ValueMetaBase.java:4964)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.database.BaseDatabaseMeta.getValueFromResultSet(BaseDatabaseMeta.java:2107)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.database.DatabaseMeta.getValueFromResultSet(DatabaseMeta.java:2950)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.database.Database.getRow(Database.java:2538)
2022/01/18 23:06:36 - dysjcq.0 - 	... 4 more
2022/01/18 23:06:36 - dysjcq.0 - Caused by: java.sql.SQLException: HOUR_OF_DAY: 0 -> 1
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:89)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:63)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:73)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:85)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.jdbc.result.ResultSetImpl.getTimestamp(ResultSetImpl.java:939)
2022/01/18 23:06:36 - dysjcq.0 - 	at org.pentaho.di.core.row.value.ValueMetaBase.getValueFromResultSet(ValueMetaBase.java:4946)
2022/01/18 23:06:36 - dysjcq.0 - 	... 7 more
2022/01/18 23:06:36 - dysjcq.0 - Caused by: com.mysql.cj.exceptions.WrongArgumentException: HOUR_OF_DAY: 0 -> 1
2022/01/18 23:06:36 - dysjcq.0 - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
2022/01/18 23:06:36 - dysjcq.0 - 	at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
2022/01/18 23:06:36 - dysjcq.0 - 	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
2022/01/18 23:06:36 - dysjcq.0 - 	at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:61)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.exceptions.ExceptionFactory.createException(ExceptionFactory.java:105)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromDate(SqlTimestampValueFactory.java:102)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromDate(SqlTimestampValueFactory.java:51)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.result.AbstractDateTimeValueFactory.createFromDate(AbstractDateTimeValueFactory.java:69)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.protocol.a.MysqlTextValueDecoder.decodeDate(MysqlTextValueDecoder.java:77)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.protocol.result.AbstractResultsetRow.decodeAndCreateReturnValue(AbstractResultsetRow.java:92)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.protocol.result.AbstractResultsetRow.getValueFromBytes(AbstractResultsetRow.java:243)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.protocol.a.result.ByteArrayRow.getValue(ByteArrayRow.java:91)
2022/01/18 23:06:36 - dysjcq.0 - 	... 9 more
2022/01/18 23:06:36 - dysjcq.0 - Caused by: java.lang.IllegalArgumentException: HOUR_OF_DAY: 0 -> 1
2022/01/18 23:06:36 - dysjcq.0 - 	at java.util.GregorianCalendar.computeTime(GregorianCalendar.java:2829)
2022/01/18 23:06:36 - dysjcq.0 - 	at java.util.Calendar.updateTime(Calendar.java:3353)
2022/01/18 23:06:36 - dysjcq.0 - 	at java.util.Calendar.getTimeInMillis(Calendar.java:1782)
2022/01/18 23:06:36 - dysjcq.0 - 	at com.mysql.cj.result.SqlTimestampValueFactory.localCreateFromDate(SqlTimestampValueFactory.java:100)
2022/01/18 23:06:36 - dysjcq.0 - 	... 15 more

二、异常分析

开始发现异常，在网上寻找解决办法。大体解决方法都是在url上添加参数如下

1. 设置参数serverTimezone=GMT%2B8，不起作用。
2. serverTimezone=Asia/Shanghai，不起作用。
3. zeroDateTimeBehavior=convertToNull，不起作用。

后来，有部分数据是可以成功，通过使用 limit语句，缩小发现，其中一条错误数据，只要字段值为1987-04-12就报错，这个字段是DATE类型，改成其他值就成功。找不到问题。

猜测可能跟驱动与数据不匹配造成的。驱动版本是8.0.26,数据库版本是5.7.24。后将驱动换回自带驱动5.1.47版本。再次运行，异常消失。