Teiid exception, when setautocommit is false "TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457"

[barfa.anil]

I am using teiid two datasources. when I set "setAutocommit=true" is work fine. But when I set "setAutocommit=false" is gives below error.

ERROR c.b.t.d.i.b.DataCloningService.error(38) - TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysql_cloud1: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@3fc4c856[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@563b6686 connection handles=0 lastReturned=1559025839295 lastValidated=1559025275191 lastCheckedOut=1559025839214 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@343f2260 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@1047af01[pool=DSMYSQL20190527075619087] xaResource=LocalXAResourceImpl@46f7c7d7[connectionListener=3fc4c856 connectionManager=5cf0e3a1 warned=false currentXid=null productName=MySQL productVersion=5.7.14-google-log jndiName=java:/DSMYSQL20190527075619087] txSync=null]

org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysql_cloud1: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@3fc4c856[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@563b6686 connection handles=0 lastReturned=1559025839295 lastValidated=1559025275191 lastCheckedOut=1559025839214 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@343f2260 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@1047af01[pool=DSMYSQL20190527075619087] xaResource=LocalXAResourceImpl@46f7c7d7[connectionListener=3fc4c856 connectionManager=5cf0e3a1 warned=false currentXid=null productName=MySQL productVersion=5.7.14-google-log jndiName=java:/DSMYSQL20190527075619087] txSync=null]

        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)

        at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)

        at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:723)

        at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:65)

        at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:561)

        at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)

        at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)

        at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)

        at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:285)

        at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:323)

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

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

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

        at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)

        at com.sun.proxy.$Proxy74.read(Unknown Source)

        at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:422)

        at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:570)

        at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:423)

[shawkins]

With autoCommit=true there is an associated Teiid property autoCommitTxn that defaults to DETECT.  In that case the engine will determine if a transaction seems necessary and will start it automatically, so a wrapping transaction won't always be created.  In autoCommit=false you have started a client local transaction, which means there will be an XA transaction started in the Teiid engine to implement that transaction.  In this case you probably have more than one local transaction source (LocalXAResourceImpl) participating - which results in an exception.  You should use either XA sources, or determine if a transaction is really needed in this case.

[barfa.anil]

Thanks Steven,

Yes, We are using more than one local transaction, We will try the XA source. But in Teiid document i am not able to find much detail on this.

We are using java api to create and deploy VDB. Where I can get more detail on XA datasource.

Thanks,

Anil

[rareddy]

Here is an example of creating a XA Data source in WildFly How to create a XA datasource in Wildfly 10 using the mysql jdbc connector - Sandny Blog

You can also check the "<wildfly/docs/teiid/datasources" directory and for every relational database there should be sample configuration file on how to configure a XA datasource with properties needed.

[barfa.anil]

Thanks Ramesh