You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by "Carlos Manuel Fernandes (DSI)" <ca...@cgd.pt> on 2016/07/21 18:22:42 UTC
Mantain Connection Pool Live
Hi all,
I have a DBCPConnectionPool to Netezza with 8 Connections, but if some connections go idle too much time, Netezza will throw :
"org.netezza.error.NzSQLException: FATAL 1: Connection Terminated - session timeout exceeded" when I attempt to retrieve one of them. After this that number of connections available decrease. I must restart the service to maintain all the connections.
The Connection Pooling Service don't handle to create a new connection?
I can resolve the problem creating a ExecuteScript processor to maintain the connections alive but I think a new property (keepAlive) on Pooling service was a better solution.
Carlos Fernandes
Full Exception:
2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7] o.a.nifi.processors.script.ExecuteScript
org.netezza.error.NzSQLException: FATAL 1: Connection Terminated - session timeout exceeded
at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280) ~[na:na]
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76) ~[na:na]
at org.netezza.sql.NzConnection.execute(NzConnection.java:2750) ~[na:na]
at org.netezza.sql.NzStatement._execute(NzStatement.java:849) ~[na:na]
at org.netezza.sql.NzStatement.execute(NzStatement.java:306) ~[na:na]
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[na:na]
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[na:na]
at groovy.sql.Sql.execute(Sql.java:2272) ~[groovy-all-2.4.5.jar:2.4.5]
at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) ~[groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) ~[groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125) ~[groovy-all-2.4.5.jar:2.4.5]
at Script1607.run(Script1607.groovy:32) ~[na:na]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:352) [groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:153) [groovy-all-2.4.5.jar:2.4.5]
at javax.script.AbstractScriptEngine.eval(Unknown Source) [na:1.7.0_67]
at org.apache.nifi.processors.script.impl.GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53) [nifi-scripting-processors-0.7.0.jar:0.7.0]
at org.apache.nifi.processors.script.ExecuteScript.onTrigger(ExecuteScript.java:201) [nifi-scripting-processors-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.FutureTask.runAndReset(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.7.0_67]
at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7] groovy.sql.Sql Failed to execute: rollback because: The action you have attempted has caused the back end to close the connection.
Re: Mantain Connection Pool Live
Posted by Toivo Adams <to...@gmail.com>.
Hi Carlos,
Yes, we can add only one property – validationQuery.
And when validationQuery is provided this means connections should be
validated.
This in turn should give us dropping invalid connections and create new
ones.
Thanks
Toivo
2016-07-25 16:37 GMT+03:00 Carlos Manuel Fernandes (DSI) <
carlos.antonio.fernandes@cgd.pt>:
> HI Toivos,
>
>
>
> if I request a connection and there isn’t a free one in the pool , a new
> one is created if the maximum wasn’t reached, it’s the normal function,
> right?
>
> If all the connections (max) are created on startup of the service we
> need to create new connections if some become invalid, but this isn’t the
> case, right?
>
>
>
> If the former is correct, Probably its simpler to have just the property
> for validation query and drop the invalid connections.
>
>
>
> Thanks
>
> Carlos
>
>
>
>
>
>
>
>
>
>
>
> *From:* Toivo Adams [mailto:toivo.adams@gmail.com]
> *Sent:* domingo, 24 de Julho de 2016 17:34
>
> *To:* users@nifi.apache.org
> *Subject:* Re: Mantain Connection Pool Live
>
>
>
> Hi Carlos,
>
> You are right, as far I know there isn't single validation query for all
> databases.
>
> It seems we must add 2 Property's to DBCPConnectionPool service which user
> must fill.
>
> 1. Do you want drop invalid connections and create new ones automatically
> (which might affect performance little bit).
>
> 2. Validation query.
>
>
> Thanks
> Toivo
>
>
>
> 2016-07-24 19:01 GMT+03:00 Carlos Manuel Fernandes (DSI) <
> carlos.antonio.fernandes@cgd.pt>:
>
> Hi Toivo, Thanks for you reply.
>
>
>
> I opened a Jira ticket NIFI-2381 (
> https://issues.apache.org/jira/browse/NIFI-2381)
>
>
>
> Its good to Know Apache commons DBC P already has this capability,
> unfortunately as I know there isn’t a universal simple validation query,
> its database specific. I obtain this list in stackoverflow (
> http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases
> ):
>
>
>
> Oracle - select 1 from dual
>
> DB2 - select 1 from sysibm.sysdummy1
>
> mysql - select 1
>
> microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
>
> postgresql - select 1
>
> Netezza – select 1
>
> Informix - select 1 from systables
>
> ingres - select 1
>
> derby - values 1
>
> H2 - select 1
>
> Firebird - select 1 from rdb$database
>
> hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
>
>
>
> I had test myself Oracle, Db2, microsoft SQL Server , Netezza, the others
> probably are correct too.
>
>
>
> Thanks
>
>
>
> Carlos Fernandes
>
>
>
>
>
>
>
> *From:* Toivo Adams [mailto:toivo.adams@gmail.com]
> *Sent:* sábado, 23 de Julho de 2016 13:20
> *To:* users@nifi.apache.org
> *Subject:* Re: Mantain Connection Pool Live
>
>
>
> Hi Carlos,
>
> I think we should add capability dropping invalid connections and create
> new ones to DBCPConnectionPool.
>
> DBCPConnectionPool uses internally Apache commons DBCP and DBCP itself
> supports dropping invalid connections.
>
> See testOnBorrow
>
> http://commons.apache.org/proper/commons-dbcp/api-1.4/src-html/org/apache/commons/dbcp/BasicDataSource.html#line.506
>
> Also validationQuery is need to be set up.
>
> Please can you create Jira ticket.
>
> Thanks
> Toivo
>
>
>
> 2016-07-21 21:22 GMT+03:00 Carlos Manuel Fernandes (DSI) <
> carlos.antonio.fernandes@cgd.pt>:
>
> Hi all,
>
>
>
> I have a DBCPConnectionPool to Netezza with 8 Connections, but if some
> connections go idle too much time, Netezza will throw :
>
> “org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded” when I attempt to retrieve one of them. After
> this that number of connections available decrease. I must restart the
> service to maintain all the connections.
>
>
>
> The Connection Pooling Service don’t handle to create a new connection?
>
>
>
> I can resolve the problem creating a ExecuteScript processor to maintain
> the connections alive but I think a new property (keepAlive) on Pooling
> service was a better solution.
>
>
>
> Carlos Fernandes
>
>
>
>
>
> Full Exception:
>
>
>
> 2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7]
> o.a.nifi.processors.script.ExecuteScript
>
> org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded
>
>
>
> at
> org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)
> ~[na:na]
>
> at
> org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76) ~[na:na]
>
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2750)
> ~[na:na]
>
> at org.netezza.sql.NzStatement._execute(NzStatement.java:849)
> ~[na:na]
>
> at org.netezza.sql.NzStatement.execute(NzStatement.java:306)
> ~[na:na]
>
> at
> org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at
> org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at groovy.sql.Sql.execute(Sql.java:2272)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
>
> at
> org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at Script1607.run(Script1607.groovy:32) ~[na:na]
>
> at
> org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:352)
> [groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:153)
> [groovy-all-2.4.5.jar:2.4.5]
>
> at javax.script.AbstractScriptEngine.eval(Unknown Source)
> [na:1.7.0_67]
>
> at
> org.apache.nifi.processors.script.impl.GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.processors.script.ExecuteScript.onTrigger(ExecuteScript.java:201)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
> [na:1.7.0_67]
>
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown
> Source) [na:1.7.0_67]
>
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
>
> 2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7]
> groovy.sql.Sql Failed to execute: rollback because: The action you have
> attempted has caused the back end to close the connection.
>
>
>
>
>
>
>
>
>
>
>
>
>
RE: Mantain Connection Pool Live
Posted by "Carlos Manuel Fernandes (DSI)" <ca...@cgd.pt>.
HI Toivos,
if I request a connection and there isn’t a free one in the pool , a new one is created if the maximum wasn’t reached, it’s the normal function, right?
If all the connections (max) are created on startup of the service we need to create new connections if some become invalid, but this isn’t the case, right?
If the former is correct, Probably its simpler to have just the property for validation query and drop the invalid connections.
Thanks
Carlos
From: Toivo Adams [mailto:toivo.adams@gmail.com]
Sent: domingo, 24 de Julho de 2016 17:34
To: users@nifi.apache.org
Subject: Re: Mantain Connection Pool Live
Hi Carlos,
You are right, as far I know there isn't single validation query for all databases.
It seems we must add 2 Property's to DBCPConnectionPool service which user must fill.
1. Do you want drop invalid connections and create new ones automatically (which might affect performance little bit).
2. Validation query.
Thanks
Toivo
2016-07-24 19:01 GMT+03:00 Carlos Manuel Fernandes (DSI) <ca...@cgd.pt>>:
Hi Toivo, Thanks for you reply.
I opened a Jira ticket NIFI-2381 (https://issues.apache.org/jira/browse/NIFI-2381)
Its good to Know Apache commons DBC P already has this capability, unfortunately as I know there isn’t a universal simple validation query, its database specific. I obtain this list in stackoverflow (http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases):
Oracle - select 1 from dual
DB2 - select 1 from sysibm.sysdummy1
mysql - select 1
microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
postgresql - select 1
Netezza – select 1
Informix - select 1 from systables
ingres - select 1
derby - values 1
H2 - select 1
Firebird - select 1 from rdb$database
hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
I had test myself Oracle, Db2, microsoft SQL Server , Netezza, the others probably are correct too.
Thanks
Carlos Fernandes
From: Toivo Adams [mailto:toivo.adams@gmail.com<ma...@gmail.com>]
Sent: sábado, 23 de Julho de 2016 13:20
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Mantain Connection Pool Live
Hi Carlos,
I think we should add capability dropping invalid connections and create new ones to DBCPConnectionPool.
DBCPConnectionPool uses internally Apache commons DBCP and DBCP itself supports dropping invalid connections.
See testOnBorrow
http://commons.apache.org/proper/commons-dbcp/api-1.4/src-html/org/apache/commons/dbcp/BasicDataSource.html#line.506
Also validationQuery is need to be set up.
Please can you create Jira ticket.
Thanks
Toivo
2016-07-21 21:22 GMT+03:00 Carlos Manuel Fernandes (DSI) <ca...@cgd.pt>>:
Hi all,
I have a DBCPConnectionPool to Netezza with 8 Connections, but if some connections go idle too much time, Netezza will throw :
“org.netezza.error.NzSQLException: FATAL 1: Connection Terminated - session timeout exceeded” when I attempt to retrieve one of them. After this that number of connections available decrease. I must restart the service to maintain all the connections.
The Connection Pooling Service don’t handle to create a new connection?
I can resolve the problem creating a ExecuteScript processor to maintain the connections alive but I think a new property (keepAlive) on Pooling service was a better solution.
Carlos Fernandes
Full Exception:
2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7] o.a.nifi.processors.script.ExecuteScript
org.netezza.error.NzSQLException: FATAL 1: Connection Terminated - session timeout exceeded
at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280) ~[na:na]
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76) ~[na:na]
at org.netezza.sql.NzConnection.execute(NzConnection.java:2750) ~[na:na]
at org.netezza.sql.NzStatement._execute(NzStatement.java:849) ~[na:na]
at org.netezza.sql.NzStatement.execute(NzStatement.java:306) ~[na:na]
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[na:na]
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[na:na]
at groovy.sql.Sql.execute(Sql.java:2272) ~[groovy-all-2.4.5.jar:2.4.5]
at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) ~[groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) ~[groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125) ~[groovy-all-2.4.5.jar:2.4.5]
at Script1607.run(Script1607.groovy:32) ~[na:na]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:352) [groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:153) [groovy-all-2.4.5.jar:2.4.5]
at javax.script.AbstractScriptEngine.eval(Unknown Source) [na:1.7.0_67]
at org.apache.nifi.processors.script.impl.GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53) [nifi-scripting-processors-0.7.0.jar:0.7.0]
at org.apache.nifi.processors.script.ExecuteScript.onTrigger(ExecuteScript.java:201) [nifi-scripting-processors-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.FutureTask.runAndReset(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.7.0_67]
at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7] groovy.sql.Sql Failed to execute: rollback because: The action you have attempted has caused the back end to close the connection.
Re: Mantain Connection Pool Live
Posted by Toivo Adams <to...@gmail.com>.
Hi Carlos,
You are right, as far I know there isn't single validation query for all
databases.
It seems we must add 2 Property's to DBCPConnectionPool service which user
must fill.
1. Do you want drop invalid connections and create new ones automatically
(which might affect performance little bit).
2. Validation query.
Thanks
Toivo
2016-07-24 19:01 GMT+03:00 Carlos Manuel Fernandes (DSI) <
carlos.antonio.fernandes@cgd.pt>:
> Hi Toivo, Thanks for you reply.
>
>
>
> I opened a Jira ticket NIFI-2381 (
> https://issues.apache.org/jira/browse/NIFI-2381)
>
>
>
> Its good to Know Apache commons DBC P already has this capability,
> unfortunately as I know there isn’t a universal simple validation query,
> its database specific. I obtain this list in stackoverflow (
> http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases
> ):
>
>
>
> Oracle - select 1 from dual
>
> DB2 - select 1 from sysibm.sysdummy1
>
> mysql - select 1
>
> microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
>
> postgresql - select 1
>
> Netezza – select 1
>
> Informix - select 1 from systables
>
> ingres - select 1
>
> derby - values 1
>
> H2 - select 1
>
> Firebird - select 1 from rdb$database
>
> hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
>
>
>
> I had test myself Oracle, Db2, microsoft SQL Server , Netezza, the others
> probably are correct too.
>
>
>
> Thanks
>
>
>
> Carlos Fernandes
>
>
>
>
>
>
>
> *From:* Toivo Adams [mailto:toivo.adams@gmail.com]
> *Sent:* sábado, 23 de Julho de 2016 13:20
> *To:* users@nifi.apache.org
> *Subject:* Re: Mantain Connection Pool Live
>
>
>
> Hi Carlos,
>
> I think we should add capability dropping invalid connections and create
> new ones to DBCPConnectionPool.
>
> DBCPConnectionPool uses internally Apache commons DBCP and DBCP itself
> supports dropping invalid connections.
>
> See testOnBorrow
>
> http://commons.apache.org/proper/commons-dbcp/api-1.4/src-html/org/apache/commons/dbcp/BasicDataSource.html#line.506
>
> Also validationQuery is need to be set up.
>
> Please can you create Jira ticket.
>
> Thanks
> Toivo
>
>
>
> 2016-07-21 21:22 GMT+03:00 Carlos Manuel Fernandes (DSI) <
> carlos.antonio.fernandes@cgd.pt>:
>
> Hi all,
>
>
>
> I have a DBCPConnectionPool to Netezza with 8 Connections, but if some
> connections go idle too much time, Netezza will throw :
>
> “org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded” when I attempt to retrieve one of them. After
> this that number of connections available decrease. I must restart the
> service to maintain all the connections.
>
>
>
> The Connection Pooling Service don’t handle to create a new connection?
>
>
>
> I can resolve the problem creating a ExecuteScript processor to maintain
> the connections alive but I think a new property (keepAlive) on Pooling
> service was a better solution.
>
>
>
> Carlos Fernandes
>
>
>
>
>
> Full Exception:
>
>
>
> 2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7]
> o.a.nifi.processors.script.ExecuteScript
>
> org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded
>
>
>
> at
> org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)
> ~[na:na]
>
> at
> org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76) ~[na:na]
>
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2750)
> ~[na:na]
>
> at org.netezza.sql.NzStatement._execute(NzStatement.java:849)
> ~[na:na]
>
> at org.netezza.sql.NzStatement.execute(NzStatement.java:306)
> ~[na:na]
>
> at
> org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at
> org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at groovy.sql.Sql.execute(Sql.java:2272)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
>
> at
> org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at Script1607.run(Script1607.groovy:32) ~[na:na]
>
> at
> org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:352)
> [groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:153)
> [groovy-all-2.4.5.jar:2.4.5]
>
> at javax.script.AbstractScriptEngine.eval(Unknown Source)
> [na:1.7.0_67]
>
> at
> org.apache.nifi.processors.script.impl.GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.processors.script.ExecuteScript.onTrigger(ExecuteScript.java:201)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
> [na:1.7.0_67]
>
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown
> Source) [na:1.7.0_67]
>
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
>
> 2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7]
> groovy.sql.Sql Failed to execute: rollback because: The action you have
> attempted has caused the back end to close the connection.
>
>
>
>
>
>
>
>
>
>
>
RE: Mantain Connection Pool Live
Posted by "Carlos Manuel Fernandes (DSI)" <ca...@cgd.pt>.
Hi Toivo, Thanks for you reply.
I opened a Jira ticket NIFI-2381 (https://issues.apache.org/jira/browse/NIFI-2381)
Its good to Know Apache commons DBC P already has this capability, unfortunately as I know there isn’t a universal simple validation query, its database specific. I obtain this list in stackoverflow (http://stackoverflow.com/questions/10684244/dbcp-validationquery-for-different-databases):
Oracle - select 1 from dual
DB2 - select 1 from sysibm.sysdummy1
mysql - select 1
microsoft SQL Server - select 1 (tested on SQL-Server 9.0, 10.5 [2008])
postgresql - select 1
Netezza – select 1
Informix - select 1 from systables
ingres - select 1
derby - values 1
H2 - select 1
Firebird - select 1 from rdb$database
hsqldb - select 1 from INFORMATION_SCHEMA.SYSTEM_USERS
I had test myself Oracle, Db2, microsoft SQL Server , Netezza, the others probably are correct too.
Thanks
Carlos Fernandes
From: Toivo Adams [mailto:toivo.adams@gmail.com]
Sent: sábado, 23 de Julho de 2016 13:20
To: users@nifi.apache.org
Subject: Re: Mantain Connection Pool Live
Hi Carlos,
I think we should add capability dropping invalid connections and create new ones to DBCPConnectionPool.
DBCPConnectionPool uses internally Apache commons DBCP and DBCP itself supports dropping invalid connections.
See testOnBorrow
http://commons.apache.org/proper/commons-dbcp/api-1.4/src-html/org/apache/commons/dbcp/BasicDataSource.html#line.506
Also validationQuery is need to be set up.
Please can you create Jira ticket.
Thanks
Toivo
2016-07-21 21:22 GMT+03:00 Carlos Manuel Fernandes (DSI) <ca...@cgd.pt>>:
Hi all,
I have a DBCPConnectionPool to Netezza with 8 Connections, but if some connections go idle too much time, Netezza will throw :
“org.netezza.error.NzSQLException: FATAL 1: Connection Terminated - session timeout exceeded” when I attempt to retrieve one of them. After this that number of connections available decrease. I must restart the service to maintain all the connections.
The Connection Pooling Service don’t handle to create a new connection?
I can resolve the problem creating a ExecuteScript processor to maintain the connections alive but I think a new property (keepAlive) on Pooling service was a better solution.
Carlos Fernandes
Full Exception:
2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7] o.a.nifi.processors.script.ExecuteScript
org.netezza.error.NzSQLException: FATAL 1: Connection Terminated - session timeout exceeded
at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280) ~[na:na]
at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76) ~[na:na]
at org.netezza.sql.NzConnection.execute(NzConnection.java:2750) ~[na:na]
at org.netezza.sql.NzStatement._execute(NzStatement.java:849) ~[na:na]
at org.netezza.sql.NzStatement.execute(NzStatement.java:306) ~[na:na]
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[na:na]
at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264) ~[na:na]
at groovy.sql.Sql.execute(Sql.java:2272) ~[groovy-all-2.4.5.jar:2.4.5]
at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48) ~[groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113) ~[groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125) ~[groovy-all-2.4.5.jar:2.4.5]
at Script1607.run(Script1607.groovy:32) ~[na:na]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:352) [groovy-all-2.4.5.jar:2.4.5]
at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:153) [groovy-all-2.4.5.jar:2.4.5]
at javax.script.AbstractScriptEngine.eval(Unknown Source) [na:1.7.0_67]
at org.apache.nifi.processors.script.impl.GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53) [nifi-scripting-processors-0.7.0.jar:0.7.0]
at org.apache.nifi.processors.script.ExecuteScript.onTrigger(ExecuteScript.java:201) [nifi-scripting-processors-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.jar:0.7.0]
at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127) [nifi-framework-core-0.7.0.jar:0.7.0]
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.FutureTask.runAndReset(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) [na:1.7.0_67]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) [na:1.7.0_67]
at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7] groovy.sql.Sql Failed to execute: rollback because: The action you have attempted has caused the back end to close the connection.
Re: Mantain Connection Pool Live
Posted by Toivo Adams <to...@gmail.com>.
Hi Carlos,
I think we should add capability dropping invalid connections and create
new ones to DBCPConnectionPool.
DBCPConnectionPool uses internally Apache commons DBCP and DBCP itself
supports dropping invalid connections.
See testOnBorrow
http://commons.apache.org/proper/commons-dbcp/api-1.4/src-html/org/apache/commons/dbcp/BasicDataSource.html#line.506
Also validationQuery is need to be set up.
Please can you create Jira ticket.
Thanks
Toivo
2016-07-21 21:22 GMT+03:00 Carlos Manuel Fernandes (DSI) <
carlos.antonio.fernandes@cgd.pt>:
> Hi all,
>
>
>
> I have a DBCPConnectionPool to Netezza with 8 Connections, but if some
> connections go idle too much time, Netezza will throw :
>
> “org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded” when I attempt to retrieve one of them. After
> this that number of connections available decrease. I must restart the
> service to maintain all the connections.
>
>
>
> The Connection Pooling Service don’t handle to create a new connection?
>
>
>
> I can resolve the problem creating a ExecuteScript processor to maintain
> the connections alive but I think a new property (keepAlive) on Pooling
> service was a better solution.
>
>
>
> Carlos Fernandes
>
>
>
>
>
> Full Exception:
>
>
>
> 2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7]
> o.a.nifi.processors.script.ExecuteScript
>
> org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded
>
>
>
> at
> org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)
> ~[na:na]
>
> at
> org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76) ~[na:na]
>
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2750)
> ~[na:na]
>
> at org.netezza.sql.NzStatement._execute(NzStatement.java:849)
> ~[na:na]
>
> at org.netezza.sql.NzStatement.execute(NzStatement.java:306)
> ~[na:na]
>
> at
> org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at
> org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at groovy.sql.Sql.execute(Sql.java:2272)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
>
> at
> org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at Script1607.run(Script1607.groovy:32) ~[na:na]
>
> at
> org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:352)
> [groovy-all-2.4.5.jar:2.4.5]
>
> at
> org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(GroovyScriptEngineImpl.java:153)
> [groovy-all-2.4.5.jar:2.4.5]
>
> at javax.script.AbstractScriptEngine.eval(Unknown Source)
> [na:1.7.0_67]
>
> at
> org.apache.nifi.processors.script.impl.GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.processors.script.ExecuteScript.onTrigger(ExecuteScript.java:201)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1054)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:136)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.call(ContinuallyRunProcessorTask.java:47)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at
> org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
> [na:1.7.0_67]
>
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(Unknown
> Source) [na:1.7.0_67]
>
> at
> java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
>
> 2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7]
> groovy.sql.Sql Failed to execute: rollback because: The action you have
> attempted has caused the back end to close the connection.
>
>
>
>
>
>
>
>
>
Re: Mantain Connection Pool Live
Posted by Toivo Adams <to...@gmail.com>.
Hi Carlos,
Sorry, it took long time, but finally first version is done.
https://github.com/apache/nifi/pull/986
https://issues.apache.org/jira/browse/NIFI-2381
Please test.
Thanks
Toivo
2016-07-21 21:22 GMT+03:00 Carlos Manuel Fernandes (DSI) <
carlos.antonio.fernandes@cgd.pt>:
> Hi all,
>
>
>
> I have a DBCPConnectionPool to Netezza with 8 Connections, but if some
> connections go idle too much time, Netezza will throw :
>
> “org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded” when I attempt to retrieve one of them. After
> this that number of connections available decrease. I must restart the
> service to maintain all the connections.
>
>
>
> The Connection Pooling Service don’t handle to create a new connection?
>
>
>
> I can resolve the problem creating a ExecuteScript processor to maintain
> the connections alive but I think a new property (keepAlive) on Pooling
> service was a better solution.
>
>
>
> Carlos Fernandes
>
>
>
>
>
> Full Exception:
>
>
>
> 2016-07-21 15:00:10,258 ERROR [Timer-Driven Process Thread-7]
> o.a.nifi.processors.script.ExecuteScript
>
> org.netezza.error.NzSQLException: FATAL 1: Connection Terminated -
> session timeout exceeded
>
>
>
> at org.netezza.internal.QueryExecutor.getNextResult(QueryExecutor.java:280)
> ~[na:na]
>
> at org.netezza.internal.QueryExecutor.execute(QueryExecutor.java:76)
> ~[na:na]
>
> at org.netezza.sql.NzConnection.execute(NzConnection.java:2750)
> ~[na:na]
>
> at org.netezza.sql.NzStatement._execute(NzStatement.java:849)
> ~[na:na]
>
> at org.netezza.sql.NzStatement.execute(NzStatement.java:306)
> ~[na:na]
>
> at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
> ~[na:na]
>
> at groovy.sql.Sql.execute(Sql.java:2272)
> ~[groovy-all-2.4.5.jar:2.4.5]
>
> at groovy.sql.Sql$execute.call(Unknown Source) ~[na:na]
>
> at org.codehaus.groovy.runtime.callsite.CallSiteArray.
> defaultCall(CallSiteArray.java:48) ~[groovy-all-2.4.5.jar:2.4.5]
>
> at org.codehaus.groovy.runtime.callsite.AbstractCallSite.
> call(AbstractCallSite.java:113) ~[groovy-all-2.4.5.jar:2.4.5]
>
> at org.codehaus.groovy.runtime.callsite.AbstractCallSite.
> call(AbstractCallSite.java:125) ~[groovy-all-2.4.5.jar:2.4.5]
>
> at Script1607.run(Script1607.groovy:32) ~[na:na]
>
> at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(
> GroovyScriptEngineImpl.java:352) [groovy-all-2.4.5.jar:2.4.5]
>
> at org.codehaus.groovy.jsr223.GroovyScriptEngineImpl.eval(
> GroovyScriptEngineImpl.java:153) [groovy-all-2.4.5.jar:2.4.5]
>
> at javax.script.AbstractScriptEngine.eval(Unknown Source)
> [na:1.7.0_67]
>
> at org.apache.nifi.processors.script.impl.
> GroovyScriptEngineConfigurator.eval(GroovyScriptEngineConfigurator.java:53)
> [nifi-scripting-processors-0.7.0.jar:0.7.0]
>
> at org.apache.nifi.processors.script.ExecuteScript.
> onTrigger(ExecuteScript.java:201) [nifi-scripting-processors-0.
> 7.0.jar:0.7.0]
>
> at org.apache.nifi.controller.StandardProcessorNode.onTrigger(
> StandardProcessorNode.java:1054) [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.
> call(ContinuallyRunProcessorTask.java:136) [nifi-framework-core-0.7.0.
> jar:0.7.0]
>
> at org.apache.nifi.controller.tasks.ContinuallyRunProcessorTask.
> call(ContinuallyRunProcessorTask.java:47) [nifi-framework-core-0.7.0.
> jar:0.7.0]
>
> at org.apache.nifi.controller.scheduling.
> TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:127)
> [nifi-framework-core-0.7.0.jar:0.7.0]
>
> at java.util.concurrent.Executors$RunnableAdapter.call(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.FutureTask.runAndReset(Unknown Source)
> [na:1.7.0_67]
>
> at java.util.concurrent.ScheduledThreadPoolExecutor$
> ScheduledFutureTask.access$301(Unknown Source) [na:1.7.0_67]
>
> at java.util.concurrent.ScheduledThreadPoolExecutor$
> ScheduledFutureTask.run(Unknown Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown
> Source) [na:1.7.0_67]
>
> at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown
> Source) [na:1.7.0_67]
>
> at java.lang.Thread.run(Unknown Source) [na:1.7.0_67]
>
> 2016-07-21 15:00:10,258 WARN [Timer-Driven Process Thread-7]
> groovy.sql.Sql Failed to execute: rollback because: The action you have
> attempted has caused the back end to close the connection.
>
>
>
>
>
>
>
>
>