From bba80d9d8943432cfa0a8650bb24f69017004c5d Mon Sep 17 00:00:00 2001 From: tianfengli Date: Tue, 8 Sep 2026 11:38:08 +0800 Subject: [PATCH] Fix incomplete TempTable-to-InnoDB fallback for multiply-referenced CTEs When TempTable cannot satisfy an allocation, create_tmp_table_with_fallback() is supposed to fall back to an on-disk InnoDB temporary table. It only replaced the handler of the TABLE it was passed, and never updated TABLE_SHARE::db_plugin. This had two consequences. First, the share kept reporting TempTable, so instantiate_tmp_table() skipped empty_record() and the on-disk table was not counted in Created_tmp_disk_tables (Bug#112556). Second, a temporary table with several TABLE objects per TABLE_SHARE - a CTE referenced more than once, or a recursive CTE - was left with clones still holding TempTable handlers. Those clones never call handler::create(), only open_tmp_table(), so the stale handler missed in the TempTable key-value store and returned HA_ERR_NO_SUCH_TABLE, reaching the user as: ERROR 1146 (42S02): Table '#sql1321ce_9_2' doesn't exist leaking an internal temporary table name for a condition the server is meant to handle by spilling to disk. This is independent of tmp_table_size: the allocation fails against the global temptable_max_ram + temptable_max_mmap budget, which is why it also occurs on servers with a large tmp_table_size and why it is intermittent under concurrency. create_ondisk_from_heap(), which handles overflow while writing, already converts all clones via Derived_refs_iterator; the create-time path omitted that. Add switch_tmp_table_share_to_ondisk(), which updates share->db_plugin and re-creates the handler of every TABLE reachable through Derived_refs_iterator, restoring set_ha_share_ref(), change_table_ptr() and init_cost_model() for each. It is simpler than create_ondisk_from_heap() because at first instantiation no clone is open, so no rows are migrated and no cursors repositioned. Also assert in open_tmp_table() that the handler matches the engine recorded in the share, so this class of inconsistency fails loudly in debug builds. Add mysql-test/t/temptable_fallback_cte.test, which uses the existing temptable_create_return_full debug injection to cover a single-reference derived table, a CTE referenced twice, a recursive CTE, a recursive CTE with an extra clone, and the Created_tmp_disk_tables accounting. --- mysql-test/r/temptable_fallback_cte.result | 125 +++++++++++++++++++++ mysql-test/t/temptable_fallback_cte.test | 110 ++++++++++++++++++ sql/sql_tmp_table.cc | 87 +++++++++++++- 3 files changed, 320 insertions(+), 2 deletions(-) create mode 100644 mysql-test/r/temptable_fallback_cte.result create mode 100644 mysql-test/t/temptable_fallback_cte.test diff --git a/mysql-test/r/temptable_fallback_cte.result b/mysql-test/r/temptable_fallback_cte.result new file mode 100644 index 00000000000..705df4072e2 --- /dev/null +++ b/mysql-test/r/temptable_fallback_cte.result @@ -0,0 +1,125 @@ +# +# TempTable-to-InnoDB fallback at create time leaves stale handlers on +# TABLE clones, so a multiply-referenced or recursive CTE fails with +# ER_NO_SUCH_TABLE instead of spilling to disk. +# +# 'temptable_create_return_full' makes temptable::Handler::create() +# return HA_ERR_RECORD_FILE_FULL, which is exactly what the TempTable +# allocator does when the global temptable_max_ram + temptable_max_mmap +# budget is exhausted. The server must then fall back to InnoDB for the +# whole TABLE_SHARE, including every TABLE object referencing it. +# +SET @@internal_tmp_mem_storage_engine = TempTable; +CREATE TABLE t (c INT); +INSERT INTO t VALUES (1), (2), (3); +ANALYZE TABLE t; +Table Op Msg_type Msg_text +test.t analyze status OK +# +# Case 1: derived table referenced once (single TABLE, no clone). +# This works even without the fix; kept as a regression guard. +# +SET debug = '+d,temptable_create_return_full'; +SELECT * FROM (SELECT COUNT(*) AS cnt FROM t GROUP BY c) AS dt ORDER BY 1; +cnt +1 +1 +1 +SET debug = '-d,temptable_create_return_full'; +# +# Case 2: non-recursive CTE referenced twice. +# One TABLE_SHARE, two TABLE objects. Only the first calls +# handler::create(); the second only calls open_tmp_table(). +# Without the fix the second TABLE still holds a TempTable handler, +# its open() misses the TempTable key-value store and reports +# HA_ERR_NO_SUCH_TABLE -> ER_NO_SUCH_TABLE. +# +SET debug = '+d,temptable_create_return_full'; +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 ORDER BY 1; +c c +1 1 +2 2 +3 3 +SET debug = '-d,temptable_create_return_full'; +# +# Case 3: recursive CTE. +# The recursive reference is opened much later, from +# FollowTailIterator::Init(), and hits the very same stale handler. +# +SET debug = '+d,temptable_create_return_full'; +WITH RECURSIVE seq (n) AS ( +SELECT 1 +UNION ALL +SELECT n + 1 FROM seq WHERE n < 5 +) +SELECT * FROM seq ORDER BY n; +n +1 +2 +3 +4 +5 +SET debug = '-d,temptable_create_return_full'; +# +# Case 4: recursive CTE referenced more than once, i.e. both a +# recursive reference and an extra non-recursive clone. +# +SET debug = '+d,temptable_create_return_full'; +WITH RECURSIVE seq (n) AS ( +SELECT 1 +UNION ALL +SELECT n + 1 FROM seq WHERE n < 4 +) +SELECT s1.n, s2.n FROM seq AS s1 JOIN seq AS s2 ON s1.n = s2.n ORDER BY 1; +n n +1 1 +2 2 +3 3 +4 4 +SET debug = '-d,temptable_create_return_full'; +# +# Case 5: the fallback must be accounted for in +# Created_tmp_disk_tables. Every materialization below ends up in +# InnoDB, so the counter must grow. +# +SET debug = '+d,temptable_create_return_full'; +SELECT VARIABLE_VALUE INTO @before +FROM performance_schema.session_status +WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'; +WITH cte AS (SELECT DISTINCT c FROM t) +SELECT COUNT(*) FROM cte AS c1 JOIN cte AS c2 ON c1.c = c2.c; +COUNT(*) +3 +SELECT VARIABLE_VALUE INTO @after +FROM performance_schema.session_status +WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'; +# Must report 1: the on-disk fallback was counted. +SELECT (@after > @before) AS disk_table_counted; +disk_table_counted +1 +SET debug = '-d,temptable_create_return_full'; +# +# Case 6: without the injection the same queries stay in TempTable +# and must return identical results. +# +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 ORDER BY 1; +c c +1 1 +2 2 +3 3 +WITH RECURSIVE seq (n) AS ( +SELECT 1 +UNION ALL +SELECT n + 1 FROM seq WHERE n < 5 +) +SELECT * FROM seq ORDER BY n; +n +1 +2 +3 +4 +5 +DROP TABLE t; +SET @@internal_tmp_mem_storage_engine = default; diff --git a/mysql-test/t/temptable_fallback_cte.test b/mysql-test/t/temptable_fallback_cte.test new file mode 100644 index 00000000000..7758dbe585e --- /dev/null +++ b/mysql-test/t/temptable_fallback_cte.test @@ -0,0 +1,110 @@ +--source include/have_debug.inc +--source include/not_hypergraph.inc # Depends on internal temporary tables. + +--echo # +--echo # TempTable-to-InnoDB fallback at create time leaves stale handlers on +--echo # TABLE clones, so a multiply-referenced or recursive CTE fails with +--echo # ER_NO_SUCH_TABLE instead of spilling to disk. +--echo # +--echo # 'temptable_create_return_full' makes temptable::Handler::create() +--echo # return HA_ERR_RECORD_FILE_FULL, which is exactly what the TempTable +--echo # allocator does when the global temptable_max_ram + temptable_max_mmap +--echo # budget is exhausted. The server must then fall back to InnoDB for the +--echo # whole TABLE_SHARE, including every TABLE object referencing it. +--echo # + +SET @@internal_tmp_mem_storage_engine = TempTable; + +CREATE TABLE t (c INT); +INSERT INTO t VALUES (1), (2), (3); +ANALYZE TABLE t; + +--echo # +--echo # Case 1: derived table referenced once (single TABLE, no clone). +--echo # This works even without the fix; kept as a regression guard. +--echo # +SET debug = '+d,temptable_create_return_full'; +SELECT * FROM (SELECT COUNT(*) AS cnt FROM t GROUP BY c) AS dt ORDER BY 1; +SET debug = '-d,temptable_create_return_full'; + +--echo # +--echo # Case 2: non-recursive CTE referenced twice. +--echo # One TABLE_SHARE, two TABLE objects. Only the first calls +--echo # handler::create(); the second only calls open_tmp_table(). +--echo # Without the fix the second TABLE still holds a TempTable handler, +--echo # its open() misses the TempTable key-value store and reports +--echo # HA_ERR_NO_SUCH_TABLE -> ER_NO_SUCH_TABLE. +--echo # +SET debug = '+d,temptable_create_return_full'; +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 ORDER BY 1; +SET debug = '-d,temptable_create_return_full'; + +--echo # +--echo # Case 3: recursive CTE. +--echo # The recursive reference is opened much later, from +--echo # FollowTailIterator::Init(), and hits the very same stale handler. +--echo # +SET debug = '+d,temptable_create_return_full'; +WITH RECURSIVE seq (n) AS ( + SELECT 1 + UNION ALL + SELECT n + 1 FROM seq WHERE n < 5 +) +SELECT * FROM seq ORDER BY n; +SET debug = '-d,temptable_create_return_full'; + +--echo # +--echo # Case 4: recursive CTE referenced more than once, i.e. both a +--echo # recursive reference and an extra non-recursive clone. +--echo # +SET debug = '+d,temptable_create_return_full'; +WITH RECURSIVE seq (n) AS ( + SELECT 1 + UNION ALL + SELECT n + 1 FROM seq WHERE n < 4 +) +SELECT s1.n, s2.n FROM seq AS s1 JOIN seq AS s2 ON s1.n = s2.n ORDER BY 1; +SET debug = '-d,temptable_create_return_full'; + +--echo # +--echo # Case 5: the fallback must be accounted for in +--echo # Created_tmp_disk_tables. Every materialization below ends up in +--echo # InnoDB, so the counter must grow. +--echo # +SET debug = '+d,temptable_create_return_full'; +--disable_result_log +SELECT VARIABLE_VALUE INTO @before + FROM performance_schema.session_status + WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'; +--enable_result_log + +WITH cte AS (SELECT DISTINCT c FROM t) +SELECT COUNT(*) FROM cte AS c1 JOIN cte AS c2 ON c1.c = c2.c; + +--disable_result_log +SELECT VARIABLE_VALUE INTO @after + FROM performance_schema.session_status + WHERE VARIABLE_NAME = 'Created_tmp_disk_tables'; +--enable_result_log + +--echo # Must report 1: the on-disk fallback was counted. +SELECT (@after > @before) AS disk_table_counted; +SET debug = '-d,temptable_create_return_full'; + +--echo # +--echo # Case 6: without the injection the same queries stay in TempTable +--echo # and must return identical results. +--echo # +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 ORDER BY 1; + +WITH RECURSIVE seq (n) AS ( + SELECT 1 + UNION ALL + SELECT n + 1 FROM seq WHERE n < 5 +) +SELECT * FROM seq ORDER BY n; + +DROP TABLE t; +SET @@internal_tmp_mem_storage_engine = default; diff --git a/sql/sql_tmp_table.cc b/sql/sql_tmp_table.cc index ff511a27b5e..698bd7b2075 100644 --- a/sql/sql_tmp_table.cc +++ b/sql/sql_tmp_table.cc @@ -2240,6 +2240,9 @@ bool open_tmp_table(TABLE *table) { table->s->db_type() == temptable_hton || table->s->db_type() == innodb_hton); + // The handler must match the engine currently recorded in the share. + assert(table->file->ht == table->s->db_type()); + int error; if ((error = table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE, @@ -2256,6 +2259,82 @@ bool open_tmp_table(TABLE *table) { return false; } +/** + Switch a not-yet-instantiated internal temporary table from TempTable to + InnoDB, updating the TABLE_SHARE and the handler of every TABLE sharing it. + + Replacing all handlers is what makes this correct: a multiply-referenced or + recursive CTE has several TABLE objects per TABLE_SHARE, and the ones that + are not the writer never call handler::create(), only open_tmp_table(). A + handler left over from TempTable would look the table up in the TempTable + key-value store, miss it, and report HA_ERR_NO_SUCH_TABLE. + + This is the create-time counterpart of create_ondisk_from_heap(), but much + simpler: no clone is created or open yet, so there are no rows to migrate + and no cursors to reposition. + + @param thd Thread handler + @param table Table being instantiated + + @retval false OK, share and all clones now use InnoDB + @retval true Error (an error has been reported) +*/ +static bool switch_tmp_table_share_to_ondisk(THD *thd, TABLE *table) { + TABLE_SHARE *const share = table->s; + + assert(share->db_type() == temptable_hton); + // Must only be called before the table is instantiated in any engine. + assert(share->tmp_open_count == 0); + + const plugin_ref old_plugin = share->db_plugin; + share->db_plugin = ha_lock_engine(nullptr, innodb_hton); + if (share->db_plugin == nullptr) { + share->db_plugin = old_plugin; + return true; /* purecov: inspected */ + } + + auto switch_table = [&](TABLE *switched) -> bool { + // A clone without a handler yet will pick up the new engine on its own. + if (switched == nullptr || switched->file == nullptr) return false; + assert(!switched->is_created()); + + /* + The superseded handler is not destroyed: it lives on + TABLE_SHARE::alloc_for_tmp_file_handler and is reclaimed with that + MEM_ROOT, as in create_ondisk_from_heap(). + */ + switched->file = get_new_handler( + share, false, share->alloc_for_tmp_file_handler, innodb_hton); + if (switched->file == nullptr) return true; /* purecov: inspected */ + if (switched->file->set_ha_share_ref(&share->ha_share)) + return true; /* purecov: inspected */ + switched->file->change_table_ptr(switched, share); + // Cost constants are per storage engine. + switched->init_cost_model(thd->cost_model()); + return false; + }; + + bool table_switched = false; + if (table->pos_in_table_list != nullptr) { + // Possibly there are clones (multiply-referenced or recursive CTE). + Derived_refs_iterator ref_it(table->pos_in_table_list); + while (TABLE *t = ref_it.get_next()) { + if (switch_table(t)) return true; /* purecov: inspected */ + if (t == table) table_switched = true; + } + } + // No clones, or 'table' is not among them: switch it explicitly. + if (!table_switched && switch_table(table)) + return true; /* purecov: inspected */ + + plugin_unlock(nullptr, old_plugin); + + // Row type may differ between the two engines, so re-evaluate it. + set_real_row_type(table); + + return false; +} + /** Try to create an in-memory temporary table and if not enough space, then try to create an on-disk one. @@ -2308,10 +2387,14 @@ static bool create_tmp_table_with_fallback(THD *thd, TABLE *table) { table->file->create(share->table_name.str, table, &create_info, nullptr); 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); + if (switch_tmp_table_share_to_ondisk(thd, table)) return true; + + create_info.db_type = table->s->db_type(); + create_info.row_type = table->s->row_type; + error = table->file->create(share->table_name.str, table, &create_info, nullptr); + if (error == 0) empty_record(table); } if (error) { -- 2.43.7