Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-4579

External Materialization MySQL can't swap primary and stage table in xa transaction

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • None
    • 8.12.7.6_3
    • Server
    • None

    Description

      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:

      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'');

      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:

      Unable to find source-code formatter for language: plain. Available languages are: actionscript, ada, applescript, bash, c, c#, c++, cpp, css, erlang, go, groovy, haskell, html, java, javascript, js, json, lua, none, nyan, objc, perl, php, python, r, rainbow, ruby, scala, sh, sql, swift, visualbasic, xml, yaml
      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')

      Attachments

        Issue Links

          is related to

          Feature Request - Feature requests from customers and/or users TEIID-4628 Support incremental materialization refresh strategies

          • Major - To be worked after higher priority work items are addressed. When the severity is high and the effort to change it is low to moderate, this term is chosen. Issues in this category likely have an existing workaround but implementation or execution may be non-trivial.
          • Resolved

          Activity

            People

              rhn-engineering-shawkins Steven Hawkins
              jstastny@redhat.com Jan Stastny
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: