In Case of MySQL
MySQL8.0.13のリリースノートに以下の記載があったので部分的ではありますが、CREATE TEMPORARYテーブルをトランザクション内で実行し確認してみました。
Previously, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements were not supported inside transactions, procedures, functions, or triggers when using GTIDs (that is, when the enforce_gtid_consistency system variable is set to ON). It was possible to use these statements with GTIDs enabled, but only outside of any transaction, and only with autocommit=1.
From MySQL 8.0.13, this restriction has been removed when binlog_format is set to ROW or MIXED. With row-based logging in use, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements can now be used inside transactions, procedures, functions, or triggers when GTIDs are enabled. When binlog_format is set to STATEMENT, the restriction remains. Because of this difference in behavior, some additional restrictions now apply to changing the binlog_format setting at runtime:
If a session has open temporary tables, the replication format cannot be changed for the session (SET @@SESSION.binlog_format).
If any replication channel has open temporary tables, the replication format cannot be changed globally (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format).
If any replication channel applier thread is currently running, the replication format cannot be changed globally (SET @@GLOBAL.binlog_format or SET @@PERSIST.binlog_format).
Trying to switch the replication format in any of these cases (or attempting to set the current replication format) results in an error. You can, however, use PERSIST_ONLY (SET @@PERSIST_ONLY.binlog_format) to change the replication format at any time, because this action does not modify the runtime global system variable value, and takes effect only after a server restart.
When binlog_format is set to ROW or MIXED, CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE statements are not written to the binary log and are therefore not replicated to slaves. When they are used in transactions, if the removal of these statements from the transaction results in an empty transaction, the transaction is not written to the binary log. If a transaction involving these statements is rolled back, a warning message is issued stating that the creation or dropping of the temporary tables could not be rolled back.
The MySQL Server code for binary logging has been refactored to create new internal interfaces for accessing events in a binary log and relay log. The new interfaces separate the write and read processes for binary logging into input and output streams, and decouple the process of capturing and retrieving binary log events from the process of writing them to files. A logical binary log file is used to wrapper the storage layer operations.
The new internal interfaces make it possible for MySQL Server to use alternative storage methods for binary log events besides a standard binary log or relay log file, including a binary log cache or memory buffer. For example, Group Replication uses the new interfaces to serialize events directly to memory buffers and transaction messages in order to co-ordinate transactions in the group. mysqlbinlog also now uses them to read binary log events from standard input.
Changes in MySQL 8.0.13 (2018-10-22, General Availability)
TEMPORARYテーブルなので、セッションを切断すれば無くなるのですが、トランザクション内で実行する事でGTIDに影響が本当に無いのか気になったので念の為の確認です。結論としては、マニュアルに記載の通り、バイナリーログには何も記録されていませんでした。
~/git/rdbms-docker/mysql [10:04:47]> mysql -h 127.0.0.1 -P 13306 -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.30 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000016 | 180 | No |
| binlog.000017 | 29804965 | No |
| binlog.000018 | 180 | No |
| binlog.000019 | 180 | No |
| binlog.000020 | 180 | No |
| binlog.000021 | 180 | No |
| binlog.000022 | 180 | No |
| binlog.000023 | 180 | No |
| binlog.000024 | 180 | No |
| binlog.000025 | 180 | No |
| binlog.000026 | 180 | No |
| binlog.000027 | 180 | No |
| binlog.000028 | 180 | No |
| binlog.000029 | 180 | No |
| binlog.000030 | 180 | No |
| binlog.000031 | 180 | No |
| binlog.000032 | 30604588 | No |
| binlog.000033 | 22621 | No |
| binlog.000034 | 3961 | No |
+---------------+-----------+-----------+
19 rows in set (0.01 sec)
mysql> flush binary logs;
Query OK, 0 rows affected (0.05 sec)
mysql> show binary logs;
+---------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000016 | 180 | No |
| binlog.000017 | 29804965 | No |
| binlog.000018 | 180 | No |
| binlog.000019 | 180 | No |
| binlog.000020 | 180 | No |
| binlog.000021 | 180 | No |
| binlog.000022 | 180 | No |
| binlog.000023 | 180 | No |
| binlog.000024 | 180 | No |
| binlog.000025 | 180 | No |
| binlog.000026 | 180 | No |
| binlog.000027 | 180 | No |
| binlog.000028 | 180 | No |
| binlog.000029 | 180 | No |
| binlog.000030 | 180 | No |
| binlog.000031 | 180 | No |
| binlog.000032 | 30604588 | No |
| binlog.000033 | 22621 | No |
| binlog.000034 | 4005 | No |
| binlog.000035 | 157 | No |
+---------------+-----------+-----------+
20 rows in set (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
Empty set (0.00 sec)
mysql> use CONFIRM
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> START TRANSACTION;CREATE TEMPORARY TABLE T_CHECK_TMP(`genre_id` INT PRIMARY KEY, `genre_name` TEXT);
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+------------+--------+------------+
| TABLE_ID | NAME | N_COLS | SPACE |
+----------+------------+--------+------------+
| 1222 | #sql1_13_3 | 5 | 4243767290 |
+----------+------------+--------+------------+
1 row in set (0.01 sec)
mysql> show binlog events in 'binlog.000035';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000035 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 |
| binlog.000035 | 126 | Previous_gtids | 1 | 157 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+-----------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------+
| Warning | 1751 | The creation of some temporary tables could not be rolled back. |
+---------+------+-----------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show variables like '%format%';
+--------------------------------------------------+---------+
| Variable_name | Value |
+--------------------------------------------------+---------+
| binlog_format | ROW |
| default_week_format | 0 |
| information_schema_stats_expiry | 86400 |
| innodb_default_row_format | dynamic |
| require_row_format | OFF |
| show_gipk_in_create_table_and_information_schema | ON |
+--------------------------------------------------+---------+
6 rows in set (0.02 sec)
mysql> drop table T_CHECK_TMP;
Query OK, 0 rows affected (0.00 sec)
mysql> START TRANSACTION;CREATE TEMPORARY TABLE T_CHECK_TMP(`genre_id` INT PRIMARY KEY, `genre_name` TEXT);
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO;
+----------+------------+--------+------------+
| TABLE_ID | NAME | N_COLS | SPACE |
+----------+------------+--------+------------+
| 1223 | #sql1_13_6 | 5 | 4243767290 |
+----------+------------+--------+------------+
1 row in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
mysql> show binlog events in 'binlog.000035';
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
| binlog.000035 | 4 | Format_desc | 1 | 126 | Server ver: 8.0.30, Binlog ver: 4 |
| binlog.000035 | 126 | Previous_gtids | 1 | 157 | |
+---------------+-----+----------------+-----------+-------------+-----------------------------------+
2 rows in set (0.00 sec)
mysql>