Apache NIFI: Occasional Exception with PUTSQL Processor java.sql.BatchUpdateException: Lock wait timeout exceeded

Question

I'm encountering occasional Exception when using PUTSQL processor in apache Nifi.

I have already Increased innodb_lock_wait_timeout But I'm still encountering the error occasionally. How can I fix this error? And what are the optimal configurations when using putsql processor to insert large volume of records into mariadb?

Variable_name;Value
innodb_lock_wait_timeout;300

Error Snippet:
2023-11-24 18:04:45,837 ERROR [Timer-Driven Process Thread-51] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c7255d17-018b-1000-143e-fc839881330c] Failed to update database due to a failed batch update, java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction. There were a total of 1 FlowFiles that failed, 14 that succeeded, and 0 that were not execute and will be routed to retry;
java.sql.BatchUpdateException: Lock wait timeout exceeded; try restarting transaction
at com.mysql.cj.jdbc.exceptions.SQLError.createBatchUpdateException(SQLError.java:224)

steven-matison · Accepted Answer

@ckumar is Correct.   For large volume ingestion into the receiving host, you need a maria db host that can handle 1000s of transactions per second.   Nifi can easily exceed default configurations for total connections.   With a well tuned highly available mariadb,  you can crank up execution time and concurrency on nifi and get 10,000s+ transactions per second.

ckumar · Answer

The reported behavior with Lock wait timeout in Mysql/MariaDB is not in control of NiFi PutSQL, You have to check your DB Table for possible issues. Thank you

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.