PutSQL failed to update database due to a failed batch update. How to resolve it?

[#1]

I am trying to run Redshift COPY command.

My flow is GetFile --> ReplaceText --> PutSQL --> LogAttribute

This gives me error as observed in nifi-app.log file:

ERROR [Timer-Driven Process Thread-5] o.apache.nifi.processors.standard.PutSQL PutSQL[id=015b1002-5ef3-12b5-6171-cc112664e2df] 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;

I configured my DBCPConnectionPool correctly and for some time, I can see the query running in redshift. But after some time, I get this error.

I tried using ExecuteSQL processor as well but as my query does not return anything so it won't help.

[#2 · Accepted solution]

Try the following:

1. Route the "failure" relationship to some processor you don't start (like UpdateAttribute)
2. When a flow file fails, right-click on the failure connection and choose List Queue
3. Click the info button (an "i" in a circle) to the left of the flow file, then choose View to see the contents

Does that look like a valid SQL statement? Can you try running it from the command line or in a separate PutSQL instance?

Also since you are getting the error after time, perhaps the connection is going idle and then failing. Is there any other error information around the error in the log file you mention above? Sometimes there is a "Caused by" line below it that refers to the lower-level error.

What version of NiFi are you using? As of NiFi 1.1.0, you can supply a Validation Query to the DBCPConnectionPool, this ensures that the connection is valid. A Validation Query is a query that does as little work as possible while still establishing connectivity to the server. An example of this (for Oracle) is "select 1 from dual". If you have that property available to you, please try something like that as the Validation Query, it may alleviate this issue if it is intermittent and related to idle time.

[#3]

Hello,

Yes, i tried looking into it the way you told. It was some delimiter error in the query.

Thanks @Matt Burgess