Details
-
Bug
-
Status: Open (View Workflow)
-
Critical
-
Resolution: Unresolved
-
N/A
-
None
-
Not for Release Notes
-
Q4/2025 Server Maintenance
Description
On oracle we see with this testase:
SET AUTOCOMMIT ON; |
CREATE GLOBAL TEMPORARY TABLE t (c INT) ON COMMIT PRESERVE ROWS; |
UPDATE t SET foo=1; |
DROP TABLE t; |
The following output:
|
oracle |
...
|
SQL> UPDATE t SET foo=1;
|
UPDATE t SET foo=1
|
ERROR at line 1: ORA-00904: "FOO": invalid identifier
|
|
|
SQL> DROP TABLE t;
|
Table dropped.
|
However the same on MariaDB (without autocommit, which is on by default):
CREATE GLOBAL TEMPORARY TABLE t (c INT) ON COMMIT PRESERVE ROWS; |
UPDATE t SET foo=1; |
DROP TABLE t; |
Will give an error on the DROP:
|
MDEV-35915-6 CS 12.2.0 ed3c63488a1613377d92ee3ade3fe6870e39b4db (Debug, Clang 21.1.0-20250811) Build 24/09/2025 |
...
|
12.2.0-dbg>UPDATE t SET foo=1;
|
ERROR 1054 (42S22): Unknown column 'foo' in 'SET'
|
|
|
12.2.0-dbg>DROP TABLE t;
|
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
|
Which does not match functionality.
Additionally, the UPDATE is invalid (no actual update is made), so the following:
CREATE GLOBAL TEMPORARY TABLE t (c INT) ON COMMIT PRESERVE ROWS; |
DROP TABLE t; |
Is effectively equal. However, using this, the table is dropped:
|
MDEV-35915-6 CS 12.2.0 ed3c63488a1613377d92ee3ade3fe6870e39b4db (Debug, Clang 21.1.0-20250811) Build 24/09/2025 |
12.2.0-dbg>CREATE GLOBAL TEMPORARY TABLE t (c INT) ON COMMIT PRESERVE ROWS;
|
Query OK, 0 rows affected (0.013 sec)
|
|
|
12.2.0-dbg>DROP TABLE t;
|
Query OK, 0 rows affected (0.006 sec)
|
The issue happens only when ON COMMIT PRESERVE ROWS is used and is present for at least InnoDB+MyISAM.
While not directly sensical, issuing a COMMIT; makes no difference. However, exiting the client and re-entering it does make a difference (i.e. the table can then be dropped even after an UPDATE in the previous client session).
Furthermore, dropping the DATABASE is possible:
USE test; |
CREATE GLOBAL TEMPORARY TABLE t (c INT) ON COMMIT PRESERVE ROWS; |
UPDATE t SET foo=1; |
DROP DATABASE test; |
Results in:
|
MDEV-35915-6 CS 12.2.0 ed3c63488a1613377d92ee3ade3fe6870e39b4db (Debug, Clang 21.1.0-20250811) Build 24/09/2025 |
...
|
12.2.0-dbg>UPDATE t SET foo=1;
|
ERROR 1054 (42S22): Unknown column 'foo' in 'SET'
|
|
|
12.2.0-dbg>DROP DATABASE test;
|
Query OK, 1 row affected (0.010 sec)
|
Which does not match the 0-lock error on DROP TABLE behavior.
More significantly, when the database is recreated subsequently and another DROP is attempted (while no table is visible!) we again get a 0-lock error:
USE test; |
CREATE GLOBAL TEMPORARY TABLE t (c INT) ON COMMIT PRESERVE ROWS; |
UPDATE t SET foo=1; |
DROP DATABASE test; |
CREATE DATABASE test; |
USE test; |
SHOW TABLES; # No tables |
DROP TABLE t; # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction |
DROP TABLE tt; # As a test; no such table |
Leads to:
|
MDEV-35915-6 CS 12.2.0 ed3c63488a1613377d92ee3ade3fe6870e39b4db (Debug, Clang 21.1.0-20250811) Build 24/09/2025 |
12.2.0-dbg>UPDATE t SET foo=1;
|
ERROR 1054 (42S22): Unknown column 'foo' in 'SET'
|
|
|
12.2.0-dbg>DROP DATABASE test;
|
Query OK, 1 row affected (0.029 sec)
|
|
|
12.2.0-dbg>CREATE DATABASE test;
|
Query OK, 1 row affected (0.000 sec)
|
|
|
12.2.0-dbg>USE test;
|
Database changed
|
|
|
12.2.0-dbg>SHOW TABLES; # No tables
|
Empty set (0.002 sec)
|
|
|
12.2.0-dbg>DROP TABLE t; # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
|
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
|
|
|
12.2.0-dbg>DROP TABLE tt; # As a test; no such table
|
ERROR 1051 (42S02): Unknown table 'test.tt'
|
Attachments
Issue Links
- is caused by
-
MDEV-35915 Implement Global temporary tables
-
- Stalled
-