Bug Report: allow temporary tables in a transaction

[mcrauwel]

Overview of the Issue

In #14110 and #16661 support was added to execute an implicit commit when running DDL's inside of a transaction. This is the way that MySQL handles these cases.

We've noticed that for temporary tables created inside a transaction, however for temporary tables there is no commit (so definitely not an implicit one) as they will not be persisted at all, we are prevented from running the CREATE TEMPORARY TABLE at all

Please allow temporary tables to be executed inside of a transaction without running an implicit commit

From the MySQL reference manual:

CREATE TABLE causes an implicit commit, except when used with the TEMPORARY keyword. See Section 15.3.3, "Statements That Cause an Implicit Commit".

source https://dev.mysql.com/doc/refman/8.4/en/create-temporary-table.html

Reproduction Steps

Initial setup, create table add one record

mysql> CREATE TABLE t1 (id BIGINT UNSIGNED PRIMARY KEY, data JSON);
Query OK, 0 rows affected (0.09 sec)

mysql> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` bigint unsigned NOT NULL,
  `data` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.04 sec)

mysql> INSERT INTO t1 VALUES (1, '[]');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | data |
+----+------+
|  1 | []   |
+----+------+
1 row in set (0.03 sec)

Start transaction, Insert record, CREATE TABLE (implicit commit), ROLLBACK, see that the record was actually committed by the implicit COMMIT

mysql> BEGIN;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (2, '[]');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | data |
+----+------+
|  1 | []   |
|  2 | []   |
+----+------+
2 rows in set (0.04 sec)

mysql> CREATE TABLE t2 (id BIGINT UNSIGNED PRIMARY KEY, data JSON);
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | data |
+----+------+
|  1 | []   |
|  2 | []   |
+----+------+
2 rows in set (0.03 sec)

mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | data |
+----+------+
|  1 | []   |
|  2 | []   |
+----+------+
2 rows in set (0.04 sec)

Start new transaction, CREATE TEMPORARY TABLE (fails), rollback (record was not committed)

mysql> BEGIN;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO t1 VALUES (3, '[]');
Query OK, 1 row affected (0.04 sec)

mysql> CREATE TEMPORARY TABLE t3 (id BIGINT UNSIGNED PRIMARY KEY, data JSON);
ERROR 1105 (HY000): target: mcrauwel.-.primary: vttablet: DDL statement executed inside a transaction (CallerID: eqbouuzo7jc7s7oppb1j)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM t1;
+----+------+
| id | data |
+----+------+
|  1 | []   |
|  2 | []   |
+----+------+
2 rows in set (0.03 sec)

mysql>

Binary Version

$ vtgate --version
vtgate version Version: 21.0.0-SNAPSHOT (Git revision a07e26e8ec4764e59a623e05bc82cb345b7843cd branch 'latest-21.0') built on Wed Mar 19 05:05:43 UTC 2025 by vitess@buildkitsandbox using go1.23.7 linux/amd64

Operating System and Environment details

Linux/amd64 - Ubuntu

Log Fragments