Created 04-04-2017 08:21 PM
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.
Created 04-04-2017 08:28 PM
Can you please share the details of what was being put? Perhaps explain/show what the content of the failed flow file was?
Created on 04-04-2017 08:35 PM - edited 08-18-2019 12:56 AM
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';
Created 04-04-2017 08:40 PM
are any updates working or is the only one being tried?
Created 04-04-2017 08:48 PM
thats the only one tried , let me try simple insert.
Created 04-04-2017 08:55 PM
Created 04-04-2017 08:33 PM
please post full exception logs, SQL string, flow file, attributes. is your db connection working?
for further debugging: https://dzone.com/articles/finding-nifi-errors
Created 04-04-2017 08:38 PM
Created 04-04-2017 08:42 PM
Created 04-04-2017 08:58 PM
is the data type of DOB date? can you try '1970-01-01'
Created 04-04-2017 09:10 PM
everything is varchar.
Created on 02-23-2021 02:01 AM - edited 02-23-2021 02:13 AM
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.