Support Questions
Find answers, ask questions, and share your expertise

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

Solved Go to solution

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

Expert Contributor

12890-nifi-putsql-error.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

Expert Contributor

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.

View solution in original post

9 REPLIES 9

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

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

Expert Contributor

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

Super Guru

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

Super Guru

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

Expert Contributor

@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

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

Super Guru

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

Super Guru

have you restarted NIFI?

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

Expert Contributor

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.

View solution in original post