Hi Community,
We are facing an error with the PutSQL processor while inserting a batch of 20
flowfiles per transaction. Each flowfile contains a record to insert.
Quite often, we get an error that it couldn't insert a record into the
database, so the flowfile exits on Retry, but when it retries, it succeeds.
The error we get from Nifi is the following:
2022-08-09 12:23:15,803 ERROR [Timer-Driven Process Thread-24]
o.apache.nifi.processors.standard.PutSQL
PutSQL[id=aae45348-0181-1000-ffff-ffffaaa08fa0] Failed to update database due
to a failed batch update, java.sql.BatchUpdateException: invalid arguments in
call. There were a total of 1 FlowFiles that failed, 0 that succeeded, and 1
that were not execute and will be routed to retry; :
java.sql.BatchUpdateException: invalid arguments in call
java.sql.BatchUpdateException: invalid arguments in call
at
oracle.jdbc.driver.OraclePreparedStatement.generateBatchUpdateException(OraclePreparedStatement.java:10345)
at
oracle.jdbc.driver.OraclePreparedStatement.executeBatchWithoutQueue(OraclePreparedStatement.java:10107)
at
oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9987)
at
oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9939)
at
oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:261)
at
org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
at
org.apache.commons.dbcp2.DelegatingStatement.executeBatch(DelegatingStatement.java:242)
at
org.apache.nifi.processors.standard.PutSQL.lambda$null$10(PutSQL.java:390)
at
org.apache.nifi.processor.util.pattern.ExceptionHandler.execute(ExceptionHandler.java:127)
at
org.apache.nifi.processors.standard.PutSQL.lambda$new$12(PutSQL.java:388)
at
org.apache.nifi.processor.util.pattern.PutGroup.putFlowFiles(PutGroup.java:94)
at org.apache.nifi.processor.util.pattern.Put.onTrigger(Put.java:102)
at
org.apache.nifi.processors.standard.PutSQL.lambda$onTrigger$20(PutSQL.java:600)
at
org.apache.nifi.processor.util.pattern.PartialFunctions.onTrigger(PartialFunctions.java:114)
at
org.apache.nifi.processor.util.pattern.RollbackOnFailure.onTrigger(RollbackOnFailure.java:184)
at org.apache.nifi.processors.standard.PutSQL.onTrigger(PutSQL.java:600)
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.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
at
java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Do you know what is the cause of the error? How does the PutSQL processor work
behind the scenes?
Some data of the flow to take into account in case it contributes:
* 1 million data approx every 24 hours
* 20 mb of data approx (attached image)
* 1 flowfile > 1 row
I attach the PutSQL configuration and and the Status History of "Bytes In" for
the last 5 minutes.
Is there any configuration or way in PutSQL to capture the error message
returned by the database? For example, through an attribute of the flow file
that failed.
Thanks!
Sergio
