Hi Sergio, I'm not a nifi dev , but for my experience with mysql , it seems that you have some problem writing multiple operations in single transaction in the database. Looking in the mysql documentation i didn't find any configuration that limit the number of operations by transactions, so we have two ways to debug:
- Check if in the moment of the error, the hardware that support the db have a problem related to I/O disk speed, memory/cpu/etc. - Check if you have a problem related to multithreading in the sql db ( https://stackoverflow.com/questions/10123460/mysql-configuration-for-multiple-threading-transactions) reducing the number of operation by transaction reduce the amount of errors ? Hope this help you debug. *Breno* Em ter., 9 de ago. de 2022 às 17:12, Sergio M. <[email protected]> escreveu: > 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 > >
