Description:
One of the key reasons to enable innodb_file_per_table is to gain the ability to regain disk space without having to re-create the entire InnoDB tablespace.
The TRUNCATE command should re-create an InnoDB table (except in certain circumstances when there are FKs, etc).
Thus, it would stand to reason that TRUNCATE should essentially re-create an .idb file. This is not currently the case.
How to repeat:
1. Set innodb_file_per_table=1
2. Create an InnoDB table
3. Insert many rows into the table
4. TRUNCATE the table
5. Confirm size of associated .ibd file
mysql 5.1.19-beta (root) [test]> CREATE TABLE ib1 (id bigint not null auto_increment primary key) engine=innodb;
Query OK, 0 rows affected (0.17 sec)
kolbe@beluga:~/MySQL/inst/5.1.19> ls -lh ./data/test/ib1.ibd
-rw-rw---- 1 kolbe users 96K 2007-06-18 19:09 ./data/test/ib1.ibd
mysql 5.1.19-beta (root) [test]> insert into ib1 () values ();
Query OK, 1 row affected (0.02 sec)
mysql 5.1.19-beta (root) [test]> insert into ib1 select null from ib1;
...
mysql 5.1.19-beta (root) [test]> select count(*) from ib1;
+----------+
| count(*) |
+----------+
| 524288 |
+----------+
1 row in set (0.04 sec)
kolbe@beluga:~/MySQL/inst/5.1.19> ls -lh ./data/test/ib1.ibd
-rw-rw---- 1 kolbe users 36M 2007-06-18 19:11 ./data/test/ib1.ibd
mysql 5.1.19-beta (root) [test]> truncate ib1;
mysql 5.1.19-beta (root) [test]> select count(*) from ib1;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
kolbe@beluga:~/MySQL/inst/5.1.19> ls -lh ./data/test/ib1.ibd
-rw-rw---- 1 kolbe users 36M 2007-06-18 19:11 ./data/test/ib1.ibd
Suggested fix:
TRUNCATE should clear the .ibd file, unless, possibly, InnoDB is forced to use underlying DELETE instead of TRUNCATE.
Perhaps TRUNCATE should even literally map to DROP/CREATE.
It would be very nice to be able to use TRUNCATE to truncate the data file as well as the table.
