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: | |
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
[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.