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