1 Reply Latest reply on Feb 19, 2018 8:15 AM by Steven Hawkins

    Teiid: Cross database INSERT into SELECT * FROM SOME_TABLE

    Kulbhushan Chaskar Master

      Hi,

       

      I am using Teiid Admin API 9.1.3 and WildFly 10.0.0

       

      I am trying to insert a data from SQL server database to MySQL database.

      For that I am trying to execute query: insert into SvcSourceVdb_mysqlbitool.bitool.employee select * from SvcSourceVdb_sqlcre.dq.dbo.employee;

      both the databases having same table structure in both database types.

      Combined VDB file structure:

      <?xml version="1.0" ?><vdb name="MysqlSqlServerBundle" version="1"><description>VDB for: MysqlSqlServerBundle, Version: 1</description><connection-type>BY_VERSION</connection-type><property name="{http://teiid.org/rest}auto-generate" value="true"></property><property name="data-service-view" value="SvcView"></property><property name="deployment-name" value="MysqlSqlServerBundle-vdb.xml"></property><import-vdb name="SvcSourceVdb_sqlcre" version="1" import-data-policies="true"></import-vdb><import-vdb name="SvcSourceVdb_mysqlbitool" version="1" import-data-policies="true"></import-vdb></vdb>

       

      Where SvcSourceVdb_mysqlbitool and SvcSourceVdb_sqlcre are VDBs physical VDB's. I am able to execute SQL queries on both the VDBs.

       

      which end up on below exceptions:

      Used values{host = 100.110.250.147}{Port = 31000 }{vdb name = MysqlSqlServerBundle }

      Used values{SQL = insert into mysqlbitool.bitool.employee select * from sqlcre.dq.dbo.employee}

      TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysqlbitool: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2935bba[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e36836b connection handles=0 lastReturned=1519034607495 lastValidated=1519033941187 lastCheckedOut=1519034607490 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@2d91dc20 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@3673b544[pool=DSMYSQL20180119122308215] xaResource=LocalXAResourceImpl@167055[connectionListener=2935bba connectionManager=1ad84fac warned=false currentXid=null productName=MySQL productVersion=5.6.19 jndiName=java:/DSMYSQL20180119122308215] txSync=null]

      org.teiid.jdbc.TeiidSQLException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysqlbitool: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2935bba[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e36836b connection handles=0 lastReturned=1519034607495 lastValidated=1519033941187 lastCheckedOut=1519034607490 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@2d91dc20 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@3673b544[pool=DSMYSQL20180119122308215] xaResource=LocalXAResourceImpl@167055[connectionListener=2935bba connectionManager=1ad84fac warned=false currentXid=null productName=MySQL productVersion=5.6.19 jndiName=java:/DSMYSQL20180119122308215] 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:703)

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

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

      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:268)

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

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

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

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

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

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

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

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

      at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1088)

      at org.teiid.jdbc.StatementImpl.executeUpdate(StatementImpl.java:344)

      at com.bitwise.mySql.WithTeiid.execute(WithTeiid.java:735)

      at com.bitwise.mySql.WithTeiid.executeSelect(WithTeiid.java:299)

      at com.bitwise.mySql.WithTeiid.main(WithTeiid.java:55)

      Caused by: org.teiid.core.TeiidProcessingException: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysqlbitool: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2935bba[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e36836b connection handles=0 lastReturned=1519034607495 lastValidated=1519033941187 lastCheckedOut=1519034607490 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@2d91dc20 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@3673b544[pool=DSMYSQL20180119122308215] xaResource=LocalXAResourceImpl@167055[connectionListener=2935bba connectionManager=1ad84fac warned=false currentXid=null productName=MySQL productVersion=5.6.19 jndiName=java:/DSMYSQL20180119122308215] txSync=null]

      at org.teiid.dqp.internal.process.DataTierTupleSource.exceptionOccurred(DataTierTupleSource.java:401)

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

      at org.teiid.query.processor.relational.ProjectIntoNode.checkExitConditions(ProjectIntoNode.java:272)

      at org.teiid.query.processor.relational.ProjectIntoNode.nextBatchDirect(ProjectIntoNode.java:239)

      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:477)

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

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

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

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

      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)

      Caused by: org.teiid.core.TeiidException: TEIID11009 Remote org.teiid.translator.TranslatorException: TEIID11009 java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2935bba[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e36836b connection handles=0 lastReturned=1519034607495 lastValidated=1519033941187 lastCheckedOut=1519034607490 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@2d91dc20 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@3673b544[pool=DSMYSQL20180119122308215] xaResource=LocalXAResourceImpl@167055[connectionListener=2935bba connectionManager=1ad84fac warned=false currentXid=null productName=MySQL productVersion=5.6.19 jndiName=java:/DSMYSQL20180119122308215] txSync=null]

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

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

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

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

      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

      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:220)

      at com.sun.proxy.$Proxy27.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)

      ... 18 more

      Caused by: java.sql.SQLException: Remote java.sql.SQLException: javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2935bba[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e36836b connection handles=0 lastReturned=1519034607495 lastValidated=1519033941187 lastCheckedOut=1519034607490 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@2d91dc20 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@3673b544[pool=DSMYSQL20180119122308215] xaResource=LocalXAResourceImpl@167055[connectionListener=2935bba connectionManager=1ad84fac warned=false currentXid=null productName=MySQL productVersion=5.6.19 jndiName=java:/DSMYSQL20180119122308215] txSync=null]

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

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

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

      ... 29 more

      Caused by: javax.resource.ResourceException: Remote javax.resource.ResourceException: IJ000457: Unchecked throwable in managedConnectionReconnected() cl=org.jboss.jca.core.connectionmanager.listener.TxConnectionListener@2935bba[state=NORMAL managed connection=org.jboss.jca.adapters.jdbc.local.LocalManagedConnection@1e36836b connection handles=0 lastReturned=1519034607495 lastValidated=1519033941187 lastCheckedOut=1519034607490 trackByTx=false pool=org.jboss.jca.core.connectionmanager.pool.strategy.OnePool@2d91dc20 mcp=SemaphoreConcurrentLinkedQueueManagedConnectionPool@3673b544[pool=DSMYSQL20180119122308215] xaResource=LocalXAResourceImpl@167055[connectionListener=2935bba connectionManager=1ad84fac warned=false currentXid=null productName=MySQL productVersion=5.6.19 jndiName=java:/DSMYSQL20180119122308215] txSync=null]

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

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

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

      ... 31 more

      Caused by: javax.resource.ResourceException: Remote javax.resource.ResourceException: IJ000461: Could not enlist in transaction on entering meta-aware object

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

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

      ... 33 more

      Caused by: javax.transaction.SystemException: Remote javax.transaction.SystemException: IJ000356: Failed to enlist: java.lang.Throwable: Unabled to enlist resource, see the previous warnings. tx=TransactionImple < ac, BasicAction: 0:ffffac110002:415e87:5a8a9e34:57 status: ActionStatus.ABORT_ONLY >

      at org.jboss.jca.core.connectionmanager.listener.TxConnectionListener$TransactionSynchronization.checkEnlisted(TxConnectionListener.java:942)

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

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

      ... 34 more

       

      Thanks,

      Kulbhushan Chaskar