Description:
When TempTable cannot satisfy an allocation, the server is supposed to fall
back to an on-disk InnoDB temporary table. `create_tmp_table_with_fallback()`
attempts that:
```c++
if (error == HA_ERR_RECORD_FILE_FULL &&
table->s->db_type() == temptable_hton) {
table->file = get_new_handler(
table->s, false, share->alloc_for_tmp_file_handler, innodb_hton);
error = table->file->create(...);
}
```
It replaces the handler of the passed `TABLE` only, and never updates
`TABLE_SHARE::db_plugin`. Two consequences:
1. The share keeps reporting TempTable, so `instantiate_tmp_table()` skips
`empty_record()` and the disk table is not counted in
`Created_tmp_disk_tables` (this is Bug#112556, still unfixed).
2. Any temporary table with several `TABLE` objects per `TABLE_SHARE` — a CTE
referenced more than once, or a recursive CTE — is left with clones still
holding TempTable handlers. Those clones never call `handler::create()`,
only `open_tmp_table()`; the stale handler misses in the TempTable
key-value store and returns `HA_ERR_NO_SUCH_TABLE`, which reaches the user
as:
```
ERROR 1146 (42S02): Table './tmp/var/tmp/mysqld.1/#sql1321ce_9_2' doesn't exist
```
An internal temporary table name is leaked to the user, and the actual
condition — out of temporary-table memory — is one the server is designed to
handle by spilling to disk.
`create_ondisk_from_heap()`, which handles overflow *while writing*, already
iterates `Derived_refs_iterator` and converts all clones. The create-time path
simply omits that.
How to repeat:
Debug build. `temptable_create_return_full` makes
`temptable::Handler::create()` return `HA_ERR_RECORD_FILE_FULL`, which is what
the allocator does when `temptable_max_ram` + `temptable_max_mmap` is
exhausted.
```sql
SET @@internal_tmp_mem_storage_engine = TempTable;
CREATE TABLE t (c INT);
INSERT INTO t VALUES (1), (2), (3);
SET debug = '+d,temptable_create_return_full';
-- (a) single-reference derived table: falls back correctly
SELECT * FROM (SELECT COUNT(*) FROM t GROUP BY c) AS dt;
-- (b) CTE referenced twice: FAILS
WITH cte AS (SELECT DISTINCT c FROM t)
SELECT c1.c, c2.c FROM cte AS c1 JOIN cte AS c2 ON c1.c = c2.c;
-- ERROR 1146 (42S02): Table '#sql...' doesn't exist
-- (c) recursive CTE: FAILS
WITH RECURSIVE seq (n) AS (
SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 5
)
SELECT * FROM seq;
-- ERROR 1146 (42S02): Table '#sql...' doesn't exist
```
That (a) succeeds while (b) and (c) fail isolates the problem to clone
handling. See the attached `temptable_fallback_cte.test`.
Without the debug injection, the same failure occurs whenever the global
budget is exhausted under concurrency:
```sql
SET GLOBAL tmp_table_size = 16777216000; -- deliberately large
SET GLOBAL temptable_max_mmap = 0;
SET GLOBAL temptable_max_ram = 2097152;
```
Note this is **independent of `tmp_table_size`**, which is why the error shows
up on servers that already have `tmp_table_size` in the hundreds of megabytes,
and why it is intermittent.
Notice:
- Bug#112556 is the missing `db_plugin` update in point 1. Fixing only that is
not sufficient: it corrects the share but exposes point 2, so both need to be
fixed together.
- Bug#33814188 / WL#14647 concerns the *per-table* `tmp_table_size` limit. The
throw here comes from `Prefer_RAM_over_MMAP_policy::block_source()`, i.e. the
*global* budget. Both raise `Result::RECORD_FILE_FULL`, which is why this is
often misdiagnosed as a `tmp_table_size` problem.
Suggested fix:
As patch attched
Description: When TempTable cannot satisfy an allocation, the server is supposed to fall back to an on-disk InnoDB temporary table. `create_tmp_table_with_fallback()` attempts that: ```c++ if (error == HA_ERR_RECORD_FILE_FULL && table->s->db_type() == temptable_hton) { table->file = get_new_handler( table->s, false, share->alloc_for_tmp_file_handler, innodb_hton); error = table->file->create(...); } ``` It replaces the handler of the passed `TABLE` only, and never updates `TABLE_SHARE::db_plugin`. Two consequences: 1. The share keeps reporting TempTable, so `instantiate_tmp_table()` skips `empty_record()` and the disk table is not counted in `Created_tmp_disk_tables` (this is Bug#112556, still unfixed). 2. Any temporary table with several `TABLE` objects per `TABLE_SHARE` — a CTE referenced more than once, or a recursive CTE — is left with clones still holding TempTable handlers. Those clones never call `handler::create()`, only `open_tmp_table()`; the stale handler misses in the TempTable key-value store and returns `HA_ERR_NO_SUCH_TABLE`, which reaches the user as: ``` ERROR 1146 (42S02): Table './tmp/var/tmp/mysqld.1/#sql1321ce_9_2' doesn't exist ``` An internal temporary table name is leaked to the user, and the actual condition — out of temporary-table memory — is one the server is designed to handle by spilling to disk. `create_ondisk_from_heap()`, which handles overflow *while writing*, already iterates `Derived_refs_iterator` and converts all clones. The create-time path simply omits that. How to repeat: Debug build. `temptable_create_return_full` makes `temptable::Handler::create()` return `HA_ERR_RECORD_FILE_FULL`, which is what the allocator does when `temptable_max_ram` + `temptable_max_mmap` is exhausted. ```sql SET @@internal_tmp_mem_storage_engine = TempTable; CREATE TABLE t (c INT); INSERT INTO t VALUES (1), (2), (3); SET debug = '+d,temptable_create_return_full'; -- (a) single-reference derived table: falls back correctly SELECT * FROM (SELECT COUNT(*) FROM t GROUP BY c) AS dt; -- (b) CTE referenced twice: FAILS WITH cte AS (SELECT DISTINCT c FROM t) SELECT c1.c, c2.c FROM cte AS c1 JOIN cte AS c2 ON c1.c = c2.c; -- ERROR 1146 (42S02): Table '#sql...' doesn't exist -- (c) recursive CTE: FAILS WITH RECURSIVE seq (n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 5 ) SELECT * FROM seq; -- ERROR 1146 (42S02): Table '#sql...' doesn't exist ``` That (a) succeeds while (b) and (c) fail isolates the problem to clone handling. See the attached `temptable_fallback_cte.test`. Without the debug injection, the same failure occurs whenever the global budget is exhausted under concurrency: ```sql SET GLOBAL tmp_table_size = 16777216000; -- deliberately large SET GLOBAL temptable_max_mmap = 0; SET GLOBAL temptable_max_ram = 2097152; ``` Note this is **independent of `tmp_table_size`**, which is why the error shows up on servers that already have `tmp_table_size` in the hundreds of megabytes, and why it is intermittent. Notice: - Bug#112556 is the missing `db_plugin` update in point 1. Fixing only that is not sufficient: it corrects the share but exposes point 2, so both need to be fixed together. - Bug#33814188 / WL#14647 concerns the *per-table* `tmp_table_size` limit. The throw here comes from `Prefer_RAM_over_MMAP_policy::block_source()`, i.e. the *global* budget. Both raise `Result::RECORD_FILE_FULL`, which is why this is often misdiagnosed as a `tmp_table_size` problem. Suggested fix: As patch attched