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

problem executing query on Microsoft SQL Server

gadeyne.bram Aug 23, 2016 9:55 AM

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,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>

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

<password>xxxxxxx</password>

</security>

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

</validation>

</xa-datasource>

The driver is configured like this.

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

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

</driver>

1. Re: problem executing query on Microsoft SQL Server

shawkins Aug 23, 2016 2:20 PM (in response to gadeyne.bram)

Seems like it's an issue on the SQL Server side. Here's a similar stack, but during recovery, XAException "SQLJDBC_XA DTC_ERROR" during MS SQL Server XA recovery in JBoss EAP - Red Hat Customer Portal
Actions
2. Re: problem executing query on Microsoft SQL Server

gadeyne.bram Aug 24, 2016 2:22 AM (in response to shawkins)

The problem is that the administrators of the sql server instance don't want to add the XA support. I've changed to a non-xa datasource and I'll see of the problem persists.
Actions

Go to original post