[teiid-issues] [JBoss JIRA] (TEIID-4579) External Materialization MySQL can't swap primary and stage table in xa transaction

External Materialization MySQL can't swap primary and stage table in xa transaction ----------------------------------------------------------------------------------- Key: TEIID-4579 URL: https://issues.jboss.org/browse/TEIID-4579 Project: Teiid Issue Type: Bug Components: Server Affects Versions: 8.12.7.6_3 Reporter: Jan Stastny Assignee: Steven Hawkins When using MySQL 5.5 as materialization target (primary and stage table are placed there) and on server side the mysql datasource is configured as xa-datasource, problem with transaction scope and status appears. The cause of this issue is, that in my "teiid_rel:MATVIEW_AFTER_LOAD_SCRIPT" I try to swap primary and stage materialization tables by using third swap table: {code:sql} exec Source.native(''ALTER TABLE DV_MATVIEWS_MAT_VIEW_STAGE RENAME TO DV_MATVIEWS_MAT_VIEW_TEMP''); exec Source.native(''ALTER TABLE DV_MATVIEWS_MAT_VIEW RENAME TO DV_MATVIEWS_MAT_VIEW_STAGE''); exec Source.native(''ALTER TABLE DV_MATVIEWS_MAT_VIEW_TEMP RENAME TO DV_MATVIEWS_MAT_VIEW''); {code} but there is a limitation of ALTER TABLE commands which results in implicit commit: https://dev.mysql.com/doc/refman/5.5/en/implicit-commit.html Eventually the first of the three ALTER commands succeeds, but the second fails, thus I end up with no DV_MATVIEWS_MAT_VIEW_STAGE table, as it was previously renamed to DV_MATVIEWS_MAT_VIEW_TEMP. This particular problem could be resolved by TEIID-4240 I believe. The stacktrace of the issue: {code:plain} 15:19:59,825 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue13) dEAkWiMWXtw2.0.77.13 Processing NEW request: EXEC Source.native('ALTER TABLE DV_MATVIEWS_MAT_VIEW_STAGE RENAME TO DV_MATVIEWS_MAT_VIEW_TEMP') 15:20:00,035 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue13) dEAkWiMWXtw2.0.77.13 Obtained execution 15:20:00,036 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue13) Source-specific command: ALTER TABLE DV_MATVIEWS_MAT_VIEW_STAGE RENAME TO DV_MATVIEWS_MAT_VIEW_TEMP 15:20:00,247 WARN [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue13) Connector worker process failed for atomic-request=dEAkWiMWXtw2.0.77.13: org.teiid.translator.jdbc.JDBCExecutionException: 1399 TEIID11008:TEIID11004 Error executing statement(s): ALTER TABLE DV_MATVIEWS_MAT_VIEW_STAGE RENAME TO DV_MATVIEWS_MAT_VIEW_TEMP at org.teiid.translator.jdbc.JDBCDirectQueryExecution.execute(JDBCDirectQueryExecution.java:95) [translator-jdbc-8.12.7.6_3-redhat-1.jar:8.12.7.6_3-redhat-1] at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:366) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.8.0_102] at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) [rt.jar:1.8.0_102] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_102] at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_102] at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:211) at com.sun.proxy.$Proxy81.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:391) 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.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.proc.ProcedurePlan.executePlan(ProcedurePlan.java:608) at org.teiid.query.processor.proc.CreateCursorResultSetInstruction.process(CreateCursorResultSetInstruction.java:69) at org.teiid.query.processor.proc.ExecDynamicSqlInstruction$1.process(ExecDynamicSqlInstruction.java:218) at org.teiid.query.processor.proc.ProcedurePlan.processProcedure(ProcedurePlan.java:389) at org.teiid.query.processor.proc.ProcedurePlan.nextBatchDirect(ProcedurePlan.java:298) at org.teiid.query.processor.proc.ProcedurePlan.nextBatch(ProcedurePlan.java:270) at org.teiid.query.processor.relational.PlanExecutionNode.nextBatchDirect(PlanExecutionNode.java:118) 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) [rt.jar:1.8.0_102] at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [rt.jar:1.8.0_102] at java.lang.Thread.run(Thread.java:745) [rt.jar:1.8.0_102] Caused by: java.sql.SQLException: XAER_RMFAIL: The command cannot be executed when global transaction is in the ACTIVE state at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2713) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2663) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:888) at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:730) at com.mysql.jdbc.jdbc2.optional.StatementWrapper.execute(StatementWrapper.java:698) at org.jboss.jca.adapters.jdbc.WrappedStatement.execute(WrappedStatement.java:157) at org.teiid.translator.jdbc.JDBCDirectQueryExecution.execute(JDBCDirectQueryExecution.java:83) [translator-jdbc-8.12.7.6_3-redhat-1.jar:8.12.7.6_3-redhat-1] ... 45 more 15:20:00,254 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue13) dEAkWiMWXtw2.0.77.13 Remove State 15:20:00,254 DEBUG [org.teiid.CONNECTOR] (Worker0_QueryProcessorQueue13) dEAkWiMWXtw2.0.77.13 Processing Close : EXEC Source.native('ALTER TABLE DV_MATVIEWS_MAT_VIEW_STAGE RENAME TO DV_MATVIEWS_MAT_VIEW_TEMP') {code}