3 Replies Latest reply on Sep 12, 2014 11:38 AM by Steven Hawkins

    Procedure execution with 2 subqueries as parameters seems to eat up all connections

    Alex K. Newbie

      I have a JDBC datasource (MySQL) with a table defined like following:

       

      CREATE TABLE `test1` (
        `salesorderid` INTEGER(11) DEFAULT NULL,
        `orderdate` DATETIME DEFAULT NULL
      )ENGINE=InnoDB CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
      INSERT INTO `test1` (`salesorderid`, `orderdate`) VALUES 
        (1,'2008-09-01 00:00:00'),
        (2,'2008-09-01 00:00:00'),
        (3,'2008-06-01 00:00:00'),
        (4,'2009-05-01 00:00:00'),
        (5,'2009-08-01 00:00:00'),
        (6,'2010-11-01 00:00:00'),
        (7,'2010-04-01 00:00:00'),
        (8,'2011-07-01 00:00:00'),
        (9,'2011-07-01 00:00:00'),
        (10,'2012-07-01 00:00:00');
      COMMIT;
      
      

       

      The datasource is connected like this:

      <datasource jndi-name="java:/my1" pool-name="my1" use-java-context="true">
          <connection-url>jdbc:mysql://localhost/db1?useCursorFetch=true&amp;defaultFetchSize=1000&amp;zeroDateTimeBehavior=convertToNull</connection-url>
          <driver>mysql</driver>
          <new-connection-sql>set SESSION sql_mode = 'ANSI'</new-connection-sql>
          <pool>
              <min-pool-size>2</min-pool-size>
              <max-pool-size>70</max-pool-size>
              <prefill>false</prefill>
              <use-strict-min>false</use-strict-min>
              <flush-strategy>FailingConnectionOnly</flush-strategy>
          </pool>
          <security>
              <user-name>user</user-name>
              <password>password</password>
          </security>
          <validation>
              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLValidConnectionChecker"/>
              <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.mysql.MySQLExceptionSorter"/>
          </validation>
          <timeout>
              <blocking-timeout-millis>120000</blocking-timeout-millis>
              <idle-timeout-minutes>5</idle-timeout-minutes>
          </timeout>
      </datasource>
      
      

       

      In a VDB I have a physical model my1 for the datasource and a virtual model v1 with a procedure defined in it:

       

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <vdb name="vdb1" version="1">
          <description>VDB1</description>
          <property name="UseConnectorMetadata" value="true" />
          <model name="my1">
              <property name="importer.useFullSchemaName" value="false"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
              <source name="my1" translator-name="mysql" connection-jndi-name="java:/my1"/>
          </model>
          <model visible = "true" type = "VIRTUAL" name = "v1">
              <metadata type = "DDL"><![CDATA[
      CREATE VIRTUAL PROCEDURE sp_dateaxis(IN startdate timestamp, IN enddate timestamp) RETURNS (xdate timestamp) AS
      BEGIN
      DECLARE timestamp dateToAdd;
      dateToAdd = startdate;
      CREATE LOCAL TEMPORARY TABLE #tDates (xdate timestamp);
      WHILE(dateToAdd <= enddate)
      BEGIN
      INSERT INTO #tDates (#tDates.xdate) SELECT dateToAdd;
      dateToadd = timestampadd(SQL_TSI_DAY, 1, dateToAdd);
      END
      SELECT * FROM #tDates;
      END
                  ]]>
              </metadata>
          </model>
      </vdb>
      
      

       

      If I then execute the following query:

      select * from (exec v1.sp_dateaxis
      (startdate=>(SELECT MIN(a.orderdate)
      FROM my1.test1 as a)
      ,enddate=>(SELECT MAX(a.orderdate)
      FROM my1.test1 as a)
      )) as a
      
      

       

      the system thinks several minutes and then fails with the following exception:

       

      WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue295) bHn9AY25kqma Connector worker process failed for atomic-request=bHn9AY25kqma.7.1.140: org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/my1
       at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:285)
       at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:57)
       at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:186) [teiid-api-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:290) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334) [rt.jar:1.7.0_25]
       at java.util.concurrent.FutureTask.run(FutureTask.java:166) [rt.jar:1.7.0_25]
       at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214) [teiid-engine-8.9.0.Alpha2.jar:8.9.0.Alpha2]
       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_25]
       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_25]
       at java.lang.Thread.run(Thread.java:724) [rt.jar:1.7.0_25]
      Caused by: java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/my1
       at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:147)
       at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:281)
       ... 15 more
      Caused by: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/my1
       at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:390) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.getManagedConnection(TxConnectionManagerImpl.java:368) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:464) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:139)
       ... 16 more
      Caused by: javax.resource.ResourceException: IJ000655: No managed connections available within configured blocking timeout (120000 [ms])
       at org.jboss.jca.core.connectionmanager.pool.mcp.SemaphoreArrayListManagedConnectionPool.getConnection(SemaphoreArrayListManagedConnectionPool.java:384) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getSimpleConnection(AbstractPool.java:397) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       at org.jboss.jca.core.connectionmanager.pool.AbstractPool.getConnection(AbstractPool.java:365) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.getManagedConnection(AbstractConnectionManager.java:329) [ironjacamar-core-impl-1.0.15.Final.jar:1.0.15.Final]
       ... 19 more
      17:17:23,254 WARN  [org.teiid.PROCESSOR] (Worker2_QueryProcessorQueue296) bHn9AY25kqma TEIID30020 Processing exception for request bHn9AY25kqma.7 'TEIID30328 Unable to evaluate (SELECT anon_grp0.agg0 AS expr1 FROM test_tables.test_f AS a): TEIID30504 test_tables: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000453: Unable to get managed connection for java:/my1'. Originally ExpressionEvaluationException 'IJ000655: No managed connections available within configured blocking timeout (120000 [ms])' SemaphoreArrayListManagedConnectionPool.java:384. Enable more detailed logging to see the entire stacktrace.
      
      

       

      If I replace any subquery with a constant, it runs instantly and successfully.