Bug #31119 the temp table is not auto drop
Submitted: 20 Sep 2007 20:28 Modified: 23 Oct 2007 6:50
Reporter: wag jialong Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:MySQL 5.0.45 OS:Linux (CentOs 4.4)
Assigned to: CPU Architecture:Any
Tags: temptable

[20 Sep 2007 20:28] wag jialong 
Description:
-rw-rw----   1 mysql mysql   20971520 Aug 18 23:27 #sql_36d7_0.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_0.MYI
-rw-rw----   1 mysql mysql    6303744 Aug 18 23:27 #sql_36d7_1.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_1.MYI
-rw-rw----   1 mysql mysql   44040192 Aug 18 23:27 #sql_36d7_12.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_12.MYI
-rw-rw----   1 mysql mysql   27262976 Aug 18 23:27 #sql_36d7_14.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_14.MYI
-rw-rw----   1 mysql mysql   12582912 Aug 18 23:27 #sql_36d7_2.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_2.MYI
-rw-rw----   1 mysql mysql   16797696 Aug 18 23:27 #sql_36d7_3.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_3.MYI
-rw-rw----   1 mysql mysql   88080384 Aug 18 23:27 #sql_36d7_4.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_4.MYI
-rw-rw----   1 mysql mysql   98566144 Aug 18 23:27 #sql_36d7_9.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:27 #sql_36d7_9.MYI
-rw-rw----   1 mysql mysql   18894848 Aug 22 00:39 #sql_3857_0.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_0.MYI
-rw-rw----   1 mysql mysql    2097152 Aug 22 00:39 #sql_3857_1.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_1.MYI
-rw-rw----   1 mysql mysql          0 Aug 22 00:39 #sql_3857_2.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_2.MYI
-rw-rw----   1 mysql mysql          0 Aug 22 00:39 #sql_3857_3.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_3.MYI
-rw-rw----   1 mysql mysql  123731968 Aug 22 00:39 #sql_3857_4.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_4.MYI
-rw-rw----   1 mysql mysql  119537664 Aug 22 00:39 #sql_3857_6.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_6.MYI
-rw-rw----   1 mysql mysql   79691776 Aug 22 00:39 #sql_3857_8.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_8.MYI
-rw-rw----   1 mysql mysql   75497472 Aug 22 00:39 #sql_3857_9.MYD
-rw-rw----   1 mysql mysql       1024 Aug 22 00:39 #sql_3857_9.MYI
-rw-rw----   1 mysql mysql  119409876 Aug 23 15:53 #sql_4fdf_1.MYD
-rw-rw----   1 mysql mysql       1024 Aug 23 15:53 #sql_4fdf_1.MYI
-rw-rw----   1 mysql mysql   12630544 Aug 18 23:26 #sql_75f4_0.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_0.MYI
-rw-rw----   1 mysql mysql  102760448 Aug 18 23:26 #sql_75f4_1.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:25 #sql_75f4_1.MYI
-rw-rw----   1 mysql mysql    2097152 Aug 18 23:26 #sql_75f4_10.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_10.MYI
-rw-rw----   1 mysql mysql    8388608 Aug 18 23:26 #sql_75f4_12.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_12.MYI
-rw-rw----   1 mysql mysql   18894848 Aug 18 23:26 #sql_75f4_13.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_13.MYI
-rw-rw----   1 mysql mysql    8388608 Aug 18 23:26 #sql_75f4_14.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_14.MYI
-rw-rw----   1 mysql mysql   18894848 Aug 18 23:26 #sql_75f4_2.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_2.MYI
-rw-rw----   1 mysql mysql   85983232 Aug 18 23:26 #sql_75f4_25.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:25 #sql_75f4_25.MYI
-rw-rw----   1 mysql mysql   20971520 Aug 18 23:26 #sql_75f4_28.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_28.MYI
-rw-rw----   1 mysql mysql  117440512 Aug 18 23:26 #sql_75f4_3.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:25 #sql_75f4_3.MYI
-rw-rw----   1 mysql mysql   16797696 Aug 18 23:26 #sql_75f4_33.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_33.MYI
-rw-rw----   1 mysql mysql    8388608 Aug 18 23:26 #sql_75f4_34.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_34.MYI
-rw-rw----   1 mysql mysql  111149056 Aug 18 23:26 #sql_75f4_4.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:25 #sql_75f4_4.MYI
-rw-rw----   1 mysql mysql   10485760 Aug 18 23:26 #sql_75f4_5.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_5.MYI
-rw-rw----   1 mysql mysql    2101248 Aug 18 23:26 #sql_75f4_6.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_6.MYI
-rw-rw----   1 mysql mysql   10485760 Aug 18 23:26 #sql_75f4_7.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_7.MYI
-rw-rw----   1 mysql mysql   50331648 Aug 18 23:26 #sql_75f4_8.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:26 #sql_75f4_8.MYI
-rw-rw----   1 mysql mysql  117440512 Aug 18 23:26 #sql_75f4_9.MYD
-rw-rw----   1 mysql mysql       1024 Aug 18 23:25 #sql_75f4_9.MYI
drwx------   5 mysql root       12288 Sep 21 04:02 .
drwxr-xr-x  13 root  mysql       4096 Aug 17 09:10 ..
-rw-rw----   1 mysql mysql  524288000 Sep 21 04:02 ibdata1
-rw-rw----   1 mysql mysql  209715200 Aug 22 09:39 ibdata2
-rw-rw----   1 mysql mysql     134349 Sep 21 04:02 localhost-relay-bin.008345
-rw-rw----   1 mysql mysql         49 Sep 21 03:56 localhost-relay-bin.index
-rw-rw----   1 mysql root     8566665 Sep 21 03:56 localhost.localdomain.err
-rw-rw----   1 mysql mysql          5 Sep 16 00:44 localhost.localdomain.pid
-rw-rw----   1 mysql mysql         83 Sep 21 04:02 master.info
drwx------   2 mysql root        4096 Aug 17 09:08 mysql
-rw-rw----   1 mysql mysql  232333756 Aug 17 10:01 mysql-bin.000001
-rw-rw----   1 mysql mysql      41313 Aug 17 10:02 mysql-bin.000002
-rw-rw----   1 mysql mysql  438879057 Aug 18 23:26 mysql-bin.000003
-rw-rw----   1 mysql mysql     333148 Aug 18 23:27 mysql-bin.000004
-rw-rw----   1 mysql mysql  642298055 Aug 22 00:39 mysql-bin.000005
-rw-rw----   1 mysql mysql     358043 Aug 22 09:39 mysql-bin.000006
-rw-rw----   1 mysql mysql   30429144 Aug 22 10:03 mysql-bin.000007
-rw-rw----   1 mysql mysql  187705658 Aug 23 15:53 mysql-bin.000008
-rw-rw----   1 mysql mysql 1073742099 Aug 30 21:57 mysql-bin.000009
-rw-rw----   1 mysql mysql  348586382 Sep  2 13:17 mysql-bin.000010
-rw-rw----   1 mysql mysql   17725260 Sep  2 15:46 mysql-bin.000011
-rw-rw----   1 mysql mysql  131151429 Sep  3 21:36 mysql-bin.000012
-rw-rw----   1 mysql mysql  924038611 Sep 14 08:16 mysql-bin.000013
-rw-rw----   1 mysql mysql  158573871 Sep 16 00:44 mysql-bin.000014
-rw-rw----   1 mysql mysql  457837915 Sep 21 04:02 mysql-bin.000015
-rw-rw----   1 mysql mysql        585 Sep 16 00:44 mysql-bin.index
-rw-rw----   1 mysql mysql         87 Sep 21 04:01 relay-log.info
drwx------   2 mysql root        4096 Aug 17 09:08 test

all the temp table cann't auto drop itself, and use a lot of disk. 

the temp table should in the memory or the disk ,but they must drop itsef,but not.

l don't know why , pls help me. thank you!

How to repeat:
in the detail, you can see the temp table created times at Aug 18,l fear if l want to drop all temp table on the disk.
[21 Sep 2007 14:44] Heikki Tuuri 
Those are MyISAM temp tables. Changing the Category.
[23 Sep 2007 6:50] Valeriy Kravchuk 
Thank you for a problem report. Please, send the entire error log. I think you had some crashes after creation of that temporary tables.
[23 Oct 2007 23:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".