question Re: NiFi Error:failed to update database due to failed batch update in Archives of Support Questions (Read Only)

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

joshua_adeleke — Mon, 19 Aug 2019 10:24:38 GMT

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?

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

pvillard — Thu, 23 Feb 2017 21:39:40 GMT

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

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

joshua_adeleke — Thu, 23 Feb 2017 21:50:14 GMT

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;

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

TimothySpann — Thu, 23 Feb 2017 22:25:51 GMT

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.

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

TimothySpann — Thu, 23 Feb 2017 22:28:28 GMT

Look for errors

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

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

joshua_adeleke — Fri, 24 Feb 2017 00:51:13 GMT

@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.

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

pvillard — Fri, 24 Feb 2017 00:53:38 GMT

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.

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

TimothySpann — Fri, 24 Feb 2017 01:43:46 GMT

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?

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

TimothySpann — Fri, 24 Feb 2017 01:44:02 GMT

have you restarted NIFI?

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

joshua_adeleke — Wed, 01 Mar 2017 17:56:30 GMT

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.