2 Replies Latest reply on Aug 24, 2016 2:22 AM by gadeyne.bram

    problem executing query on Microsoft SQL Server

    gadeyne.bram Master

      Hi,

       

      I'm not sure that this problem is caused by teiid so I thought to first discuss this here.

       

      I have a query that connects to sql server. It is called from an java ee 7 application that runs on a wildfly server. The application executes several scheduled connection simultaneous using a pool of teiid connections.

       

      The strange thing is that only the queries that connect to Microsoft SQL Server fail with the exception com.microsoft.sqlserver.jdbc.SQLServerException: The connection is closed. and the following stack trace:

       

      at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:742)

              at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(SQLServerXAResource.java:774)

              at org.jboss.jca.adapters.jdbc.xa.XAManagedConnection.start(XAManagedConnection.java:259)

              at org.jboss.jca.core.tx.jbossts.XAResourceWrapperImpl.start(XAResourceWrapperImpl.java:215)

              at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:662)

              at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:423)

              at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.enlist(TxConnectionListener.java:1020)

              at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:394)

              at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:554)

              at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:922)

              at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:740)

              at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)

              at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:67)

              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:279)

              at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:68)

              at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:203)

              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:329)

              at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)

              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

              at java.lang.reflect.Method.invoke(Method.java:497)

              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

              at com.sun.proxy.$Proxy57.execute(Unknown Source)

              at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

              at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)

              at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:394)

              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

              at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)

              at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)

              at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)

              at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)

              at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:164)

              at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:227)

              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

              at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:248)

              at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:229)

              at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:203)

              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

              at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:104)

              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

              at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:94)

              at org.teiid.query.processor.relational.GroupingNode.groupSortPhase(GroupingNode.java:490)

              at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:366)

              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

              at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)

              at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

              at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

              at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

              at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

              at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

      at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

              at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472)

              at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)

              at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

              at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

              at java.lang.Thread.run(Thread.java:745)

       

       

      2016-08-23 14:00:00,275 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW javax.transaction.SystemException: TransactionImple.enlistResource - XAResource.start ARJUNA016054: could not register transaction: < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffff7f000101:-4b06b63a:57bb0c7f:4913, node_name=1, branch_uid=0:ffff7f000101:-4b06b63a:57bb0c7f:4951, subordinatenodename=null, eis_name=java:/prestiz2 >

      2016-08-23 14:00:00,275 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:713)

      2016-08-23 14:00:00,275 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:423)

      2016-08-23 14:00:00,276 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.enlist(TxConnectionListener.java:1020)

      2016-08-23 14:00:00,276 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener.enlist(TxConnectionListener.java:394)

      2016-08-23 14:00:00,276 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.jca.core.connectionmanager.tx.TxConnectionManagerImpl.managedConnectionReconnected(TxConnectionManagerImpl.java:554)

      2016-08-23 14:00:00,277 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.reconnectManagedConnection(AbstractConnectionManager.java:922)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.jca.core.connectionmanager.AbstractConnectionManager.allocateConnection(AbstractConnectionManager.java:740)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.jca.adapters.jdbc.WrapperDataSource.getConnection(WrapperDataSource.java:138)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.jboss.as.connector.subsystems.datasources.WildFlyDataSource.getConnection(WildFlyDataSource.java:67)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:279)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.translator.jdbc.JDBCExecutionFactory.getConnection(JDBCExecutionFactory.java:68)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.translator.ExecutionFactory.getConnection(ExecutionFactory.java:203)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:329)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at sun.reflect.GeneratedMethodAccessor111.invoke(Unknown Source)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at java.lang.reflect.Method.invoke(Method.java:497)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at com.sun.proxy.$Proxy57.execute(Unknown Source)

      2016-08-23 14:00:00,278 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:306)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.DataTierTupleSource.nextTuple(DataTierTupleSource.java:142)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.AccessNode.nextBatchDirect(AccessNode.java:394)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchIterator.finalRow(BatchIterator.java:69)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.common.buffer.AbstractTupleSource.getCurrentTuple(AbstractTupleSource.java:70)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchIterator.getCurrentTuple(BatchIterator.java:84)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.common.buffer.AbstractTupleSource.hasNext(AbstractTupleSource.java:92)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.MergeJoinStrategy.process(MergeJoinStrategy.java:164)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:227)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

      2016-08-23 14:00:00,279 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.SourceState.getTupleBuffer(SourceState.java:248)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.EnhancedSortMergeJoinStrategy.loadLeft(EnhancedSortMergeJoinStrategy.java:229)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.JoinNode.nextBatchDirect(JoinNode.java:203)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.SelectNode.nextBatchDirect(SelectNode.java:104)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchCollector$BatchProducerTupleSource.nextTuple(BatchCollector.java:94)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.GroupingNode.groupSortPhase(GroupingNode.java:490)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.GroupingNode.nextBatchDirect(GroupingNode.java:366)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.ProjectNode.nextBatchDirect(ProjectNode.java:150)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalNode.nextBatch(RelationalNode.java:282)

      2016-08-23 14:00:00,280 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.relational.RelationalPlan.nextBatch(RelationalPlan.java:145)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.QueryProcessor.nextBatchDirect(QueryProcessor.java:151)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.QueryProcessor.nextBatch(QueryProcessor.java:114)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:164)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.query.processor.BatchCollector.collectTuples(BatchCollector.java:146)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.RequestWorkItem.processMore(RequestWorkItem.java:472)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:348)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:51)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.RequestWorkItem.run(RequestWorkItem.java:274)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)

      2016-08-23 14:00:00,281 ERROR [stderr] (Worker1647_QueryProcessorQueue11331) bWez1L9VwVsW       at java.lang.Thread.run(Thread.java:745)

       

      The SQL server connection is configured like this:

       

      <xa-datasource jndi-name="java:/prestiz2" pool-name="prestiz2" enabled="true">

                          <xa-datasource-property name="ServerName">

                              db-prd-prestiz

                          </xa-datasource-property>

                          <xa-datasource-property name="PortNumber">

                              1433

                          </xa-datasource-property>

                          <xa-datasource-property name="DatabaseName">

                              PrestIZ2

                          </xa-datasource-property>

                          <driver>sqlserver2012</driver>

                          <xa-pool>

                              <min-pool-size>0</min-pool-size>

                              <max-pool-size>10</max-pool-size>

                          </xa-pool>

                          <security>

                              <user-name>xxxxxxx</user-name>

                              <password>xxxxxxx</password>

                          </security>

                          <validation>

                              <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.mssql.MSSQLValidConnectionChecker"/>

                          </validation>

                      </xa-datasource>

       

      The driver is configured like this.

       

      <driver name="sqlserver2012" module="com.microsoft.sqlserver.jdbc">

                              <driver-class>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class>

                              <xa-datasource-class>com.microsoft.sqlserver.jdbc.SQLServerXADataSource</xa-datasource-class>

                          </driver>