I have a situation, where I have to get the combined data from two tables from Mysql database, ExecuteSql processor throwing out bellow error, where as the same query is working fine from Mysql Workbench.
2020-04-22 14:55:06,597 ERROR [Timer-Driven Process Thread-8] o.a.nifi.processors.standard.ExecuteSQL ExecuteSQL[id=9d81c7ae-0171-1000-f638-2f1e60bfe48e] Unable to execute SQL select query select t1.CODE_CLOCK_ID, t1.COMPANY_CD, t1.GROUP_SEGMENT_L1, t1.GROUP_SEGMENT_CD, t2.CODE_CLOCK_ID, t2.COMPANY_CD, t2.GROUP_SEGMENT_L1, t2.GROUP_SEGMENT_CD from sheet26 t1 INNER JOIN sheet27 t2 on t1.CODE_CLOCK_ID = t2.CODE_CLOCK_ID and t1.COMPANY_CD = t2.COMPANY_CD LIMIT 0, 1000; due to org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0.. No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0. org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0. at org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$1(AbstractExecuteSQL.java:301) at org.apache.nifi.controller.repository.StandardProcessSession.write(StandardProcessSession.java:2746) at org.apache.nifi.processors.standard.AbstractExecuteSQL.onTrigger(AbstractExecuteSQL.java:297) at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27) at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1176) at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:213) at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117) at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110) at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305) at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) at java.base/java.lang.Thread.run(Thread.java:830) Caused by: org.apache.avro.AvroRuntimeException: Duplicate field CODE_CLOCK_ID in record any.data.sheet26: CODE_CLOCK_ID type:UNION pos:4 and CODE_CLOCK_ID type:UNION pos:0. at org.apache.avro.Schema$RecordSchema.setFields(Schema.java:651) at org.apache.avro.SchemaBuilder$FieldAssembler.endRecord(SchemaBuilder.java:2013) at org.apache.nifi.util.db.JdbcCommon.createSchema(JdbcCommon.java:636) at org.apache.nifi.util.db.JdbcCommon.convertToAvroStream(JdbcCommon.java:239) at org.apache.nifi.processors.standard.sql.DefaultAvroSqlWriter.writeResultSet(DefaultAvroSqlWriter.java:49) at org.apache.nifi.processors.standard.AbstractExecuteSQL.lambda$onTrigger$1(AbstractExecuteSQL.java:299) ... 13 common frames omitted
what could be issue and what I have to change in query to get the record set from database?
I am using Nifi 1.11.4.
This issue happens even excluding INNER or even replacing INNER JOIN with "where" in the select query.
... View more
