Bug #64672 innodb_file_per_table does not affect temporary tables
Submitted: 16 Mar 2012 17:46 Modified: 17 Apr 2012 12:02
Reporter: Garrett Smith Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.5 OS:Linux
Assigned to: CPU Architecture:Any

[16 Mar 2012 17:46] Garrett Smith
Description:
Tables created using "CREATE TEMPORARY TABLE ..." aren't created in separate innodb files when innodb_file_per_table is set. The result is that the ibdata1 can grow unbounded in size even when innodb_file_per_table is set.

How to repeat:
Create an InnoDb table using "CREATE TEMPORARY TABLE ..." and write data to it. Note the increase in the ibdata1. This space cannot be reclaimed without rebuilding the db.
[17 Mar 2012 12:02] Valeriy Kravchuk
You are wrong in your assumptions about the reason why ibdata* grow. Look:

macbook-pro:5.5 openxs$ bin/mysql -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 3
Server version: 5.5.20-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> show variables like 'innodb_file%';
+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_file_format       | Antelope |
| innodb_file_format_check | ON       |
| innodb_file_format_max   | Antelope |
| innodb_file_per_table    | ON       |
+--------------------------+----------+
4 rows in set (0.02 sec)

mysql> create table ti1(c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.61 sec)

mysql> create temporary table ti2(c1 int) engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> show variables like 'tmpdir';
+---------------+----------------------------------------------------+
| Variable_name | Value                                              |
+---------------+----------------------------------------------------+
| tmpdir        | /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/ |
+---------------+----------------------------------------------------+
1 row in set (0.00 sec)

So, normal InnoDB table is created in datadir/test (I check in other shell):

macbook-pro:5.5 openxs$ ls -l data/test/ti[12]*
-rw-rw----  1 openxs  staff   8556 17 бер 13:56 data/test/ti1.frm
-rw-rw----  1 openxs  staff  98304 17 бер 13:56 data/test/ti1.ibd

while temporary InnoDB table is created (with separate .ibd file, as expected!) in tmpdir:

macbook-pro:5.5 openxs$ ls -l /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/total 29240
-rw-rw----  1 openxs  staff      8556 17 бер 13:56 #sql8d33_3_3.frm
-rw-rw----  1 openxs  staff     98304 17 бер 13:56 #sql8d33_3_3.ibd
drwxr-xr-x@ 2 openxs  staff        68 11 січ 06:55 FPInstallMountPoint
drwxr-xr-x@ 3 openxs  staff       102 11 січ 06:55 FPUnpackPath
drwx------  2 openxs  staff        68  8 січ 17:43 PreviewTemp-3WtTgh
...

Note that #sql8d33_3_3.ibd file and the fact that our connection id was 3. This is the .ibd file of the temporary table we created.

My check is on Mac OS X, but you can find out on Linux it is all the same actually...
[18 Apr 2012 1: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".