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:
None 
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:03] tianfeng li
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()
[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.