Bug #73743 "Explain tablename" increases Created_tmp_disk_tables
Submitted: 27 Aug 2014 16:05 Modified: 28 Aug 2014 8:40
Reporter: Flavian C Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.19 5.5.36 OS:Any
Assigned to: CPU Architecture:Any
Tags: disk, MySQL, temporary table

[27 Aug 2014 16:05] Flavian C
Description:
One of My server was stormed by nearly 95% of Created_tmp_disk_tables. Today thanks to upgrade to 5.6 and performance_schema i quickly found that "EXPLAIN tablename" was fired continuously which was creating all the disk temporary table.

How to repeat:
CREATE TABLE `TEST` (
  `id` int(11)
) ENGINE=InnoDB;

show status like 'Created_tmp_disk_tables';

EXPLAIN TEST;

show status like 'Created_tmp_disk_tables';
[28 Aug 2014 8:35] MySQL Verification Team
Hello Flavian,

Thank you for the bug report.
The DESCRIBE|EXPLAIN statement provides information similar to SHOW COLUMNS, this very same issue has been reported and tracked in Bug #70433.

See, more explanation in comment [27 Sep 2013 8:23] Øystein Grøvlen in Bug #70433

Marking as duplicate of Bug #70433

Thanks,
Umesh