Hi Mohit, Thanks for sharing the update, glad to know that you found a solution! (The exception message still looks strange to me though..)
Koji On Wed, Nov 8, 2017 at 4:29 PM, <[email protected]> wrote: > 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 >
