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)
• Status: Verified
• Category: MySQL Server: Information schema
• Severity: S2 (Serious)
• Version: 8.0.30,8.4.0
• OS: Any
• 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)