Ingestion Via Custom MySQL Query Fails With java.sql.SQLException: Illegal value for java.sql.Time Type

Problem

An import job/data link based on a custom MySQL query fails with the following (or similar) exception:

ERROR [2018-05-23 07:21:59.544] [JobExecutionPlanRunner] (ClusterSession.java:252) - Failed to run cluster job 'Import job (12): TestJob#source' [2 sec]
datameer.dap.sdk.ErrorLogException: Invalid record source: com.mysql.jdbc.....
at datameer.dap.common.job.mr.input.ImportExceptionHandler.handleRecordException(ImportExceptionHandler.java:79)
at datameer.dap.common.job.mr.input.StreamlinedImportRecordReader.next(StreamlinedImportRecordReader.java:73)
at datameer.dap.common.job.mr.input.StreamlinedImportRecordReader.next(StreamlinedImportRecordReader.java:22)

Caused by: java.sql.SQLException: Illegal hour value '30' for java.sql.Time type in value '30:10:33.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:964)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
at com.mysql.jdbc.TimeUtil.fastTimeCreate(TimeUtil.java:286)
at com.mysql.jdbc.ResultSetImpl.fastTimeCreate(ResultSetImpl.java:976)
at com.mysql.jdbc.ResultSetRow.getTimeFast(ResultSetRow.java:884)
at com.mysql.jdbc.BufferRow.getTimeFast(BufferRow.java:543)
at com.mysql.jdbc.ResultSetImpl.getTimeInternal(ResultSetImpl.java:5572)
at com.mysql.jdbc.ResultSetImpl.getTime(ResultSetImpl.java:5340)
at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4546)
at datameer.dap.common.db.DbDialect.getImportValue(DbDialect.java:565)
at datameer.dap.common.job.dapimport.jdbc.JdbcRecordParser.readRecord(JdbcRecordParser.java:52)

Cause

In case of a custom query where you use time/date related functions, e.g. TIMEDIFF() to find out the duration of a process and this function returns a timestamp in a format hh:mm:ss, that doesn't comply the rule below, an exception will be thrown.

hour - 0 to 23
minute - 0 to 59
second - 0 to 59

This limitation comes from the implementation of the MySQL JDBC driver. Please refer to the pages below for further details.

• public class: TimeUtil
• Class Time

Solution

You can either change the input data to avoid improper values or exclude the function that causes the issue from a query and use Datameer instruments after ingestion to transform the data.