NiFi Error:failed to update database due to failed batch update

[#1]

Hi, i am loading records from a csv file into Oracle 12c. The PutSQL processor comes up with error message "failed to update database due to failed batch update". I am able to run the insert statements using sql developer but not sure why PutSQL is having issues. What could be wrong?

[#2]

Hi @Joshua Adeleke. Could you have a look at ./logs/nifi-app.log file and share the stack trace? Thanks!

[#3]

Hi @Pierre Villard, see the PutSQL error below;

2017-02-23 14:40:43,173 INFO [Heartbeat Monitor Thread-1] o.a.n.c.c.h.AbstractHeartbeatMonitor Finished processing 1 heartbeats in 21071 nanos
2017-02-23 14:40:43,202 INFO [Timer-Driven Process Thread-18] o.a.n.controller.FileSystemSwapManager Successfully swapped out 10000 FlowFiles from FlowFileQueue[id=14c03e86-82c8-103d-984e-73d1ee3b19f6] to Swap File /apps1/flowfile-repo/swap/1487853642436-14c03e86-82c8-103d-984e-73d1ee3b19f6-2bfefc74-f917-4d3c-80a2-2707596f1dd8.swap
2017-02-23 14:40:43,617 INFO [Timer-Driven Process Thread-18] o.a.n.c.r.WriteAheadFlowFileRepository Successfully swapped out 10000 FlowFiles from FlowFileQueue[id=14c03e86-82c8-103d-984e-73d1ee3b19f6] to Swap File /apps1/flowfile-repo/swap/1487853642436-14c03e86-82c8-103d-984e-73d1ee3b19f6-2bfefc74-f917-4d3c-80a2-2707596f1dd8.swap
2017-02-23 14:40:44,580 INFO [StandardProcessScheduler Thread-3] o.a.n.c.s.TimerDrivenSchedulingAgent Scheduled PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] to run with 5 threads
2017-02-23 14:40:44,584 ERROR [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;
2017-02-23 14:40:44,584 ERROR [Timer-Driven Process Thread-8] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;
2017-02-23 14:40:44,585 ERROR [Timer-Driven Process Thread-17] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;
2017-02-23 14:40:44,585 ERROR [Timer-Driven Process Thread-10] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;
2017-02-23 14:40:44,585 ERROR [Timer-Driven Process Thread-3] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;
2017-02-23 14:40:44,586 ERROR [Timer-Driven Process Thread-17] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;
2017-02-23 14:40:44,586 ERROR [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=c37c33b4-f4f6-165c-beb3-be6de80a4882] 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;

[#4]

Can you access oracle 12c from that same machine from the command-line or from Oracle SQL Developer? Make sure there are no firewall or other blockers.

Check the SQL connection, is there errors listed there? Anything in the data provenance look odd?

For me, sometimes that will happen if there's a network issue or bad data.

[#5]

Look for errors

https://community.hortonworks.com/articles/71839/decyphering-error-messages-in-apache-nifi.html

[#6]

@Timothy Spann I am able to access Oracle 12c and successfully run the insert statements generated in NiFi. SQL connection is fine, no errors. I have checked data provenance as well and nothing seems to be amiss but that it dropped the flowfile.

[#7]

Could you set the relationship "retry" back to the SQL processor just to check if it gives more info in the logs? And the same for other relationship you are auto terminating at the moment.

[#8]

what is the Oracle Connection parameters you are using?

which JDBC driver?

is it the same user/password that you are using in SQL Developer?

is oracle limiting where connections are allowed from?

do you have enough / too many connections specified in your NIFI pool?

if you have Oracle DBA access, can you see if anything is showing up in Oracle? anything in oracle logs?

can you trace via TCP/IP ala wireshark?

can you read from that connection via NIFI?

are there specific table permissions?

[#9]

have you restarted NIFI?

[#10 · Accepted solution]

The issue was with a column that handled datetime in millseconds and needed the format as 'YYYY-MM-DD HH24:MI:SS,FF9' specified in NiFi and it worked.