Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
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

avatar
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

avatar

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

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

avatar
Master 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.

avatar
Master Guru

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

avatar

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.

avatar
Master 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?

avatar
Master Guru

have you restarted NIFI?

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