PUTSQL Retry Relation is not triggered

[#1]

Hi,

Im trying to utilize the rety relation of the PutSQL processor by redirecting it to the RetryFlowFile processor. However nothing seem to trigger this relationship. My understanding is that the retry is triggered when the DB could not be reached such as bad connection string, sql server is down...etc. but no matter what I do on the sql server to test this scenario, the retry is not triggered, instead an an exception is thrown (jdbc.SQLServerException) and the flowfile is pushed back to the queue and penalized! Here is what I did on my sql server express instance to test:

- Disable the SQL Server service.

- Changing the connection string in the JDBC connection pool service to be incorrect.

- Taking the Database instance offline

- Even blocking the target table by doing select inside transaction with wait time

None of the above seem to trigger the Retry. The failure relation seem to be triggered by query error and is working fine. My question is why the Retry is not getting triggered and what condition triggers it?

Thanks

[#2]

Can someone answer my question please? Thank you

[#3]

retry:  A FlowFile is routed to this relationship if the database cannot be updated but attempting the operation again may succeed

I think that depends on the JDBC driver and the database server which they choose to implement.

I have only ever seen failures for JDBC related things.

The code looks like it depends on what SQL Server returns through the JDBC

Have you tried PutDatabaseRecord

https://github.com/apache/nifi/blob/c396927299586b896df4ebc745793b4c451f3898/nifi-nar-bundles/nifi-s...

https://github.com/apache/nifi/blob/c396927299586b896df4ebc745793b4c451f3898/nifi-nar-bundles/nifi-s...

It seems their JDBC driver may only have retry for connection issue and that would happen in the connection pool and not in the PutSQL

https://docs.microsoft.com/en-us/sql/connect/jdbc/jdbc-driver-support-for-high-availability-disaster...

I don't think you can work with RetryFlowFile and PutSQL with SQL Server with the generic JDBC driver.   There may be a commercial JDBC driver that has that functionality.   https://media.datadirect.com/download/docs/jdbc/alljdbc/index.html#page/jdbcconnect/Specifying_Conne...

If this is a critical support issue, please open a ticket with your cloudera support account.

[#4]

This is a bug in NiFi. I found that codes are updated in this ticket to handle connection unavailable [NIFI-10265] PutDatabaseRecord hangs and keeps file in queue when DB is unavailable instead of sendi... with Nifi version 1.17.0.

You should upgrade NiFi to a newer version which will route connection unavailable error to failure relationship where you can setup retry policy to handle it.

[#5]

Definitely upgrade to the latest NiFI which is 1.22.