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)