MySQL Bugs: #58800: "TRUNCATE ibtable;" does not reclaim space when ran with --innodb-file-per-table

Bug #58800	"TRUNCATE ibtable;" does not reclaim space when ran with --innodb-file-per-table
Submitted:	7 Dec 2010 20:04	Modified:	6 Jan 2011 20:56
Reporter:	Tobias Asplund	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.77 / 5.1.43	OS:	Any
Assigned to:	John Russell	CPU Architecture:	Any

Description:
As per http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html an InnoDB table without foreign keys will DROP and re-CREATE a table when the command TRUNCATE TABLE is used, this seems to not be the case.
In this case it's verified with the --innodb-file-per-table flag turned on.

Manually dropping and re-creating the table will create an empty ibd-file, but TRUNCATE will not.

How to repeat:
mysql> show create table blah \G
*************************** 1. row ***************************
       Table: blah
Create Table: CREATE TABLE `blah` (
  `ip` int(10) unsigned NOT NULL,
  `request_time` timestamp NULL DEFAULT NULL,
  `time_zone` char(5) NOT NULL,
  `http_request` text NOT NULL,
  `return_code` smallint(5) unsigned NOT NULL,
  `bytes` int(10) unsigned DEFAULT NULL,
  `referrer` text,
  `browser` text,
  `a` int(11) DEFAULT NULL,
  KEY `return_code` (`return_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

-rw-rw----  1 mysql mysql       8862 Aug 12 10:21 blah.frm
-rw-rw----  1 mysql mysql  293601280 Dec  7 21:00 blah.ibd

mysql> show table status like 'blah'\G
*************************** 1. row ***************************
           Name: blah
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1156821
 Avg_row_length: 228
    Data_length: 264011776
Max_data_length: 0
   Index_length: 15220736
      Data_free: 7340032
 Auto_increment: NULL
    Create_time: 2010-08-12 10:22:30
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.05 sec)

Ok, let's TRUNCATE it:

mysql> TRUNCATE blah;
Query OK, 0 rows affected (0.08 sec)

mysql> show table status like 'blah'\G
*************************** 1. row ***************************
           Name: blah
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 16384
      Data_free: 286261248
 Auto_increment: NULL
    Create_time: 2010-08-12 10:22:30
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

-rw-rw----  1 mysql mysql       8862 Aug 12 10:21 blah.frm
-rw-rw----  1 mysql mysql  293601280 Dec  7 21:02 blah.ibd

An OPTIMIZE will do the job after the TRUNCATE, but that's not ATOMIC :/

Tobias, IMHO, this is designed as such.
In the innodb plugin, it will return space.
Prior to innodb plugin, you must run OPTIMIZE TABLE after truncate.

Please, check if my theory work.

http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html

Fair enough :)

The documentation should probably by updated slightly, though.

Thanks.

Thank you for the feedback.

I agree text on http://dev.mysql.com/doc/refman/5.1/en/truncate-table.html is a bit confusing, probably link to http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html there will work. So verified as documentation issue.

Updated the TRUNCATE TABLE section with appropriate references to file_per_table and Plugin.