Temporary tables are now allowed in transactions when GTIDs are enabled

There has been a recurring request from our users to remove a limit when using global transaction identifiers (GTIDs) and temporary tables together. The limit is that temporary tables cannot be created and dropped inside a transaction when GTIDs are turned on. This is due to the fact that the act of creating or dropping temporary tables resembles non-transactional behavior and thus may lead to an inconsistent replication data flow.

As of MySQL 8.0.13, the MySQL server allows creation and dropping of temporary tables inside a transaction, procedure, function or trigger, when GTIDs are enabled. This is only possible if you are running binlog format in ROW mode (or MIXED, because it switches to ROW mode automatically).

Since version 8.0, CREATE TEMPORARY and DROP TEMPORARY are not replicated when  using row-based replication. Building on that, we now allow these statements when  using row-based replication and GTIDs.

Limitations

Note though that for the user to be able to safely run this feature the following limits were set (to avoid creating replication issues, by switching back and forth between binlog  formats while temporary tables are in use):

1. SET @@session.binlog_format is disallowed if the session has open temporary table(s). Otherwise DROP TEMPORARY TABLE is written into binary log in the following case:
SET @@session.binlog_format=STATEMENT;
CREATE TEMPORARY TABLE t1 (a INT);
SET @@session.binlog_format=ROW;
DROP TEMPORARY TABLE t1;

2. SET @@global.binlog_format and SET @@persist.binlog_format is disallowed if any replication channel has open temporary table(s). Otherwise DROP TEMPORARY TABLE is written into binary log on slave in the following case:
slave> SET @@global.binlog_format=STATEMENT;
slave> START SLAVE;
master> CREATE TEMPORARY TABLE t1(a INT);
slave> [wait for t1 to replicate]
slave> STOP SLAVE;
slave> SET @@global.binlog_format=ROW / SET @@persist.binlog_format=ROW
master> DROP TEMPORARY TABLE t1;
slave> START SLAVE;

3. SET @@global.binlog_format and SET @@persist.binlog_format is disallowed if any replication channel applier thread is running. Suppose you could change the global binlog_format while there are no temporary tables, but replication threads running. For  the replication threads, the option only takes effect later, when the thread is restarted the next time. In case any temporary tables are created between the change of binlog_format and the next thread restart, the change effectively happens with open temporary tables, leading to similar issues as described above.

Summary

This feature allows CREATE/DROP TEMPORARY TABLE inside a transaction, PROCEDURE, FUNCTION, or TRIGGER when @@global.gtid_mode=ON and @@session.binlog_format=ROW/MIXED. For design details, please see WL#8872. It is available in MySQL 8.0.13 release. Please download and try it out from here.

3,239 total views, 52 views today

One thought on “Temporary tables are now allowed in transactions when GTIDs are enabled

Leave a Reply