You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@nifi.apache.org by Nattida Pankiriya <na...@ascendcorp.com.INVALID> on 2024/06/13 07:39:15 UTC
PutDatabaseRecord routing to failure relationship after getting Error Cause by SQLTransientConnectionException
Hi,
I have tested the PutDatabaseRecord processor in case the database
connection fails on Nifi version 1.26.0, and I propose to handle an error
with retry policy.
After I disconnected the database connection, I got an error message below
*ERROR* [Timer-Driven Process Thread-8] o.a.n.p.standard.PutDatabaseRecord
PutDatabaseRecord[id=bcec93b5-306b-3fec-6eac-dfd3916a5dab]
Failed to put Records to database for
StandardFlowFileRecord[uuid=ba18e9a8-2cc8-4f7e-adcd-1da757b483b1,claim=StandardContentClaim
[resourceClaim=StandardResourceClaim[id=1716964021086-21,
container=default, section=21], offset=0,
length=8450],offset=0,name=3bbaf37a-f692-4389-8cfa-59ce265ceaee,size=8450].
*Routing
to failure.*
*org.apache.nifi.processor.exception.ProcessException: Connection retrieval
failed*
at
org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:363)
at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:55)
at
java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
at java.base/java.lang.reflect.Method.invoke(Method.java:580)
at
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
at
org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:105)
at jdk.proxy15/jdk.proxy15.$Proxy93.getConnection(Unknown Source)
at
org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:486)
at
org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
at
org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1361)
at
org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:247)
at
org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:102)
at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
at
java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
at
java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
at
java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
at
java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at
java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1583)
*Caused by: java.sql.SQLTransientConnectionException:
*HikariCPConnectionPool[id=c25182df-725f-3c25-649b-9481538a3ec2]
- Connection is not available, request timed out after 5004ms.
at
com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
at
com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
at
org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:354)
... 18 common frames omitted
*Caused by: java.sql.SQLRecoverableException:* IO Error: Invalid Operation,
NOT Connected
at
oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9395)
at
oracle.jdbc.driver.PhysicalConnection.setNetworkTimeout(PhysicalConnection.java:10000)
at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:566)
at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173)
at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
... 21 common frames omitted
*Caused by: oracle.net.ns.NetException:* Invalid Operation, NOT Connected
at oracle.net.nt.TcpNTAdapter.setOption(TcpNTAdapter.java:757)
at oracle.net.ns.NSProtocol.setOption(NSProtocol.java:730)
at oracle.net.ns.NSProtocol.setSocketReadTimeout(NSProtocol.java:1045)
at
oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9392)
... 25 common frames omitted
Then I checked on the source code of the PutDatabaseRecord processor and
found code that handles Throwable with If clause.
I see that it only has "if(toAnalyze instanceof SQLTransientException)" but
not the ProcessException, thus, fails to catch this exception to handle as
retry relationship.
So I would like to clarify why this error routes to failure or if this is a
bug.
[image: image.png]
Best Regard
Nattida Pankiriya
Re: PutDatabaseRecord routing to failure relationship after getting Error Cause by SQLTransientConnectionException
Posted by Jim Steinebrey <jr...@gmail.com>.
Hi Nattida,
I remembered something right after I sent the last email,
SLQTransientConnectionException is a subclass of SQLTransientException so I do not have to add an implicit check for SLQTransientConnectionException.
Here is the Jira Ticket and the proposed change in a Pull Request that I made for this issue.
Ticket
https://issues.apache.org/jira/browse/NIFI-13397
Pull Request
https://github.com/apache/nifi/pull/8964
Best Regards,
Jim
> On Jun 13, 2024, at 3:39 AM, Nattida Pankiriya <na...@ascendcorp.com.INVALID> wrote:
>
> Hi,
> I have tested the PutDatabaseRecord processor in case the database connection fails on Nifi version 1.26.0, and I propose to handle an error with retry policy.
> After I disconnected the database connection, I got an error message below
>
> ERROR [Timer-Driven Process Thread-8] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=bcec93b5-306b-3fec-6eac-dfd3916a5dab]
> Failed to put Records to database for StandardFlowFileRecord[uuid=ba18e9a8-2cc8-4f7e-adcd-1da757b483b1,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1716964021086-21, container=default, section=21], offset=0, length=8450],offset=0,name=3bbaf37a-f692-4389-8cfa-59ce265ceaee,size=8450]. Routing to failure.
> org.apache.nifi.processor.exception.ProcessException: Connection retrieval failed
> at org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:363)
> at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:55)
> at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
> at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:105)
> at jdk.proxy15/jdk.proxy15.$Proxy93.getConnection(Unknown Source)
> at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:486)
> at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1361)
> at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:247)
> at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:102)
> at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
> at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
> at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
> at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
> at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
> at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
> at java.base/java.lang.Thread.run(Thread.java:1583)
> Caused by: java.sql.SQLTransientConnectionException: HikariCPConnectionPool[id=c25182df-725f-3c25-649b-9481538a3ec2] - Connection is not available, request timed out after 5004ms.
> at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
> at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
> at org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:354)
> ... 18 common frames omitted
> Caused by: java.sql.SQLRecoverableException: IO Error: Invalid Operation, NOT Connected
> at oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9395)
> at oracle.jdbc.driver.PhysicalConnection.setNetworkTimeout(PhysicalConnection.java:10000)
> at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:566)
> at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
> ... 21 common frames omitted
> Caused by: oracle.net.ns.NetException: Invalid Operation, NOT Connected
> at oracle.net.nt.TcpNTAdapter.setOption(TcpNTAdapter.java:757)
> at oracle.net.ns.NSProtocol.setOption(NSProtocol.java:730)
> at oracle.net.ns.NSProtocol.setSocketReadTimeout(NSProtocol.java:1045)
> at oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9392)
> ... 25 common frames omitted
>
> Then I checked on the source code of the PutDatabaseRecord processor and found code that handles Throwable with If clause.
> I see that it only has "if(toAnalyze instanceof SQLTransientException)" but not the ProcessException, thus, fails to catch this exception to handle as retry relationship.
> So I would like to clarify why this error routes to failure or if this is a bug.
>
>
>
>
> Best Regard
> Nattida Pankiriya
Re: PutDatabaseRecord routing to failure relationship after getting Error Cause by SQLTransientConnectionException
Posted by Jim Steinebrey <jr...@gmail.com>.
Hi Nattida,
Here is the Jira Ticket I opened if you want to track it.
https://issues.apache.org/jira/browse/NIFI-13397
Also I remembered something right after I sent the last email,
SLQTransientConnectionException is a subclass of SQLTransientException so the second line does not have to check for it explicitly.
so only one line of code needs to change. Here is my latest change (only the first line is changed compared to current code in GitHub)
final Throwable toAnalyze = (e instanceof BatchUpdateException || e instanceof ProcessException) ? e.getCause() : e;
if (toAnalyze instanceof SQLTransientException) { // this line remains unchanged
Have a great day!
Jim Steinebrey
> On Jun 13, 2024, at 3:39 AM, Nattida Pankiriya <na...@ascendcorp.com.INVALID> wrote:
>
> Hi,
> I have tested the PutDatabaseRecord processor in case the database connection fails on Nifi version 1.26.0, and I propose to handle an error with retry policy.
> After I disconnected the database connection, I got an error message below
>
> ERROR [Timer-Driven Process Thread-8] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=bcec93b5-306b-3fec-6eac-dfd3916a5dab]
> Failed to put Records to database for StandardFlowFileRecord[uuid=ba18e9a8-2cc8-4f7e-adcd-1da757b483b1,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1716964021086-21, container=default, section=21], offset=0, length=8450],offset=0,name=3bbaf37a-f692-4389-8cfa-59ce265ceaee,size=8450]. Routing to failure.
> org.apache.nifi.processor.exception.ProcessException: Connection retrieval failed
> at org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:363)
> at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:55)
> at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
> at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:105)
> at jdk.proxy15/jdk.proxy15.$Proxy93.getConnection(Unknown Source)
> at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:486)
> at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1361)
> at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:247)
> at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:102)
> at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
> at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
> at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
> at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
> at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
> at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
> at java.base/java.lang.Thread.run(Thread.java:1583)
> Caused by: java.sql.SQLTransientConnectionException: HikariCPConnectionPool[id=c25182df-725f-3c25-649b-9481538a3ec2] - Connection is not available, request timed out after 5004ms.
> at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
> at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
> at org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:354)
> ... 18 common frames omitted
> Caused by: java.sql.SQLRecoverableException: IO Error: Invalid Operation, NOT Connected
> at oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9395)
> at oracle.jdbc.driver.PhysicalConnection.setNetworkTimeout(PhysicalConnection.java:10000)
> at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:566)
> at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
> ... 21 common frames omitted
> Caused by: oracle.net.ns.NetException: Invalid Operation, NOT Connected
> at oracle.net.nt.TcpNTAdapter.setOption(TcpNTAdapter.java:757)
> at oracle.net.ns.NSProtocol.setOption(NSProtocol.java:730)
> at oracle.net.ns.NSProtocol.setSocketReadTimeout(NSProtocol.java:1045)
> at oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9392)
> ... 25 common frames omitted
>
> Then I checked on the source code of the PutDatabaseRecord processor and found code that handles Throwable with If clause.
> I see that it only has "if(toAnalyze instanceof SQLTransientException)" but not the ProcessException, thus, fails to catch this exception to handle as retry relationship.
> So I would like to clarify why this error routes to failure or if this is a bug.
>
>
>
>
> Best Regard
> Nattida Pankiriya
Re: PutDatabaseRecord routing to failure relationship after getting Error Cause by SQLTransientConnectionException
Posted by Jim Steinebrey <jr...@gmail.com>.
Hi Nattida,
That is an excellent point you raise. Thanks for raising it and for suggesting a change.
I looked at the existing code and
final Throwable toAnalyze = (e instanceof BatchUpdateException) ? e.getCause() : e;
if (toAnalyze instanceof SQLTransientException) {
and I feel it would be safer to change it this way (and still satisfy your use case)
final Throwable toAnalyze = (e instanceof BatchUpdateException || e instanceof ProcessException) ? e.getCause() : e;
if (toAnalyze instanceof SQLTransientException || toAnalyze instanceof SQLTransientConnectionException) {
I will be glad to make this change and see if reviewers approve it for the next release of NiFi.
If you need this change before the next release of NiFi, you could download the NiFi source and fix that one processor yourself.
If you have strong Java experience, I encourage you to contribute to the NiFi open source project in the future.
Sincerely,
Jim Steinebrey
> On Jun 13, 2024, at 3:39 AM, Nattida Pankiriya <na...@ascendcorp.com.INVALID> wrote:
>
> Hi,
> I have tested the PutDatabaseRecord processor in case the database connection fails on Nifi version 1.26.0, and I propose to handle an error with retry policy.
> After I disconnected the database connection, I got an error message below
>
> ERROR [Timer-Driven Process Thread-8] o.a.n.p.standard.PutDatabaseRecord PutDatabaseRecord[id=bcec93b5-306b-3fec-6eac-dfd3916a5dab]
> Failed to put Records to database for StandardFlowFileRecord[uuid=ba18e9a8-2cc8-4f7e-adcd-1da757b483b1,claim=StandardContentClaim [resourceClaim=StandardResourceClaim[id=1716964021086-21, container=default, section=21], offset=0, length=8450],offset=0,name=3bbaf37a-f692-4389-8cfa-59ce265ceaee,size=8450]. Routing to failure.
> org.apache.nifi.processor.exception.ProcessException: Connection retrieval failed
> at org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:363)
> at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:55)
> at java.base/jdk.internal.reflect.DirectMethodHandleAccessor.invoke(DirectMethodHandleAccessor.java:103)
> at java.base/java.lang.reflect.Method.invoke(Method.java:580)
> at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:254)
> at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:105)
> at jdk.proxy15/jdk.proxy15.$Proxy93.getConnection(Unknown Source)
> at org.apache.nifi.processors.standard.PutDatabaseRecord.onTrigger(PutDatabaseRecord.java:486)
> at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
> at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1361)
> at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:247)
> at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:102)
> at org.apache.nifi.engine.FlowEngine$2.run(FlowEngine.java:110)
> at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
> at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:358)
> at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
> at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
> at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
> at java.base/java.lang.Thread.run(Thread.java:1583)
> Caused by: java.sql.SQLTransientConnectionException: HikariCPConnectionPool[id=c25182df-725f-3c25-649b-9481538a3ec2] - Connection is not available, request timed out after 5004ms.
> at com.zaxxer.hikari.pool.HikariPool.createTimeoutException(HikariPool.java:696)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:197)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:162)
> at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:128)
> at org.apache.nifi.dbcp.HikariCPConnectionPool.getConnection(HikariCPConnectionPool.java:354)
> ... 18 common frames omitted
> Caused by: java.sql.SQLRecoverableException: IO Error: Invalid Operation, NOT Connected
> at oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9395)
> at oracle.jdbc.driver.PhysicalConnection.setNetworkTimeout(PhysicalConnection.java:10000)
> at com.zaxxer.hikari.pool.PoolBase.setNetworkTimeout(PoolBase.java:566)
> at com.zaxxer.hikari.pool.PoolBase.isConnectionAlive(PoolBase.java:173)
> at com.zaxxer.hikari.pool.HikariPool.getConnection(HikariPool.java:186)
> ... 21 common frames omitted
> Caused by: oracle.net.ns.NetException: Invalid Operation, NOT Connected
> at oracle.net.nt.TcpNTAdapter.setOption(TcpNTAdapter.java:757)
> at oracle.net.ns.NSProtocol.setOption(NSProtocol.java:730)
> at oracle.net.ns.NSProtocol.setSocketReadTimeout(NSProtocol.java:1045)
> at oracle.jdbc.driver.T4CConnection.doSetNetworkTimeout(T4CConnection.java:9392)
> ... 25 common frames omitted
>
> Then I checked on the source code of the PutDatabaseRecord processor and found code that handles Throwable with If clause.
> I see that it only has "if(toAnalyze instanceof SQLTransientException)" but not the ProcessException, thus, fails to catch this exception to handle as retry relationship.
> So I would like to clarify why this error routes to failure or if this is a bug.
>
>
>
>
> Best Regard
> Nattida Pankiriya