I am getting following exception with PutSQL processor "2017-04-04 20:18:00,746 ERROR [Timer-Driven Process Thread-10] o.apache.nifi.processors.standard.PutSQL PutSQL[id=aff94ebf-739d-1936-ffff-ffff9e38bbdf] Failed to update database due to a failed batch update. There were a total of 1 FlowFiles that failed, 0 that succeeded, and 0 that were not execute and will be routed to retry;"
The same sql if I run through command line works fine. There are no additional details for the exception.
Insert into PCSOR (policy_num, prem_amount, LOB, AdminSystem, Name, DOB, Phone, Address) values ( '123456','2000.00','PC','PC SOR','John Smith','1/1/1970','212-010-2345','316 Lincoln Rd. Brooklyn, NY 11225') ON DUPLICATE KEY UPDATE Address='316 Lincoln Rd. Brooklyn, NY 11225';
I'm new to NiFi, and I'm not sure your data flow has same condition as mine, but I have a same issue of being occurred the same exception that you mentioned.
I'm using Oracle 11g XE, there was no invalid query nor invalid data. In addition, I had another problem with the Oracle session of PutSQL been locked when I let a lot of flowfile flow to PutSQL processor, e.g., 5,000 flowfile in 0.5 sec.
I have spent all day long to fix this problem today modifying almost every single properties of all processors connected to the flow, and even of DBCP controller service... and finally found the cause.
In processor PutSQL, there is a property named 'Support Fragmented Transactions'. I don't know pretty much about this and need to know how it works, but when I have set it false, the problem was solved. And it took some time more than before.
I'm not an expert of NiFi, but I hope this might be helpful for you.