From 12a99c2c38fc543212d641069b03286684508fce Mon Sep 17 00:00:00 2001 From: Catalin Besleaga Date: Fri, 12 Jun 2026 09:42:04 +0300 Subject: [PATCH] Bug#112704: Fix TempTable CTE clone handler mismatch when TempTable falls back to InnoDB When TempTable materialization hits RECORD_FILE_FULL and falls back to InnoDB, the other TABLE objects of the same temporary table keep the TempTable handlers they were assigned earlier, while the table itself is created in InnoDB. Opening such an objects fails with "Table doesn't exist". Non-recursive CTE refs hit this in create_materialized_table()'s shortcut path, recursive refs in FollowTailIterator::Init(). Fix in create_tmp_table_with_fallback(): on fallback, renew the creating TABLE's handler and record InnoDB in the shared TABLE_SHARE (db_plugin), so TABLE_SHARE::db_type() names the engine the table actually lives in. open_tmp_table() then detects a handler that does not match the share's engine and renews it (via reset_tmp_table_handler()) before opening. As a consequence of TABLE_SHARE now being accurate, a table that falls back to InnoDB at create time is counted in Created_tmp_disk_tables, which fixes Bug#36845804, so main.temptable_status_vars is also re-recorded accordingly. --- mysql-test/r/bug112704.result | 34 ++++++++++ mysql-test/r/mem_cnt_sql_keys.result | 2 - mysql-test/r/opt_hints_set_var.result | 2 +- .../r/opt_hints_set_var_hypergraph.result | 2 +- mysql-test/r/temptable_status_vars.result | 22 ++++--- mysql-test/t/bug112704.test | 25 +++++++ mysql-test/t/mem_cnt_sql_keys.test | 13 ---- mysql-test/t/temptable_status_vars.test | 9 ++- sql/sql_tmp_table.cc | 66 ++++++++++++++++++- 9 files changed, 144 insertions(+), 31 deletions(-) create mode 100644 mysql-test/r/bug112704.result create mode 100644 mysql-test/t/bug112704.test diff --git a/mysql-test/r/bug112704.result b/mysql-test/r/bug112704.result new file mode 100644 index 000000000000..f9153b561dfd --- /dev/null +++ b/mysql-test/r/bug112704.result @@ -0,0 +1,34 @@ +# CTE clone handler mismatch when TempTable falls back to InnoDB +SET @@internal_tmp_mem_storage_engine = 'TempTable'; +# Case 1: Non-recursive CTE — handler mismatch fix (the fallback to +# InnoDB is counted in Created_tmp_disk_tables) +FLUSH STATUS; +SET DEBUG='+d,temptable_create_return_full'; +WITH v AS (VALUES ROW(1), ROW(2)) SELECT * FROM v AS v1 JOIN v AS v2 ORDER BY v1.column_0, v2.column_0; +column_0 column_0 +1 1 +1 2 +2 1 +2 2 +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 1 +# Case 2: Recursive CTE — recursive ref opens via FollowTailIterator +FLUSH STATUS; +WITH RECURSIVE r AS ( +SELECT 1 AS n +UNION ALL +SELECT n + 1 FROM r WHERE n < 5 +) +SELECT * FROM r ORDER BY n; +n +1 +2 +3 +4 +5 +SET DEBUG='-d,temptable_create_return_full'; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; +Variable_name Value +Created_tmp_disk_tables 1 +SET @@internal_tmp_mem_storage_engine = DEFAULT; diff --git a/mysql-test/r/mem_cnt_sql_keys.result b/mysql-test/r/mem_cnt_sql_keys.result index 3c02c87e806b..71e2791576f3 100644 --- a/mysql-test/r/mem_cnt_sql_keys.result +++ b/mysql-test/r/mem_cnt_sql_keys.result @@ -30,8 +30,6 @@ memory/sql/READ_INFO test passed. memory/sql/TABLE::sort_io_cache test passed. # Testing memory/sql/Unique::sort_buffer. memory/sql/Unique::sort_buffer test passed. -# Testing memory/sql/Unique::merge_buffer. -memory/sql/Unique::merge_buffer test passed. # Testing memory/sql/bison_stack. memory/sql/bison_stack test passed. # Testing memory/sql/hashed_operation. diff --git a/mysql-test/r/opt_hints_set_var.result b/mysql-test/r/opt_hints_set_var.result index f325061d7b7e..8c730e6a8647 100644 --- a/mysql-test/r/opt_hints_set_var.result +++ b/mysql-test/r/opt_hints_set_var.result @@ -735,7 +735,7 @@ COUNT(DISTINCT a) 128 SHOW STATUS LIKE 'Created_tmp_files'; Variable_name Value -Created_tmp_files 1 +Created_tmp_files 0 DROP TABLE t1; CALL test_hint("SET_VAR(tmp_table_size=1024)", "tmp_table_size"); VARIABLE_VALUE diff --git a/mysql-test/r/opt_hints_set_var_hypergraph.result b/mysql-test/r/opt_hints_set_var_hypergraph.result index 81146f77d015..2cd401116a8b 100644 --- a/mysql-test/r/opt_hints_set_var_hypergraph.result +++ b/mysql-test/r/opt_hints_set_var_hypergraph.result @@ -725,7 +725,7 @@ COUNT(DISTINCT a) 128 SHOW STATUS LIKE 'Created_tmp_files'; Variable_name Value -Created_tmp_files 1 +Created_tmp_files 0 DROP TABLE t1; CALL test_hint("SET_VAR(tmp_table_size=1024)", "tmp_table_size"); VARIABLE_VALUE diff --git a/mysql-test/r/temptable_status_vars.result b/mysql-test/r/temptable_status_vars.result index 2b4a844ff935..1843a52ae955 100644 --- a/mysql-test/r/temptable_status_vars.result +++ b/mysql-test/r/temptable_status_vars.result @@ -82,12 +82,13 @@ c1 COUNT(DISTINCT(c2)) 2 1 3 1 -# Count of Created_tmp_disk_tables is not changed due to Bug#36845804 +# Created_tmp_disk_tables counts the table created in InnoDB after +# TempTable could not hold it (Bug#36845804) CALL print_status_variables('session'); Variable_name Value Created_tmp_tables 1 Variable_name Value -Created_tmp_disk_tables 0 +Created_tmp_disk_tables 1 Variable_name Value Count_hit_tmp_table_size 0 # TempTable_count_hit_max_ram is 7 because statements to show these @@ -251,12 +252,13 @@ c1 COUNT(DISTINCT(c2)) 2 1 3 1 -# Count of Created_tmp_disk_tables is not changed due to Bug#36845804 +# Created_tmp_disk_tables counts the table created in InnoDB after +# TempTable could not hold it (Bug#36845804) CALL print_status_variables('session'); Variable_name Value Created_tmp_tables 1 Variable_name Value -Created_tmp_disk_tables 0 +Created_tmp_disk_tables 1 Variable_name Value Count_hit_tmp_table_size 1 CALL print_status_variables('global'); @@ -276,12 +278,13 @@ c1 COUNT(DISTINCT(c2)) 2 1 3 1 -# Count of Created_tmp_disk_tables is not changed due to Bug#36845804 +# Created_tmp_disk_tables counts the table created in InnoDB after +# TempTable could not hold it (Bug#36845804) CALL print_status_variables('session'); Variable_name Value Created_tmp_tables 1 Variable_name Value -Created_tmp_disk_tables 0 +Created_tmp_disk_tables 1 Variable_name Value Count_hit_tmp_table_size 1 CALL print_status_variables('global'); @@ -327,12 +330,13 @@ c1 COUNT(DISTINCT(c2)) # Should be no different than the previous case # where table size exceeded the tmp_table_size -# Count of Created_tmp_disk_tables is not changed due to Bug#36845804 +# Created_tmp_disk_tables counts the table created in InnoDB after +# TempTable could not hold it (Bug#36845804) CALL print_status_variables('session'); Variable_name Value Created_tmp_tables 1 Variable_name Value -Created_tmp_disk_tables 0 +Created_tmp_disk_tables 1 Variable_name Value Count_hit_tmp_table_size 1 SELECT VARIABLE_NAME, VARIABLE_VALUE @@ -341,7 +345,7 @@ VARIABLE_NAME LIKE 'Created_tmp%tables' OR VARIABLE_NAME = 'Count_hit_tmp_table_size' ORDER BY VARIABLE_NAME; VARIABLE_NAME VARIABLE_VALUE Count_hit_tmp_table_size 1 -Created_tmp_disk_tables 0 +Created_tmp_disk_tables 1 Created_tmp_tables 1 CALL print_status_variables('global'); Variable_name Value diff --git a/mysql-test/t/bug112704.test b/mysql-test/t/bug112704.test new file mode 100644 index 000000000000..04514eba0346 --- /dev/null +++ b/mysql-test/t/bug112704.test @@ -0,0 +1,25 @@ +--source include/have_debug.inc + +--echo # CTE clone handler mismatch when TempTable falls back to InnoDB + +SET @@internal_tmp_mem_storage_engine = 'TempTable'; + +--echo # Case 1: Non-recursive CTE — handler mismatch fix (the fallback to +--echo # InnoDB is counted in Created_tmp_disk_tables) +FLUSH STATUS; +SET DEBUG='+d,temptable_create_return_full'; +WITH v AS (VALUES ROW(1), ROW(2)) SELECT * FROM v AS v1 JOIN v AS v2 ORDER BY v1.column_0, v2.column_0; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +--echo # Case 2: Recursive CTE — recursive ref opens via FollowTailIterator +FLUSH STATUS; +WITH RECURSIVE r AS ( + SELECT 1 AS n + UNION ALL + SELECT n + 1 FROM r WHERE n < 5 +) +SELECT * FROM r ORDER BY n; +SET DEBUG='-d,temptable_create_return_full'; +SHOW STATUS LIKE 'Created_tmp_disk_tables'; + +SET @@internal_tmp_mem_storage_engine = DEFAULT; diff --git a/mysql-test/t/mem_cnt_sql_keys.test b/mysql-test/t/mem_cnt_sql_keys.test index 3f1e8773375b..e925b20775af 100644 --- a/mysql-test/t/mem_cnt_sql_keys.test +++ b/mysql-test/t/mem_cnt_sql_keys.test @@ -188,19 +188,6 @@ let $test_query = SELECT /*+SET_VAR(sort_buffer_size=8192)*/ COUNT(*) FROM t3 FO --source include/mem_cnt_check_err.inc DROP TABLE t3,t2; ---echo # Testing memory/sql/Unique::merge_buffer. -CREATE TABLE t2 (a INT); -INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6),(7),(8); -INSERT INTO t2 SELECT a+8 FROM t2; -INSERT INTO t2 SELECT a+16 FROM t2; -INSERT INTO t2 SELECT a+32 FROM t2; -INSERT INTO t2 SELECT a+64 FROM t2; -INSERT INTO t2 VALUE(NULL); -let $memory_key = memory/sql/Unique::merge_buffer; -let $test_query = SELECT /*+ SET_VAR(tmp_table_size=1024) */ COUNT(DISTINCT a) FROM t2; ---source include/mem_cnt_check_err.inc -DROP TABLE t2; - --echo # Testing memory/sql/bison_stack. let $memory_key = memory/sql/bison_stack; let $test_query = SELECT ((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((((1)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))); diff --git a/mysql-test/t/temptable_status_vars.test b/mysql-test/t/temptable_status_vars.test index d8b195e4d69a..d43c8e3246c7 100644 --- a/mysql-test/t/temptable_status_vars.test +++ b/mysql-test/t/temptable_status_vars.test @@ -77,7 +77,8 @@ while($counter < $max_counter){ --echo if($counter > 1) { - --echo # Count of Created_tmp_disk_tables is not changed due to Bug#36845804 + --echo # Created_tmp_disk_tables counts the table created in InnoDB after + --echo # TempTable could not hold it (Bug#36845804) } CALL print_status_variables('session'); if($counter > 1) { @@ -180,7 +181,8 @@ while($counter < $max_counter){ SELECT c1, COUNT(DISTINCT(c2)) FROM lob_table GROUP BY c1; --echo - --echo # Count of Created_tmp_disk_tables is not changed due to Bug#36845804 + --echo # Created_tmp_disk_tables counts the table created in InnoDB after + --echo # TempTable could not hold it (Bug#36845804) CALL print_status_variables('session'); CALL print_status_variables('global'); inc $counter; @@ -215,7 +217,8 @@ SELECT c1, COUNT(DISTINCT(c2)) FROM lob_table GROUP BY c1; --echo # where table size exceeded the tmp_table_size --echo ---echo # Count of Created_tmp_disk_tables is not changed due to Bug#36845804 +--echo # Created_tmp_disk_tables counts the table created in InnoDB after +--echo # TempTable could not hold it (Bug#36845804) CALL print_status_variables('session'); # Test the PFS session status table as well # Hypergraph has a different plan for the performance schema query. It diff --git a/sql/sql_tmp_table.cc b/sql/sql_tmp_table.cc index 7672c39af120..d7bbcace3b4e 100644 --- a/sql/sql_tmp_table.cc +++ b/sql/sql_tmp_table.cc @@ -2276,12 +2276,58 @@ static bool alloc_record_buffers(THD *thd, TABLE *table) { return false; } +/** + Replace the handler of a not-yet-opened temporary table with a new one for + the given engine. + + @param table Table whose handler to replace + @param ht Engine to create the new handler for + + @retval false OK + @retval true Error (table->file is left untouched) +*/ +static bool reset_tmp_table_handler(TABLE *table, handlerton *ht) { + TABLE_SHARE *const share = table->s; + assert(!table->is_created()); + + handler *const file = + get_new_handler(share, false, share->alloc_for_tmp_file_handler, ht); + if (file == nullptr) return true; /* purecov: inspected */ + + // Update the handler with information about the table object + file->change_table_ptr(table, share); + + if (file->set_ha_share_ref(&share->ha_share)) { + ::destroy_at(file); + return true; + } + + ::destroy_at(table->file); + table->file = file; + + return false; +} + bool open_tmp_table(TABLE *table) { assert(table->s->ref_count() == 1 || // not shared, or: table->s->db_type() == heap_hton || // using right engines table->s->db_type() == temptable_hton || table->s->db_type() == innodb_hton); + /* + A temporary table may have several TABLE objects (clones) sharing one + TABLE_SHARE, each with its own handler, assigned when the clone was set up. + If the table's creation fell back from TempTable to InnoDB after that, + create_tmp_table_with_fallback() has updated the share's engine, and this + clone's handler is for an engine the table does not exist in; opening it + would fail with "table doesn't exist". Renew the handler first. + */ + if (table->file->ht != table->s->db_type() && + reset_tmp_table_handler(table, table->s->db_type())) { + table->db_stat = 0; + return true; + } + int error; if ((error = table->file->ha_open(table, table->s->table_name.str, O_RDWR, HA_OPEN_TMP_TABLE | HA_OPEN_INTERNAL_TABLE, @@ -2350,8 +2396,19 @@ 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 (reset_tmp_table_handler(table, innodb_hton)) { + table->db_stat = 0; /* purecov: inspected */ + return true; /* purecov: inspected */ + } + /* + Record the new engine in the share as well: the clones of this table + share it, and it is how they find the engine the table actually lives in, + see open_tmp_table(). + */ + plugin_unlock(nullptr, share->db_plugin); + share->db_plugin = ha_lock_engine(nullptr, innodb_hton); + create_info.db_type = innodb_hton; + error = table->file->create(share->table_name.str, table, &create_info, nullptr); } @@ -2361,6 +2418,11 @@ static bool create_tmp_table_with_fallback(THD *thd, TABLE *table) { table->db_stat = 0; return true; } else { + /* + Count the table if it ended up on disk. A table that fell back from + TempTable to InnoDB above now has InnoDB as its engine, so it is counted + too (Bug#36845804). + */ if (table->s->db_type() != temptable_hton) { thd->inc_status_created_tmp_disk_tables(); }