MariaDB Temporary Table: Create, From Select, Index and Drop

I created a temporary table in one MariaDB session, and a second session that asked for it a moment later got ERROR 1146. A MariaDB temporary table belongs to one connection: CREATE TEMPORARY TABLE builds it, and disconnecting drops it.

Here’s what we’re building, a session-only sales summary:

Before You Start: Test Setup and Sample Data

Test setup: MariaDB 13.0.2, Windows 11, one session per .sql file. Six orders (see create a table in MariaDB):

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer VARCHAR(20) NOT NULL,
  state CHAR(2) NOT NULL,
  total DECIMAL(8,2) NOT NULL
);
INSERT INTO orders VALUES
  (1, 'Megan Bowen',     'TX', 129.99),
  (2, 'Alex Wilber',     'CO',  58.40),
  (3, 'Adele Vance',     'WA', 310.00),
  (4, 'Lee Gu',          'TX',  45.25),
  (5, 'Diego Siciliani', 'CA',  89.00),
  (6, 'Lynne Robbins',   'WA',  72.10);
SELECT VERSION(), COUNT(*) AS orders FROM orders;

Result:

+----------------+--------+
| VERSION()      | orders |
+----------------+--------+
| 13.0.2-MariaDB |      6 |
+----------------+--------+

Step 1: Create a Temporary Table With a Primary Key and Index

CREATE TABLE plus one word. Keys and indexes behave like any MariaDB primary key:

CREATE OR REPLACE TEMPORARY TABLE cart (
  sku VARCHAR(10) PRIMARY KEY,
  qty INT NOT NULL,
  INDEX ix_qty (qty)
);
INSERT INTO cart VALUES ('MUG-01', 2), ('MUG-01', 5);

SHOW FULL TABLES;

Output:

--------------
INSERT INTO cart VALUES ('MUG-01', 2), ('MUG-01', 5)
--------------

ERROR 1062 (23000) at line 6: Duplicate entry 'MUG-01' for key 'PRIMARY'
+------------------------+-----------------+
| Tables_in_df_mariatemp | Table_type      |
+------------------------+-----------------+
| cart                   | TEMPORARY TABLE |
| orders                 | BASE TABLE      |
+------------------------+-----------------+

Checkpoint: ERROR 1062, and cart listed as TEMPORARY TABLE. I expected SHOW TABLES to hide it. Not since MariaDB 11.2.

Step 2: CREATE TEMPORARY TABLE AS SELECT (and LIKE)

Filter and aggregate in one statement, here with GROUP BY. LIKE copies a structure, keys included:

CREATE TEMPORARY TABLE state_totals AS
SELECT state, COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE total > 50
GROUP BY state;
SELECT * FROM state_totals ORDER BY revenue DESC;

CREATE TEMPORARY TABLE orders_copy LIKE orders;

SELECT TABLE_NAME, TABLE_TYPE, ENGINE
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'TEMPORARY';

What came back:

+-------+--------+---------+
| state | orders | revenue |
+-------+--------+---------+
| WA    |      2 |  382.10 |
| TX    |      1 |  129.99 |
| CA    |      1 |   89.00 |
| CO    |      1 |   58.40 |
+-------+--------+---------+
+--------------+------------+--------+
| TABLE_NAME   | TABLE_TYPE | ENGINE |
+--------------+------------+--------+
| orders_copy  | TEMPORARY  | InnoDB |
| state_totals | TEMPORARY  | InnoDB |
+--------------+------------+--------+

Checkpoint: four states, since Lee Gu’s $45.25 order misses the filter. What this tells me: with default_tmp_storage_engine NULL, your temp tables get InnoDB, while the server’s own GROUP BY scratch tables use Aria.

Note: SELECT INTO in MariaDB fills variables, not new tables.

Step 3: Prove a Second Session Can’t See the Temp Table

Session 1 builds tx_orders and sleeps. I started session 2 alongside it with start /b:

CREATE TEMPORARY TABLE tx_orders AS
SELECT id, customer, total FROM orders WHERE state = 'TX';
SELECT CONNECTION_ID() AS session_1, COUNT(*) AS tx_rows FROM tx_orders;
DO SLEEP(3);

Output:

+-----------+---------+
| session_1 | tx_rows |
+-----------+---------+
|      1482 |       2 |
+-----------+---------+

Session 2, mid-sleep:

SELECT CONNECTION_ID() AS session_2;
SELECT COUNT(*) FROM tx_orders;

It got:

+-----------+
| session_2 |
+-----------+
|      1483 |
+-----------+
--------------
SELECT COUNT(*) FROM tx_orders
--------------

ERROR 1146 (42S02) at line 2: Table 'df_mariatemp.tx_orders' doesn't exist

Step 4: Shadow a Real Table, Then DROP TEMPORARY TABLE IF EXISTS

A temporary table may borrow a real table’s name. It hides that table for your session:

CREATE TEMPORARY TABLE orders AS
SELECT * FROM orders WHERE state = 'TX';
SELECT COUNT(*) AS rows_seen FROM orders;

DROP TEMPORARY TABLE IF EXISTS orders;
SELECT COUNT(*) AS rows_seen FROM orders;

DROP TEMPORARY TABLE IF EXISTS orders;
SHOW WARNINGS;

Output:

+-----------+
| rows_seen |
+-----------+
|         2 |
+-----------+
+-----------+
| rows_seen |
+-----------+
|         6 |
+-----------+
+-------+------+-------------------------------------+
| Level | Code | Message                             |
+-------+------+-------------------------------------+
| Note  | 1051 | Unknown table 'df_mariatemp.orders' |
+-------+------+-------------------------------------+

Checkpoint: a plain DROP TABLE on that last line would have deleted the real orders. Scary.

Step 5: Grant the CREATE TEMPORARY TABLES Privilege

My mt_report account has only SELECT on the database. It tries the Texas report:

CREATE TEMPORARY TABLE tx_orders AS
SELECT id, customer, total FROM orders WHERE state = 'TX';
UPDATE tx_orders SET total = total * 1.0825;
SELECT customer, total FROM tx_orders;

Output:

--------------
CREATE TEMPORARY TABLE tx_orders AS
SELECT id, customer, total FROM orders WHERE state = 'TX'
--------------

ERROR 1044 (42000) at line 1: Access denied for user 'mt_report'@'localhost' to database 'df_mariatemp'

The fix, like any MariaDB database grant:

GRANT CREATE TEMPORARY TABLES ON df_mariatemp.*
  TO 'mt_report'@'localhost';

Checkpoint: the rerun prints Megan Bowen at $140.71, with no UPDATE grant needed.

Test: Can You Use a Temporary Table Twice in One Query?

Old posts promise ERROR 1137 (Can’t reopen table) when a query touches a temp table twice. My self-join:

CREATE TEMPORARY TABLE cart AS SELECT 'MUG-01' AS sku;

SELECT a.sku FROM cart a JOIN cart b USING (sku);

Result:

+--------+
| sku    |
+--------+
| MUG-01 |
+--------+

No error. MariaDB removed that limit in 10.2.1 (MDEV-5535); MySQL still has it. Joins and subqueries are fine.

Troubleshooting: What Tripped Me Up

• CREATE TEMPORARY TABLE IF NOT EXISTS on an existing name is Note 1050.
• Tables made in a stored procedure outlive the CALL.

My Takeaways on MariaDB Temp Tables

1. Type TEMPORARY in DROP, LIKE and CREATE OR REPLACE.
2. Check existence in information_schema.TABLES (TABLE_TYPE = ‘TEMPORARY’).

Pooled connections never really disconnect, so drop yours.

Where to Take It Next With Temp Tables

On SQL Server? Read the SQL Server temp table version. More:

• MariaDB indexes and EXPLAIN
• INSERT INTO … SELECT

Frequently Asked Questions