title: Exception 'Cannot get a connection, pool error Timeout waiting for idle object' when using 'DBCPConnectionPoolLookup' service in Nifi
body: <p>I'm trying to use 'DBCPConnectionPoolLookup' service in 'ExecuteGroovyScript' to dynamically query the required database based on 'database.name' parameter in the input flow file.</p>
  
  <p>The processor is successfully able to get the corresponding 'DBCPConnectionPool' service for querying but I'm getting the an exception <code>java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object</code>. As opposed to if I directly use the 'DBCPConnectionPool' service without the 'Lookup' service without changing any configuration it works fine.
  I access the service as follows:</p>
  
  <p><code>def clientDb = CTL.SQLLookupService.getConnection(flowFile.getAttributes())</code></p>
  
  <p>Then use the 'clientDb' object to query as:
  <code>clientDb.rows(timseriesSqlCountQuery).eachWithIndex { row, idx -&gt;numRowsTimeSeries= row.c}</code></p>
  
  <p>I have tried increasing the values of <code>Max Wait Time</code> and <code>Max Total Connections</code> to higher values in  'DBCPConnectionPool' service, it does not help. </p>
  
  <p>Please find below detail links of images for code,error and configuration</p>
  
  <ol>
  <li><a href="https://i.stack.imgur.com/xOJHZ.jpg" rel="nofollow noreferrer">Exception</a></li>
  <li><a href="https://i.stack.imgur.com/Hcwfx.jpg" rel="nofollow noreferrer">Configuration of 'ExecuteGroovyScript'</a></li>
  <li><a href="https://i.stack.imgur.com/SnDfD.jpg" rel="nofollow noreferrer">Configuration of 'DBCPConnectionPool' service</a></li>
  <li><a href="https://i.stack.imgur.com/29aXp.jpg" rel="nofollow noreferrer">Configuration of 'DBCPConnectionPoolLookup' service</a></li>
  </ol>
  
  <p>Script Code</p>
  
  <pre><code>import org.apache.nifi.distributed.cache.client.Deserializer
  import org.apache.nifi.distributed.cache.client.Serializer
  import org.apache.nifi.distributed.cache.client.exception.DeserializationException
  import org.apache.nifi.distributed.cache.client.exception.SerializationException
  import groovy.sql.Sql
  
  import java.time.*
  
  try {
      def flowFile = session.get()
  
      def isBootstrap=flowFile."isBootstrap"
  
      def timseriesSqlQuery='SELECT id FROM [dbo].[Points] where ([MappedToEquipment] = \'Mapped\' or PointStatus = \'Mapped\')'
      def timseriesSqlCountQuery='SELECT count(id) as c FROM [dbo].[Points] where ([MappedToEquipment] = \'Mapped\' or PointStatus = \'Mapped\')'
  
      def spaceSqlQuery='select id from (select id from dbo.organization union select id from dbo.facility union select id from dbo.building union select id  from dbo.floor union select id from dbo.wing union select id from dbo.room union select id from dbo.systems) tmp'
      def spaceSqlCountQuery='select count(id) as c from (select id from dbo.organization union select id from dbo.facility union select id from dbo.building union select id  from dbo.floor union select id from dbo.wing union select id from dbo.room union select id from dbo.systems) tmp'
  
  
      def cache = CTL.lastIngestTimeMap
  
      def clientDb = CTL.SQLLookupService.getConnection(flowFile.getAttributes())//SQL.staticService
  
      int numRowsTimeSeries=0
      int numRowsSpace=0
  
      clientDb.rows(timseriesSqlCountQuery).eachWithIndex { row, idx -&gt;numRowsTimeSeries= row.c}
      clientDb.rows(spaceSqlCountQuery).eachWithIndex { row, idx -&gt;numRowsSpace= row.c}
  }
  </code></pre>
  
  <p>Exception from Nifi logs</p>
  
  <pre><code>2019-09-12 06:18:33,629 ERROR [Timer-Driven Process Thread-3] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=b435c079-ee6c-3c42-a6ea-020968267ecf] ExecuteGroovyScript[id=b435c079-ee6c-3c42-a6ea-020968267ecf] failed to process session due to java.lang.ClassCastException; Processor Administratively Yielded for 1 sec: java.lang.ClassCastException
  java.lang.ClassCastException: null
  2019-09-12 06:18:33,629 WARN [Timer-Driven Process Thread-3] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ExecuteGroovyScript[id=b435c079-ee6c-3c42-a6ea-020968267ecf] due to uncaught Exception: java.lang.ClassCastException
  java.lang.ClassCastException: null
  2019-09-12 06:18:33,629 ERROR [Timer-Driven Process Thread-9] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=9b81ca15-93a5-3953-9f40-d0874cfe2531] ExecuteGroovyScript[id=9b81ca15-93a5-3953-9f40-d0874cfe2531] failed to process session due to java.lang.ClassCastException; Processor Administratively Yielded for 1 sec: java.lang.ClassCastException
  java.lang.ClassCastException: null
  2019-09-12 06:18:33,629 WARN [Timer-Driven Process Thread-9] o.a.n.controller.tasks.ConnectableTask Administratively Yielding ExecuteGroovyScript[id=9b81ca15-93a5-3953-9f40-d0874cfe2531] due to uncaught Exception: java.lang.ClassCastException
  java.lang.ClassCastException: null
  2019-09-12 06:18:33,708 ERROR [Timer-Driven Process Thread-10] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=a1ec4496-dca3-38ab-a47b-43d7ff95e40f] org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
  org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
          at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:308)
          at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
          at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:498)
          at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:84)
          at com.sun.proxy.$Proxy89.getConnection(Unknown Source)
          at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onInitSQL(ExecuteGroovyScript.java:339)
          at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:439)
          at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
          at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
          at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
          at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
          at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
          at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
          at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
          at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
          at java.lang.Thread.run(Thread.java:748)
  Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
          at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
          at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
          at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:305)
          ... 19 common frames omitted
  Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
          at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:451)
          at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:365)
          at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
          ... 21 common frames omitted
  2019-09-12 06:18:33,708 ERROR [Timer-Driven Process Thread-2] o.a.n.p.groovyx.ExecuteGroovyScript ExecuteGroovyScript[id=54d1e251-88f2-33f3-0489-722879a802bd] org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object: org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
  org.apache.nifi.processor.exception.ProcessException: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
          at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:308)
          at org.apache.nifi.dbcp.DBCPService.getConnection(DBCPService.java:49)
          at sun.reflect.GeneratedMethodAccessor106.invoke(Unknown Source)
          at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
          at java.lang.reflect.Method.invoke(Method.java:498)
          at org.apache.nifi.controller.service.StandardControllerServiceInvocationHandler.invoke(StandardControllerServiceInvocationHandler.java:84)
          at com.sun.proxy.$Proxy89.getConnection(Unknown Source)
          at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onInitSQL(ExecuteGroovyScript.java:339)
          at org.apache.nifi.processors.groovyx.ExecuteGroovyScript.onTrigger(ExecuteGroovyScript.java:439)
          at org.apache.nifi.processor.AbstractProcessor.onTrigger(AbstractProcessor.java:27)
          at org.apache.nifi.controller.StandardProcessorNode.onTrigger(StandardProcessorNode.java:1165)
          at org.apache.nifi.controller.tasks.ConnectableTask.invoke(ConnectableTask.java:203)
          at org.apache.nifi.controller.scheduling.TimerDrivenSchedulingAgent$1.run(TimerDrivenSchedulingAgent.java:117)
          at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
          at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:308)
          at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$301(ScheduledThreadPoolExecutor.java:180)
          at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:294)
          at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
          at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
          at java.lang.Thread.run(Thread.java:748)
  Caused by: java.sql.SQLException: Cannot get a connection, pool error Timeout waiting for idle object
          at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:142)
          at org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:1563)
          at org.apache.nifi.dbcp.DBCPConnectionPool.getConnection(DBCPConnectionPool.java:305)
          ... 19 common frames omitted
  Caused by: java.util.NoSuchElementException: Timeout waiting for idle object
          at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:451)
          at org.apache.commons.pool2.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:365)
          at org.apache.commons.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134)
          ... 21 common frames omitted
  
  </code></pre>