Sergio,
Your email says the flowfiles each contain a record to insert, but
PutSQL takes a full SQL statement such as INSERT INTO tableName VALUES
('hello', 'world', 1). If you have a record of data rather than a SQL
statement, you can use PutDatabaseRecord for that instead. If you do
have SQL statements in your flowfile, can you share an example?
Thanks,
Matt
On Tue, Aug 9, 2022 at 4:12 PM Sergio M. <[email protected]> wrote:
>
> 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
>
