XML

Word

Printable

Details

Type: New Feature
Status: Stalled (View Workflow)
Priority: Critical
Resolution: Unresolved
Fix Version/s: 12.3
Component/s: None
Labels:

Sprint:
Q1/2026 Server Development, Q1/2026 Server Maintenance

Description

GLOBAL TEMPORARY TABLE is a feature found in Oracle, MSSQL, PostgreSQL etc.

Syntax in Oracle:

CREATE GLOBAL TEMPORARY TABLE tmp_table

{ (create_definition, ...)

  | [SELECT * FROM tbl_NAME WHERE conditions ] }

ON COMMIT [DELETE | PRESERVE] ROWS

The purpose of this syntax is to create temporary table where each user can have
their own data, which is destroyed at the end of the session.
Any user with access right to this table can access and use it.

Architecture

"CREATE GLOBAL TEMPORARY t1" creates a 'template' for future temporary tables with name t1.
Internally in MariaDB we would only create a .frm file for the table. We denote this table as parent table.
On first access to the parent table, MariaDB would internally execute
"CREATE TEMPORARY TABLE t1 LIKE t1". This temporary table is denoted as a child table and is bound to the parent, it is not shown in temporary tables list anywhere in information schema, or otherwise.
Any future DML of t1 would use the child table. There are a some commands that would intentionally use a parent table, like DDL.

Child table, since it's temporary, exists only in current session. Its lifetime is transaction for ON COMMIT DELETE ROWS, or session lifetime, or until TRUNCATE is executed. TRUNCATE is the only way to manually drop the child table.

A child table can't exist longer than its parent, so "DROP TABLE t1" would drop the global temporary table only if there is no data bound to this table from any session.
This means, any existence of a child table would block DDL until all the bound child tables are dropped. This is implemented with child table holding an MDL-READ lock of the EXPLICIT duration (like HANDLER does) – more precisely, with a duration of that child table's existence.
To conform Oracle behavior, where DROP TABLE exits immediately if it's blocked with some session-bound data, lock_wait_timeout is overriden to be 0 for DDL that Oracle has.

Existing implementations

ORACLE

ORACLE has GLOBAL TEMPORARY TABLE syntax as part of their normal CREATE TABLE definition.
Both CREATE TABLE …SELECT and CREATE TABLE (create_definition…) work.
https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/CREATE-TABLE.html#GUID-F9CE0CC3-13AE-4744-A43C-EAC7A71AAAB6__I2153132
https://oracle-base.com/articles/misc/temporary-tables

Examples:
https://dbfiddle.uk/O8KdF2kw
CREATE... SELECT: https://dbfiddle.uk/rY75s2Y5, https://dbfiddle.uk/7LipZHrC

TRUNCATE TABLE behavior:
When you truncate a temporary table, only the rows created during the current session are removed

IBM DB2

DB2 supports Declared Global Temporary Tables (DGTT), which are declared with a DECLARE GLOBAL TEMPORARY TABLE statement rather than CREATE …. The data is session-private.
https://www.ibm.com/docs/en/db2-for-zos/12?topic=statements-declare-global-temporary-table

MS SQL Server

Uses a concept of local (#table) and global (##table) temporary tables, but these are not the same as the standard’s “global temporary table”. They have their own rules about scoping and session handling.

PostgreSQL

Docs state that GLOBAL keyword has no effect.

However, there is an Oracle compatibility module, pgtt, which adds support.

But:

PostgreSQL temporary table clause ON COMMIT DROP is not supported by the extension, GTT are persistent over transactions.

There is also an ad-hoc trigger based suggestion.

MS SQL

Global temporary tables in MSSQL are created as a normal table with the a prefix of ## for the table name.

Documentation notes

Naming ambiguity

Now having global temporary tables, it becomes tricky to refer to /classic/ temporary tables due to naming ambiguity.
Sql standard refers to classic ones (those we have for long, non-global) as local temporary tables
Oracle uses a notion of PRIVATE temporary table for them.

We generally prefer to stay standard-compatible. Perhaps, we should add use of PRIVATE keyword for extra compatibility (as a separate feature). However when it's ambiguous we should prefer referring to classic temporary tables as local temporary tables.

How DDL beheves when GTT is in use

When GTT is in use in some session (i.e. there is a data bound to that session), it keeps a lock on that GTT. So DDL would not go until all the data to all the sessions is freed (with truncate/commit). For Oracle compatibility, we internally set lock_wait_timeout=0, so all DDL act as if NOWAIT is specified.

For now, WAIT x is not supported (though there is such syntax, so future support is possible).

GTTs doc page deserves a replication section.

What should be written there is:

Global temporary tables replicate only DDL statements. Data stored in Global temporary tables is never replicated. That is, DML statements involving Global temporary tables are forced to be replicated in the row mode. Statements involving Global temporary temporary tables are not suitable for statement-based replication (the advice is to use mixed or row-based). In statement-based replication, statements involving GTT will be forcefully replicated in row mode, with a warning.

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

MariaDBGttAwareConnection.zip
9 kB
2025-02-11 18:55

Issue Links

blocks

MCOL-5642 support querying temporary tables with cross engine joins

Open

causes

MDEV-37368 Assertion failed in close_thread_tables on UPDATE referring bad field

Closed

MDEV-37369 SIGSEGV on NEXTVAL from Global temporary table

Closed

MDEV-37378 SIGSEGV in MDL_ticket::has_stronger_or_equal_type and Assertion `!table->s->tmp_table' failed in wait_while_table_is_used on CREATE TRIGGER

Closed

MDEV-37379 Assertion `index->is_readable()' failed in dict_create_index_tree_in_mem on REPLACE DELAYED

Closed

MDEV-37380 SIGSEGV in thr_upgrade_write_delay_lock and ASAN use-after-poison in Delayed_insert::handle_inserts on INSERT DELAYED

Closed

MDEV-37381 SIGSEGV in mysql_ha_close_table after HANDLER OPEN of Global temporary table

Closed

MDEV-37382 crash on CREATE OR REPLACE GTT under LOCK TABLES

Closed

MDEV-37383 Crash in end_read_record after REPAIR of Global temporary table

Closed

MDEV-37384 SIGSEGV in mysql_unpack_partition on CREATE GTT ... LIKE partitioned table

Closed

MDEV-37385 Thread hang on CoR where the original table is a GTT

Closed

MDEV-37386 SIGSEGV in close_thread_table, UBSAN null-pointer-use in close_thread_table and Assertion `thd->open_tables == table->table' failed in mysql_create_like_table on CREATE GTT ... LIKE

Closed

MDEV-37394 SIGSEGV in handler::ha_external_lock on CREATE GTT ... ENGINE=INNODB SELECT, ASAN heap-use-after-free in unlock_external

Closed

MDEV-37395 SIGSEGV on CREATE TABLE ... SELECT where source table is a GTT

Closed

MDEV-37576 Assertion `!global_table.versioned()' failed after ALTER TABLE GTT ADD SYSTEM VERSIONING

Closed

MDEV-37577 Assertion `has_temporary_tables() || (rgi_slave && rgi_slave->is_parallel_exec)' failed in THD::open_temporary_table on CREATE TABLE

Closed

MDEV-37578 Assertion failed in TDC_element::flush on ALTER TABLE GTT DISCARD TABLESPACE

Closed

MDEV-37579 use-after-free in MDL_context::release_lock on FLUSH TABLE GTT

Closed

MDEV-37594 Thread hang on TRUNCATE GTT after server_id change

Closed

MDEV-37595 Assertion `thd->transaction->stmt.is_empty()' failed in mysql_ha_open when using mrg_myisam GTT

Closed

MDEV-37596 enforce_storage_engine has an effect on child global temporary tables

Closed

MDEV-37597 InnoDB: Failing assertion: table->get_ref_count() == 0 in dict_sys_t::remove on UPDATE

Closed

MDEV-37612 SIGSEGV in mysql_ha_close_table (opt) and in MDL_ticket::has_pending_conflicting_lock (dbg) after HANDLER OPEN

Closed

MDEV-37656 Thread hang in 'starting' state on FLUSH TABLES and shutdown hang, where the original table is a LOCKED GTT

Closed

MDEV-37657 SIGSEGV in mysql_ha_flush on SHOW CREATE TABLE after HANDLER OPEN GTT

Closed

MDEV-37660 Make Global Temporary Tables DDL errors more specific

In Testing

MDEV-37666 Global temporary table can be created with system versioning using CREATE LIKE

Closed

MDEV-37667 SIGSEGV in MDL_context::release_lock on ALTER on locked GTT in low memory env

Closed

MDEV-37668 SIGSEGV on DROP TABLE GTT under LOCK TABLES and different server_id

Closed

MDEV-37681 SIGSEGV on TRUNCATE GTT after failed RENAME under LOCK TABLE

Closed

MDEV-37690 SIGSEGV and UBSAN null-pointer-use in myrocks::Rdb_transaction_impl::prepare on CREATE TABLE ... AS SELECT

Closed

MDEV-37693 use-after-free in mysql_ha_flush after 2nd HANDLER OPEN execution

Closed

MDEV-37694 ASAN heap-use-after-free in check_column_name on CoR ... LIKE

Closed

MDEV-37700 DROP TABLE on GTT table succeeds in MariaDB only

Closed

MDEV-37718 Assertion '!thd->rgi_slave' failed in open_global_temporary_table on CREATE/ANALYZE GTT

Closed

MDEV-37720 use-after-free on CREATE OR REPLACE GTT under LOCK TABLES and pseudo_slave_mode

In Testing

MDEV-37742 SIGSEGV in Gtid_index_writer::write_current_node on SET GLOBAL binlog_checksum (opt builds)

Closed

MDEV-37793 Behavior of DROP TABLE / DROP DATABASE is inconsistent with GTT

Closed

MDEV-37798 Incomplete SAVEPOINT support for InnoDB global temporary tables

Closed

MDEV-37799 Incorrect behavior for DELETE statements on a GTT table

Closed

MDEV-37817 DROP TABLE GTT doesn't succeed for ON COMMIT DELETE ROWS

Closed

MDEV-37850 Wrong error on DROP TABLE GTT after DROP DATABASE

Closed

MDEV-37871 SIGSEGV I_P_List_iterator, UBSAN reference binding to null pointer on DROP DATABASE test

Closed

MDEV-37872 SIGSEGV in MDL_context::release_loc, Assertion `mdl_ticket->m_duration == MDL_TRANSACTION && duration != MDL_TRANSACTION' failed, ASAN heap-use-after-free in I_P_List on CoR

In Testing

MDEV-37873 Assertion `!memcmp(trx_id->data, reset_trx_id, 6)' failed in btr_cur_optimistic_insert on CREATE GTT

Closed

MDEV-37896 MTR Tests main.global_temporary_table and rpl.global_temporary_table are not stable on 2nd run

In Testing

MDEV-37897 Previously fixed MDEV-37690 bug was re-introduced into GTT feature branch

Closed

MDEV-37898 No GTT MTR coverage on release builds due to debug_sync requirement

Closed

MDEV-37909 GTT: "errno: 0 "Internal error/check" on CoR ... LIKE under lock

Closed

MDEV-37928 Assertion !thd->rgi_slave failed on ANALYZE TABLE on replica

Closed

MDEV-37929 Assertion !thd->rgi_slave' failed on REPAIR TABLE on replica

Closed

MDEV-37934 Assertion `thd->transaction->stmt.is_empty() || (thd->state_flags & Open_tables_state::BACKUPS_AVAIL)' failed in GRANT on GTT

In Testing

MDEV-37941 Assertion `!thd->rgi_slave' failed on INSERT w/ parallel slave

In Testing

MDEV-37956 ASAN heap-use-after-free and SIGSEGV in mysql_ha_close_table on DROP DATABASE

In Testing

MDEV-37957 Assertion `(*tables)->reginfo.lock_type >= TL_READ_SKIP_LOCKED' failed on HANDLER READ

In Testing

MDEV-37958 SIGSEGV and UBSAN invalid-enum-load in ha_mroonga::storage_create_foreign_key on INSERT

In Testing

MDEV-37963 wait_while_table_is_used() variation on MDEV-26805 stack in GTT branch

Closed

MDEV-38121 Master GTT overrides the slave table with the same name

Closed

MDEV-38125 Assertion !thd->rgi_slave failed on INSERT under LOCK TABLES

In Testing

MDEV-38151 GTT: missing FUNCTION support

In Testing

MDEV-38152 Spider GTT: table creation succeeds, INSERT fails with ER_ILLEGAL_HA_CREATE_OPTION

In Testing

MDEV-38266 GTT: Infinite loop after LOCK+REPAIR

In Testing

MDEV-38292 If GTT is in use in a replica session, DROP TABLE isn't applied.

Closed

MDEV-38438 Assertion `element->all_tables.is_empty()' failed in tdc_assert_clean_share on SELECT

In Testing

MDEV-38439 Memory leak when using GTT table on sphinx

In Review

MDEV-38440 UBSAN invalid-bool-load in ha_federated::end_bulk_insert on INSERT to Federated GTT

In Testing

MDEV-38441 Assertion `s->tmp_table != NO_TMP_TABLE || s->tdc->ref_count > 0' failed in TABLE::init on SELECT

Closed

MDEV-38442 Assertion `share->tdc->ref_count' failed in tdc_release_share on BEGIN

Closed

MDEV-38443 RocksDB GTT: table creation succeeds, INSERT fails with ER_ILLEGAL_HA_CREATE_OPTION, TRUNCATE asserts

In Testing

MDEV-38444 ASAN heap-use-after-free memory corruption on FLUSH TABLES

In Testing

MDEV-38445 malloc(): unaligned [tcache|fastbin] chunk detected, ASAN heap-use-after-free on CoR GTT

Closed

MDEV-38448 Memory corruption in find_locked_table and SIGSEGV from memcpy in TABLE_LIST::is_the_same_definition on CREATE GTT

In Testing

MDEV-38449 GTT+ha_federatedx: Assertion `sp && savepoint_next && *sp && *sp <= savepoint_level' failed and memory corruption on RELEASE SAVEPOINT

Closed

MDEV-38457 pseudo_slave_mode nor pseudo_thread_id can be enabled after creating a GTT, starting a trx and querying the GTT

Closed

MDEV-38460 Global temp table with ON COMMIT PRESERVE ROWS results in a lock wait timeout

Closed

MDEV-38468 SIGSEGV in MDL_lock::incompatible_granted_types_bitmap, Assertion `mdl_ticket->m_duration == MDL_TRANSACTION && duration != MDL_TRANSACTION' in MDL_context::set_lock_duration, memory corruptions in I_P_List & MDL_context::set_lock_duration

In Testing

MDEV-38480 GTT: Aria gets error HA_ERR_CRASHED_ON_USAGE after TRUNCATE

Closed

MDEV-38481 GTT: Assertion `file->trn == trn' failed in ha_maria::start_stmt on Aria ANALYZE TABLE

In Testing

MDEV-38495 Internal MariaDB error code: 1944 on GTT referencing BINLOG command

Closed

MDEV-38528 Federated[x] TEMPORARY table creation no longer possible after MDEV-38449 patch

Confirmed

MDEV-38567 GTT Context corruption on repeated SQL when autocommit is disabled

In Testing

MDEV-38568 GTT: Assertion `thd->mdl_context.is_lock_owner...' failed in close_thread_table during CREATE OR REPLACE

In Testing

MDEV-38603 GTT: Failing assertion: prebuilt->template_type == ROW_MYSQL_WHOLE_ROW on UPDATE

In Testing

MDEV-38638 GTT: SBR and MBR fall back to ROW events for all DML using GTT and base tables + binlog Bloat

Confirmed

MDEV-38646 GTT: ER_BINLOG_STMT_MODE_AND_ROW_ENGINE upon INSERT

Stalled

MDEV-38647 GTT: Slave thread-hangs on 'Waiting for table metadata lock' on DROP TABLE

Closed

MDEV-38648 Slave SQL thread halts with 1944 error upon INSERT ... DELAYED

Closed

is blocked by

MDEV-37348 Logged CREATE TEMPORARY TABLE LIKE from unlogged table fails

Closed

MDEV-38254 replicated temporary tables must not be accessed by start_new_trans

Closed

is part of

MDEV-32521 SQL Server compatibility

Open

MDEV-35388 PostgreSQL-compatible syntax

Open

MDEV-35973 Oracle Compatibility Project 1 for 2025

Stalled

relates to

MDEV-37877 Assertion `!mysql_bin_log.is_open()' failed in THD::mark_tmp_table_as_free_for_reuse on UPDATE

Closed

MDEV-10872 Providing compatibility Oracle database

Open

MDEV-37686 rpl.create_or_replace_mix2 fails in MDEV-35915 branch

Closed

MDEV-37719 use-after-free on logged CREATE OR REPLACE TEMPORARY TABLE LIKE not-logged-table

Stalled

MDEV-37940 Assertion `!thd->rgi_slave' failed on START SLAVE

Stalled

MDEV-37968 Assertion `table->s->table_category == TABLE_CATEGORY_SYSTEM || table->s->table_category == TABLE_CATEGORY_STATISTICS' failed in bool open_system_tables_for_read(THD *, TABLE_LIST *)

Open

has action item: DOCS-5950 Loading...

links to

MSSQL T-SQL syntax

(82 causes, 2 is blocked by, 3 is part of, 6 relates to, 1 has action item, 1 links to)

Activity

People

Assignee:: Nikita Malyavin

Reporter:: Michael Widenius

Assigned for Implementation:: Nikita Malyavin

Assigned for Review:: Michael Widenius

Assigned for Testing:: Roel Van de Paar

Assigned for Documentation:: Stefan Hinz

Votes:: 0 Vote for this issue

Watchers:: 14 Start watching this issue

Dates

Due:: 2025-06-16

Created:: 2025-01-23 13:42

Updated:: 2026-01-27 20:38

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.