Bug #53171 SHOW COLUMNS commands increment Created_tmp_disk_tables and Created_tmp_tables
Submitted: 26 Apr 2010 17:18 Modified: 26 Apr 2010 17:54
Reporter: Dima T Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.45 OS:Linux
Assigned to: CPU Architecture:Any

[26 Apr 2010 17:18] Dima T
Description:

> show variables like '%version%';
+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| protocol_version        | 10               |
| version                 | 5.1.45-1-log     |
| version_comment         | (Debian)         |
| version_compile_machine | x86_64           |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+
5 rows in set (0.00 sec)

How to repeat:
> create table t2 (c1 int);
Query OK, 0 rows affected (0.41 sec)

> show session status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 3     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 11    |
+-------------------------+-------+
3 rows in set (0.00 sec)

> show columns from t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

> show session status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 12    |
+-------------------------+-------+
3 rows in set (0.00 sec)
[26 Apr 2010 17:54] Valeriy Kravchuk
This is repeatable:

77-52-4-109:trunk openxs$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table t2(c1 int);
Query OK, 0 rows affected (0.08 sec)

mysql> show session status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show columns from t2;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> show session status like '%tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
3 rows in set (0.00 sec)

but why do you think it is a bug? SHOW statements use internal temporary tables to store the results, and our manual (http://dev.mysql.com/doc/refman/5.1/en/internal-temporary-tables.html) explains:

"Some conditions prevent the use of an in-memory temporary table, in which case the server uses an on-disk table instead:

    - Presence of a BLOB or TEXT column in the table
..."

Now look:

77-52-4-109:trunk openxs$ bin/mysql --column-type-info -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show columns from t2;
Field   1:  `Field`
Catalog:    `def`
Database:   `information_schema`
Table:      `COLUMNS`
Org_table:  `COLUMNS`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     192
Max_length: 2
Decimals:   0
Flags:      NOT_NULL 

Field   2:  `Type`
Catalog:    `def`
Database:   `information_schema`
Table:      `COLUMNS`
Org_table:  `COLUMNS`
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     589815
Max_length: 7
Decimals:   0
Flags:      NOT_NULL BLOB 

Field   3:  `Null`
Catalog:    `def`
Database:   `information_schema`
Table:      `COLUMNS`
Org_table:  `COLUMNS`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     9
Max_length: 3
Decimals:   0
Flags:      NOT_NULL 

Field   4:  `Key`
Catalog:    `def`
Database:   `information_schema`
Table:      `COLUMNS`
Org_table:  `COLUMNS`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     9
Max_length: 0
Decimals:   0
Flags:      NOT_NULL 

Field   5:  `Default`
Catalog:    `def`
Database:   `information_schema`
Table:      `COLUMNS`
Org_table:  `COLUMNS`
Type:       BLOB
Collation:  utf8_general_ci (33)
Length:     589815
Max_length: 0
Decimals:   0
Flags:      BLOB 

Field   6:  `Extra`
Catalog:    `def`
Database:   `information_schema`
Table:      `COLUMNS`
Org_table:  `COLUMNS`
Type:       VAR_STRING
Collation:  utf8_general_ci (33)
Length:     81
Max_length: 0
Decimals:   0
Flags:      NOT_NULL 

+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

so, this temporary table has 2 BLOB columns and, thus, is stored on disk. 

This is how it is implemented and documented, so this is not a bug formally.
[13 Jul 2012 9:40] Henrik Brinkmann
It is not a bug, but it makes server optimizing difficult e.g when you try to optimize your tables in order to avoid disk tables. The informative value of the status "Created_tmp_disk_tables" decreases with this behaviour.