NiFi PutSQL exception.

[#1]

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.

[#2]

Can you please share the details of what was being put? Perhaps explain/show what the content of the failed flow file was?

[#3]

@jwitt

PutSQL :

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';

Sample Flow:

Connection Pool.

[#4]

are any updates working or is the only one being tried?

[#5]

thats the only one tried , let me try simple insert.

[#6]

@jwitt tried the executeSQL which is working fine.

[#7]

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

[#8]

@milind pandit : Can you try the same without the ; (semicolon) at the end. Not sure though.

[#9]

same result.

[#10]

is the data type of DOB date? can you try '1970-01-01'

[#11]

everything is varchar.

[#12]

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.