All Products
Search

This Product

    :How to handle restrictions on temporary tables caused by the GTID feature of RDS MySQL 5.6

    Document Center

    :How to handle restrictions on temporary tables caused by the GTID feature of RDS MySQL 5.6

    Last Updated:Dec 02, 2022

    Problem description

    In RDS MySQL 5.6, the GTID feature restricts temporary tables, and the following error is reported.

    When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, the statements CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can be executed in a non-transactional context only, and require that AUTOCOMMIT = 1.

    Solution

    You can select the following solutions based on the actual situation.

    • Change the create temporary table statement to createtable and use a common table as an alternative to a temporary table.

    • Modify the code to place the create and delete operations on temporary tables outside the transaction, and make sure that the parameter of the session is autocommit=1.

    Application scope

    • ApsaraDB RDS for MySQL