Bug #112556 Created_tmp_disk_tables doesn't count on-disk temptable with fallback
| Submitted: | 28 Sep 2023 7:03 | Modified: | 2 Jul 2024 3:40 |
|---|---|---|---|
| Reporter: | tianfeng li (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Information schema | Severity: | S2 (Serious) |
| Version: | 8.0.30,8.4.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Contribution | ||
[28 Sep 2023 7:13]
tianfeng li
db_type-set-incorrectly-with-small-initial-tmp_table (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.
Contribution: 0001-db_type-set-incorrectly-with-small-initial-tmp_table.patch (application/octet-stream, text), 1.10 KiB.
[3 Oct 2023 6:40]
MySQL Verification Team
Hello tianfeng li, Thank you for the report and contribution. regards, Umesh
[2 Jul 2024 3:40]
tianfeng li
Note that affected in 8.4.0: mysql> Show status like '%Created_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | | Created_tmp_files | 5 | | Created_tmp_tables | 3 | +-------------------------+-------+ 3 rows in set (0.02 sec) mysql> Select count(distinct b) from t1; +-------------------+ | count(distinct b) | +-------------------+ | 30000 | +-------------------+ 1 row in set (6.78 sec) mysql> Show status like '%Created_tmp%'; +-------------------------+-------+ | Variable_name | Value | +-------------------------+-------+ | Created_tmp_disk_tables | 1 | | Created_tmp_files | 5 | | Created_tmp_tables | 4 | +-------------------------+-------+ 3 rows in set (0.01 sec)
Portions of this website are copyright © 2001, 2002 The PHP Group
Page generated in 0.053 sec. using MySQL 8.4.7-u6-cloud
Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the
respective copyright holders.
It is not reviewed in advance
by Oracle and does not necessarily represent the opinion of
Oracle or any other party.
Description: We want to create a scenario where the value of tmp_table_size is set to a small value, so that any SQL query that requires a temporary table will be forced to use on-disk storage instead of in-memory storage. So run as follows: ``` create table t1(a int, b blob); drop procedure if exists test; delimiter $ create procedure test() begin declare i int default 1; while i<=30000 do insert into t1 values(i,"123"+i); set i=i+1; end while; end $ delimiter ; call test(); set tmp_table_size=1024; Show global status like '%Created_tmp%'; # now we expect use a on disk tmp table. Select count(distinct b) from t1; Show global status like '%Created_tmp%'; # only Created_tmp_tables increased # In trace, also a tmp table, but not on disk tmp table. set session optimizer_trace="enabled=on",end_markers_in_json=on; Select count(distinct b) from t1; SELECT * from information_schema.optimizer_trace limit 1\G ``` In gdb, ensure that: the code try to create a temptable engine table, through create_tmp_table_with_fallback(), ``` int error = 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); error = table->file->create(share->table_name.str, table, &create_info, nullptr); } ``` now table->file->ht = innodb_ht but table->s->db_type() remains temptable_hton, so the following counter logic won't increase tmp_disk_tables counter: if (table->s->db_type() != temptable_hton) { thd->inc_status_created_tmp_disk_tables(); } also, trace_tmp_table logic won't treat this tmp table as a tmp_disk_table. Actually, table->s->db_type() is depended on many places, which may hide other dangerous bugs. How to repeat: As above, just create a scenario that use on-disk tmp table: ``` create table t1(a int, b blob); drop procedure if exists test; delimiter $ create procedure test() begin declare i int default 1; while i<=30000 do insert into t1 values(i,"123"+i); set i=i+1; end while; end $ delimiter ; call test(); set tmp_table_size=1024; Show global status like '%Created_tmp%'; # now we expect use a on disk tmp table. Select count(distinct b) from t1; Show global status like '%Created_tmp%'; # only Created_tmp_tables increased # In trace, also a tmp table, but not on disk tmp table. set session optimizer_trace="enabled=on",end_markers_in_json=on; Select count(distinct b) from t1; SELECT * from information_schema.optimizer_trace limit 1\G ``` Suggested fix: update table->s->db_type() in create_tmp_table_with_fallback()