MySQL Bugs: #29153: SHOW and INFORMATION_SCHEMA commands increment Created_tmp_disk

Bug #29153	SHOW and INFORMATION_SCHEMA commands increment Created_tmp_disk_tables
Submitted:	16 Jun 2007 0:02	Modified:	13 Nov 2008 16:21
Reporter:	Arjen Lentz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.19, 5.0.34, 5.0.38,5.1.20-BK	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any
Tags:	Created_tmp_disk_tables, information_schema, show, STATUS

Description:
Everytime when you issue a "SHOW COLUMNS/FIELDS from table command, the "Created_tmp_disk_table" status increments by 1.

How to repeat:
create table tmp (a int);

show local status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     | 
+-------------------------+-------+

show columns from tmp;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| a     | int(11) | YES  |     | NULL    |       | 
+-------+---------+------+-----+---------+-------+

show local status like 'Created_tmp_disk_tables';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     | 
+-------------------------+-------+

drop table tmp;

Suggested fix:
There is no good reason to have this tmp table go to disk, although I can suggest a probable cause: The internal table definition of the tmp table for the result probably contains a TEXT type field for a column rather than using VARCHAR. Given the increased size limits in 4.1/5.0 for varchar fields, having it be a TEXT type field is unnecessary.

I presume this would also be affecting similar queries through INFORMATION_SCHEMA as the underlying schema in sql/sql_show.cc is the same.

There appears to be a more general problem here, i.e. the problem is not limited to this one SHOW command....

Queries such as the below go rampant on (disk) temporary tables:
  select table_schema,table_name from information_schema.views limit 1;
  select distinct table_schema from information_schema.columns;
The count can increase by a dozen or more, possibly depending on the # of schemas in the server.

Seeing created_tmp_tables increase is no surprise as that's the current implementation of show and information_schema. But the DISK tables should be avoidable but using better column types. It's probably just the mapping of the SQL_TYPE_STRING in the schemas defined in sql_show.cc.

Having them created on disk is a side affect of using LONGTEXT types within the output definitions..

This is pretty much a duplicate of http://bugs.mysql.com/bug.php?id=10210 - although it seems that this was only fixed for SHOW STATUS. 

We do in fact increment other counters as well:

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

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

mysql> show local status like 'Created_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 4     | 
| Created_tmp_files       | 5     | 
| Created_tmp_tables      | 6     | 
+-------------------------+-------+
3 rows in set (0.00 sec)

5.1.25-rc-debug

mysql> select table_name,column_name,column_type from columns where table_schema='information_schema' and column_type like '%text%';
+-------------+-------------------------+-------------+
| table_name  | column_name             | column_type |
+-------------+-------------------------+-------------+
| COLUMNS     | COLUMN_DEFAULT          | longtext    | 
| COLUMNS     | COLUMN_TYPE             | longtext    | 
| EVENTS      | EVENT_DEFINITION        | longtext    | 
| EVENTS      | SQL_MODE                | longtext    | 
| PARTITIONS  | PARTITION_EXPRESSION    | longtext    | 
| PARTITIONS  | SUBPARTITION_EXPRESSION | longtext    | 
| PARTITIONS  | PARTITION_DESCRIPTION   | longtext    | 
| PLUGINS     | PLUGIN_DESCRIPTION      | longtext    | 
| PROCESSLIST | INFO                    | longtext    | 
| ROUTINES    | ROUTINE_DEFINITION      | longtext    | 
| ROUTINES    | SQL_MODE                | longtext    | 
| TRIGGERS    | ACTION_CONDITION        | longtext    | 
| TRIGGERS    | ACTION_STATEMENT        | longtext    | 
| TRIGGERS    | SQL_MODE                | longtext    | 
| TRIGGERS    | DEFINER                 | longtext    | 
| VIEWS       | VIEW_DEFINITION         | longtext    | 
+-------------+-------------------------+-------------+
16 rows in set

Look, VARCHAR from 5.0 supports up to nearly 64k. Even if we just roughly change the LONGTEXT columns to the maximum length varchar, it would be faster than it is now.
Leaving this as-is just makes infoschema unusable in many cases (many dbs/tables/columns) as it's way too slow!

That really wouldn't be a valid option for all of these tables however, to go with an easy example for a change, INFORMATION_SCHEMA.COLUMNS, defines COLUMN_DEFAULT and COLUMN_TYPE as:

ST_FIELD_INFO columns_fields_info[]=
{
...
  {"COLUMN_DEFAULT", MAX_FIELD_VARCHARLENGTH, MYSQL_TYPE_STRING, 0,
   1, "Default", OPEN_FRM_ONLY},
...
  {"COLUMN_TYPE", 65535, MYSQL_TYPE_STRING, 0, 0, "Type", OPEN_FRM_ONLY},
...
};

MAX_FIELD_VARCHARLENGTH is:

./sql/unireg.h:65:#define MAX_FIELD_VARCHARLENGTH       65535

So we are really defining the columns as the VARCHAR max value, however the INFORMATION_SCHEMA is also UTF8. 

You can fix this by pushing the fields down further, below is a test setting this to 21800:

[markleith@medusa:~/mysql] $ mysql -u root -S mysql-5.1.28.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.28-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show session status like 'created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 7     | 
| Created_tmp_files       | 5     | 
| Created_tmp_tables      | 34    | 
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show columns from columns;
+--------------------------+---------------------+------+-----+---------+-------+
| Field                    | Type                | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+---------+-------+
...
| COLUMN_DEFAULT           | varchar(21800)      | YES  |     | NULL    |       | 
...
| COLUMN_TYPE              | varchar(21800)      | NO   |     |         |       | 
...
+--------------------------+---------------------+------+-----+---------+-------+
19 rows in set (0.01 sec)

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

However, note that we have to restrict the columns also down to below the maximum row length as well - 65535 - so there may be cases where this is just not viable. 

I still question the need for these kinds of lengths with some of these columns however - especially:

EVENTS.SQL_MODE
ROUTINES.SQL_MODE
TRIGGERS.SQL_MODE

I can't ever see a SQL_MODE taking up over 1000 characters myself.. I could be wrong, but it would be nasty if so.

TRIGGERS.DEFINER

| Host                  | char(60)                          | NO   | PRI |         |       | 
| User                  | char(16)                          | NO   | PRI |         |       | 

This gives a max of say .. 80 characters (''@'' included) for a definer. 

However I understand why the rest of these are 65535 or greater, as they have to allow for 64k worth of data to be shown.

After some discussion with Peter and Dmitri about this, it obvious that at least TRIGGERS.DEFINER should not be a LONGTEXT anyway:

mysql> select table_schema, table_name, column_name, data_type from information_schema.columns where column_name like '%definer%';
+--------------------+------------+-------------+-----------+
| table_schema       | table_name | column_name | data_type |
+--------------------+------------+-------------+-----------+
| information_schema | EVENTS     | DEFINER     | varchar   | 
| information_schema | ROUTINES   | DEFINER     | varchar   | 
| information_schema | TRIGGERS   | DEFINER     | longtext  | 
| information_schema | VIEWS      | DEFINER     | varchar   | 
| mysql              | event      | definer     | char      | 
| mysql              | proc       | definer     | char      | 
+--------------------+------------+-------------+-----------+
6 rows in set (7.30 sec)

So this should be changed to a VARCHAR(77) to be consistent with the other tables. 

There is some discussion around whether the SQL_MODE columns could be reduced in size, Dmitri says:

[15:24] Leith: dlenev: thanks - would you say the SQL_MODE is set to LONGTEXT because of it being a SET too? Can you ever see the need for it being a LONGTEXT?
[15:24] Leith: dlenev: see http://bugs.mysql.com/bug.php?id=29153 for background
[15:30] dlenev: Leith: well... it needs to be long enough to include all modes.... indeed probably 65535 chars is too much for this purprose and so we can use something smaller... but also keep in mind that we probably want to be future proof... i.e. have some space reserved for future...
[15:31] Leith: dlenev: Indeed, however if we ever get to even something like 10000 characters for sql mode settings that would be *a lot* of settings 
[15:35] dlenev: Leith: sure. At the moment there are about 32 modes and longest name is around 26 chars... so yes... say 256 modes * 32 chars = 8192 chars....
Leith: dlenev: mind if I update the bug with these comments further?
[15:40] dlenev: Leith: no problem... I am 100% sure about TRIGGERS.DEFINER... as to sql_mode I am less sure...
[15:40] Leith: yea agreed
[15:41] dlenev: since I might be missing something...

Consensus on the other columns (that show the bodies of routines, default values etc.) seems to be that they are more appropriate as LONGTEXT, otherwise the bodies and defaults etc. could be truncated  on retrieval, which is obviously unacceptable unfortunately.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/54472

2746 Sergey Glukhov	2008-09-23
      Bug#29153 SHOW and INFORMATION_SCHEMA commands increment Created_tmp_disk_tables
      TRIGGERS.SQL_MODE, EVENTS.SQL_MODE, TRIGGERS.DEFINER:
      field type is changed to VARCHAR.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/55921

2773 Sergey Glukhov	2008-10-09
      Bug#29153 SHOW and INFORMATION_SCHEMA commands increment Created_tmp_disk_tables
      TRIGGERS.SQL_MODE, EVENTS.SQL_MODE, TRIGGERS.DEFINER:
      field type is changed to VARCHAR.

Pushed into 6.0.8-alpha  (revid:sergey.glukhov@sun.com-20081009120930-uiwgkh7w6n27x1pz) (version source revid:sergey.glukhov@sun.com-20081009130256-r0bdshg13fu9okav) (pib:5)

Pushed into 5.1.30  (revid:sergey.glukhov@sun.com-20081009120930-uiwgkh7w6n27x1pz) (version source revid:kgeorge@mysql.com-20081010130753-obt82wv52av801ed) (pib:5)

The versions are actually 5.1.31, 6.0.9.

Noted in 5.1.31, 6.0.9 changelogs.

Some SHOW statements and retrievals from the INFORMATION_SCHEMA
TRIGGERS and EVENTS tables used a temporary table and incremented the
Created_tmp_disk_tables status variable, due to the way that TEXT
columns are handled. The TRIGGERS.SQL_MODE, TRIGGERS.DEFINER, and
EVENTS.SQL_MODE columns now are VARCHAR to avoid this problem.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)