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

[7 Dec 2010 20:04] Tobias Asplund
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 :/
[8 Dec 2010 5:33] MySQL Verification Team
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.
[8 Dec 2010 5:35] MySQL Verification Team
http://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-other-changes-truncate.html
[10 Dec 2010 2:37] Tobias Asplund
Fair enough :)

The documentation should probably by updated slightly, though.

Thanks.
[14 Dec 2010 20:55] Sveta Smirnova
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.
[6 Jan 2011 20:56] John Russell
Updated the TRUNCATE TABLE section with appropriate references to file_per_table and Plugin.