Bug #118536 Concurrent CTE queries cause erroneous missing temporary table errors

• Submitted: 26 Jun 17:55
• Modified: 27 Jun 7:24
• Reporter: Chrysanthemum Swingler
• Status: Analyzing
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.40, 9.3.0
• OS: Linux (RDS, Docker)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any (Tested on x86_64 and aarch64)

[26 Jun 17:55] Chrysanthemum Swingler

Description:
Running multiple concurrent Concurrent Table Expression (WITH...) queries will cause MySQL to start returning "1146 (42S02): Table './mysqltmp/#sql1_1cb5_e' doesn't exist" errors to a portion of those queries given enough running concurrently. 

In my testing, I was able to reproduce this in RDS with MySQL 8.0.40 running on a db.x2iedn.xlarge, and I was also able to reproduce this in the official MySQL docker container, running v 9.3.0 running in Docker on a M2Pro MacBook Pro.

The contents of the table and the predicate of the query seem to be of no consequence - this is consistently reproducible against an empty table.

Upon activating the extended slow query log, you can see that it reports returning error 1146 back to the client, reporting "Created_tmp_tables: 1", and "Created_tmp_disk_tables: 0". 

Returned to client:
```
Table './mysqltmp/#sql1_2ed4_0' doesn't exist"
```

Logged from server:

```
# Time: 2025-06-26T17:47:41.166787Z
# User@Host: locust[locust] @  [192.168.65.1]  Id: 12266
# Query_time: 0.000092  Lock_time: 0.000001 Rows_sent: 0  Rows_examined: 0 Thread_id: 12266 Errno: 1146 Killed: 0 Bytes_received: 290 Bytes_sent: 100 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 1 Count_hit_tmp_table_size: 0  Start: 2025-06-26T17:47:41.166695Z End: 2025-06-26T17:47:41.166787Z
SET timestamp=1750960061;
WITH RECURSIVE parents AS (
    SELECT
        t1.id, t1.id3 as breadcrumb
    FROM t1
    WHERE id = 1 AND id2 = 1

    UNION ALL

    SELECT
        t1.id, t1.id3
    FROM t1
    JOIN parents ON (
        t1.id = t1.id3
        AND NOT t1.id2 = 1
    )
)
```

How to repeat:
Create a Docker container using the official upstream image from https://hub.docker.com/_/mysql with the default settings.

Create a table with the following definition:

```
CREATE TABLE `t1` (
    `id` int NOT NULL AUTO_INCREMENT,
    `id2` int NOT NULL, 
    `id3` int DEFAULT NULL,
    `id4` int NOT NULL,
    PRIMARY KEY (`id`),
    KEY `k_id3` (`id3`), 
    KEY `k_id2` (`id2`), 
    KEY `k_id4` (`id4`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
```

Run the following query with a concurrency over 1600:

```
WITH RECURSIVE parents AS (
    SELECT
        t1.id, t1.id3 as breadcrumb
    FROM t1
    WHERE id = 1 AND id2 = 1

    UNION ALL

    SELECT
        t1.id, t1.id3
    FROM t1
    JOIN parents ON (
        t1.id = t1.id3
        AND NOT t1.id2 = 1
    )
)

SELECT id from parents;
```

A portion of those queries will to fail with the error "1146 (42S02): Table './mysqltmp/#sql1_2ed4_0' doesn't exist" as you start running more than 1600 concurrently.