Teiid standalone - SQL Server insert into error

[mtawk]

We are encountering an error when using insert into select under SQL Server.

The source table and the destination table are from 2 different databases under the same SQL Server.

The exception is reproduced in the method org.teiid.translator.jdbc.JDBCBaseExecution.getPreparedStatement(String sql)

Attached is a simple test query along with exception stack.

Note that this issue is only reproduced with Teiid standalone, the same query executes successfully using Teiid embedded.

[rareddy]

The standalone server by default is configured with the transaction manager, which is trying to wrap a transaction around both data sources and failing since they may be not XA sources. I suspect in embedded you did not configure a transaction manager.

[mtawk]

We have put both sources as XA and the issue persists.

[rareddy]

Are you running into any transaction timeout issues perhaps?

[mtawk]

Concerning transaction timeout, the issue is reproduced systematically and directly after execution with a small select query result.

Otherwise, we have set manually jta to false in teiid-standalone.xml and it executed successfully

<datasource jta="false" jndi-name="java:/Model_BR-ds" pool-name="Model_BR-ds">

But we are using AdminAPI to create our data sources and deploy our VDB. And we were not able to set jta to false because jta is listed into excludedProperty method org.teiid.adminapi.jboss.AdminFactory.AdminImpl.excludeProperty.

Is it a good approach to set jta property to false and remove it from excludedProperty list?

[rareddy]

Setting JTA=false is also same as turning off transactions.

What is an error on the server side with your small batch?

[mtawk]

The error with small batch is the same sent in the attached file.

We find out that this issue is not only related to SQL Server and it is systematically reproduced when the source table has a different datasource from the destination table, regardless of DB server type. We managed to reproduce the same issue with MySQL and Oracle.

[rareddy]

Unfortunately with the logs that is attached there is no way to figure out why your system is marked for "rollback", look through the whole log there must be something else logged as to why the transaction is rolled back

[mtawk]

You find attached server.log for the detailed logs while executing the query

[rareddy]

Per logs, I see that you configured both XA data sources, however, they are both being registering as "Last Resource Records" and thus the error, which is only needed when only one of the data sources is non-xa and others are XA. So, there is something else with your data source configuration may be at fault here. Can you post you <datasource> segments for both oracle and mysql data sources in the standalone.xml file?

[mtawk]

You find attached the datasource segments from standalone.xml

[rareddy]

Those are not correct, you did not create XA data sources at all. See the "docs/teiid/datasources" directory for "oracle" and "mysql" directories and sample XA configurations in there. Replace with those and re-run your test.

[mtawk]

We have tried with XA data sources like the sample XA configurations but the problem persists.

You find attached the XA data sources from teiid-standalone.xml along with the server log files

[shawkins]

The relevant log statements:

Query start:

2019-08-20 09:23:06,456 INFO  [org.teiid.COMMAND_LOG] (NIO1) fVLa6nTScSci START USER COMMAND: startTime=2019-08-20 09:23:06.456 requestID=fVLa6nTScSci.4 txID=null sessionID=fVLa6nTScSci applicationName=JDBC principal=teiiduser vdbName=DomainOracleVDB vdbVersion=3 sql=INSERT INTO "ORCLModel_ib"."IMPLIFY_YOUSSEF"."VIEWADE46AE_sva_290719" ("VIEWADE46AE_sva_290719ID",

"COMPTE",

"GESTIONNAIRE",

"USERNAME")

SELECT 1,

"_COMPTE",

"_GESTIONNAIRE",

"_USERNAME"

We attempt to get the oracle connection from the pool and arjuna reports a problem:

2019-08-20 09:23:07,117 WARN  [org.jboss.jca.core.connectionmanager.listener.TxConnectionListener] (Worker3_QueryProcessorQueue30) fVLa6nTScSci IJ000305: Connection error occured: org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@4a22bad3[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3b3878c4 connection handles=0 lastReturned=1566282052056 lastValidated=1566282052056 lastCheckedOut=1566282187115 trackByTx=true pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@59b69c00 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@44a56732[pool=ORCLModel_ib-ds] xaResource=XAResourceWrapperImpl@e8724fa[xaResource=org.jboss.jca.adapters.jdbc.xa.XAManagedConnection@3b3878c4 pad=false overrideRmValue=true productName=Oracle productVersion=Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options jndiName=java:/ORCLModel_ib-ds] txSync=TransactionSynchronization@1276398898{tx=Local transaction (delegate=TransactionImple < ac, BasicAction: 0:ffffc0a8d301:3c1af496:5d5b9124:20 status: ActionStatus.RUNNING >, owner=Local transaction context for provider JBoss JTA transaction provider) wasTrackByTx=true enlisted=false cancel=false}]: oracle.jdbc.xa.OracleXAException

The underlying problem seems to be:

2019-08-20 09:23:07,117 WARN  [com.arjuna.ats.jta] (Worker3_QueryProcessorQueue30) fVLa6nTScSci ARJUNA016089: TransactionImple.enlistResource - xa_start  - caught: XAException.XAER_NOTA for < formatId=131077, gtrid_length=29, bqual_length=36, tx_uid=0:ffffc0a8d301:3c1af496:5d5b9124:20, node_name=1, branch_uid=0:ffffc0a8d301:3c1af496:5d5b9124:24, subordinatenodename=null, eis_name=java:/youssefdbModel_hp-ds >: oracle.jdbc.xa.OracleXAException

As a result the transaction is marked as rollback only:

2019-08-20 09:23:07,117 TRACE [com.arjuna.ats.jta] (Worker3_QueryProcessorQueue30) fVLa6nTScSci TransactionImple.setRollbackOnly

A likely problem that could produce XAER_NOTA is that the oracle source is configured with <is-same-rm-override>true</is-same-rm-override>.  Generally oracle should be configured with that value as false - What is the purpose of "isSameRM-override-value" or "is-same-rm-override" attribute in datasource configuration? - Red H…

[mtawk]

Thank you Steven for the detailed explanation

Changed is-same-rm-override to false, and still having the same error

And the link at the bottom need a redhat subscription