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.ad...@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.fernan...@cgd.pt<mailto:carlos.antonio.fernan...@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.
