Hi Koji, I was able to fix this issue using following with my JDBC connection -
jdbc:mysql://localhost:3306/nifi_test?zeroDateTimeBehavior=convertToNull It was basically causing due to null values in a Timestamp column which I was able to cater by converting it to null. Thanks, Mohit -----Original Message----- From: Koji Kawamura [mailto:[email protected]] Sent: 08 November 2017 05:00 To: [email protected] Subject: Re: NiFi ExecuteSQL error => can not be represented as java.sql.Timestamp Hi Mohit, The exception looks as if the entire string ' 821725069 2161514622096 ... 0-00 0 3' was converted to java.sql.Timestamp. Would you share your create table DDL statement, few sample record data, NiFi, MySQL and JDBC driver version you're using? Thanks, Koji On Wed, Nov 8, 2017 at 1:14 AM, <[email protected]> wrote: > Hi all, > > > > I’m facing issue while fetching records from mysql table with > Timestamp column. Table has 4 timestamp columns. It is working fine > when I change the data type to string. > > > > It throws the following exception : > > > > org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: > Value ' 821725069 2161514622096 > > ________________________________ > 001[1]20 18248217 > 233243264523 > 2332442942490 > 00233244294249 004217 2017-05-01 00:42:17[1]65 N 62001-404-282360 11 1 > 0000-00-00 00:00:00 > 01079211655171 0 62001-404-28236 > > -10.751025 > > -10.751025 0.270833 6.729165 6.458332[1]96 0.2708331 00 > > ________________________________ > 404 > 0023324429420[1]650 [1]10220 > [email protected][email protected] > 8-1007175176-0846083c 20170501 > 233243264523 2017-05-01 > 19:37:061CCNCDR44-ASCCN8_03-Blk65536Blk-2360-20170501-3841 > ________________________________ > 614[1]410 > 010792116551710 > ________________________________ > 0-00 0 3 ' can not be represented as > java.sql.Timestamp > > at > org.apache.nifi.processors.standard.ExecuteSQL$2.process(ExecuteSQL.ja > va:220) > > at > org.apache.nifi.controller.repository.StandardProcessSession.write(Sta > ndardProcessSession.java:2570) > > at > org.apache.nifi.processors.standard.ExecuteSQL.onTrigger(ExecuteSQL.ja > va:206) > > at > org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcesso > r.java:27) > > at > org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardPro > cessorNode.java:1119) > > at > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(Cont > inuallyRunProcessorTask.java:147) > > at > org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(Cont > inuallyRunProcessorTask.java:47) > > at > org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run > (TimerDrivenSchedulingAgent.java:128) > > at > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511 > ) > > at > java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308) > > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.a > ccess$301(ScheduledThreadPoolExecutor.java:180) > > at > java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.r > un(ScheduledThreadPoolExecutor.java:294) > > at > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.j > ava:1149) > > at > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor. > java:624) > > at java.lang.Thread.run(Thread.java:748) > > Caused by: java.sql.SQLException: Value ' 821725069 > 2161514622096 > > ________________________________ > 001[1]20 18248217 > 233243264523 > 2332442942490 > 00233244294249 004217 2017-05-01 00:42:17[1]65 N 62001-404-282360 11 1 > 0000-00-00 00:00:00 > 01079211655171 0 62001-404-28236 > > -10.751025 > > -10.751025 0.270833 6.729165 6.458332[1]96 0.2708331 00 > > ________________________________ > 404 > 0023324429420[1]650 [1]10220 > [email protected][email protected] > 8-1007175176-0846083c 20170501 > 233243264523 2017-05-01 > 19:37:061CCNCDR44-ASCCN8_03-Blk65536Blk-2360-20170501-3841 > ________________________________ > 614[1]410 > 010792116551710 > ________________________________ > 0-00 0 3 ' can not be represented as > java.sql.Timestamp > > 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.ResultSetRow.getTimestampFast(ResultSetRow.java:937) > > at > com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:559) > > at > com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:5 > 918) > > at > com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:5588) > > at > com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:4549) > > at > org.apache.commons.dbcp.DelegatingResultSet.getObject(DelegatingResult > Set.java:325) > > at > org.apache.commons.dbcp.DelegatingResultSet.getObject(DelegatingResult > Set.java:325) > > at > org.apache.nifi.processors.standard.util.JdbcCommon.convertToAvroStrea > m(JdbcCommon.java:322) > > at > org.apache.nifi.processors.standard.ExecuteSQL$2.process(ExecuteSQL.ja > va:218) > > ... 14 common frames omitted > > > > Kindly help. > > > > Regards, > > Mohit Jain
