MySQL Bugs: #56536: CREATE TABLE … doesn't initialize data_length, index

Bug #56536	CREATE TABLE … doesn't initialize data_length, index_length
Submitted:	3 Sep 2010 13:10	Modified:	5 Oct 2010 16:12
Reporter:	Paolo Lunazzi	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.1.43	OS:	Linux
Assigned to:	Jimmy Yang	CPU Architecture:	Any
Tags:	create, data_free, innodb, truncate

Description:
Hi all,

according to the manual "If there are no FOREIGN KEY constraints, InnoDB performs fast truncation by dropping the original table and creating an empty one with the same definition, which is much faster than deleting rows one by one.".

I tested on mysql 5.1.43/Linux/x86_64 that if I do a truncate table then data_free is the same size of table before truncate. If I drop/create table data_free is obviously 0 so I think that MySQL deleting rows one by one even if there aren't any FOREIGN KEY constraints that reference the table.

How to repeat:
Create a Innodb table without foreign key and insert some data.
Do a show table status and see Data_length and Data_free values, then truncate table and redo a show table status. See how Data_free is grow.

Thank you for the report.

I see Data_length = 0 after TRUNCATE and Data_free is 0 everytime. Also version 5.1.43 is old. Please try with version 5.1.50 and if problem still exists provide your configuration file.

Tried with 5.1.50:

mysql> show table status\G
*************************** 1. row ***************************
           Name: so_hits
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 1401342
 Avg_row_length: 65
    Data_length: 91881472
Max_data_length: 0
   Index_length: 92536832
      Data_free: 4194304
 Auto_increment: NULL
    Create_time: 2010-09-10 13:37:53
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

mysql> truncate table so_motime_hits_live;
Query OK, 0 rows affected (0.28 sec)

mysql> show table status\G
*************************** 1. row ***************************
           Name: so_hits
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 32768
      Data_free: 187695104
 Auto_increment: NULL
    Create_time: 2010-09-10 13:37:53
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: 
        Comment: 
1 row in set (0.00 sec)

Thank you for the feedback.

Most of data were freed really: 91881472 > 16384

Please send us output of SHOW CREATE TABLE so_hits, so we can re-create the problem.

But if the truncate table drops the table like it is written on the manual, data_free must be 0. In fact if I do manually a drop/create data_free is 0.
Below the create table.

mysql> show create table so__hits\G
*************************** 1. row ***************************
       Table: so_motime_hits_live
Create Table: CREATE TABLE `so_hits` (
  `id` bigint(20) unsigned NOT NULL,
  `file_id` int(10) unsigned NOT NULL,
  `offset` int(10) unsigned NOT NULL,
  `tld` char(2) NOT NULL,
  `ev_date` date NOT NULL,
  `ev_hour` tinyint(3) unsigned NOT NULL,
  `step_id` tinyint(3) unsigned NOT NULL,
  `spare` char(1) DEFAULT NULL,
  `cmp` int(11) DEFAULT NULL,
  `flight` int(11) DEFAULT NULL,
  `crt` int(11) DEFAULT NULL,
  `leaf` smallint(5) unsigned NOT NULL,
  `site` smallint(5) DEFAULT NULL,
  `state` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Id_File` (`file_id`,`offset`),
  KEY `idx` (`cmp`,`flight`,`crt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Thank you for the feedback.

Verified as described, although this can be expected, because every storage engine can use own implementation of TRUNCATE and InnoDB does.

Test case for MTR:

--source include/have_innodb.inc

CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL,
  `file_id` int(10) unsigned NOT NULL,
  `offset` int(10) unsigned NOT NULL,
  `tld` char(2) NOT NULL,
  `ev_date` date NOT NULL,
  `ev_hour` tinyint(3) unsigned NOT NULL,
  `step_id` tinyint(3) unsigned NOT NULL,
  `spare` char(1) DEFAULT NULL,
  `cmp` int(11) DEFAULT NULL,
  `flight` int(11) DEFAULT NULL,
  `crt` int(11) DEFAULT NULL,
  `leaf` smallint(5) unsigned NOT NULL,
  `site` smallint(5) DEFAULT NULL,
  `state` tinyint(3) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Id_File` (`file_id`,`offset`),
  KEY `idx` (`cmp`,`flight`,`crt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--disable_query_log
--disable_warnings
let $i=1000;
while ($i)
{
--eval insert into t1 values($i,$i,$i,'$i',curdate(),$i,$i,'$i',$i,$i,$i,$i,$i,$i)
dec $i;
}
--enable_warnings
--enable_query_log

--vertical_results
show table status like 't1';
truncate table t1;
show table status like 't1';
drop table t1;
create table t1(f1 text);
show table status like 't1';

The built-in InnoDB in MySQL 5.1 implements TRUNCATE by dropping and re-creating the index trees within the same tablespace. It will not delete and re-create the *.ibd file, nor shrink the *.ibd file before re-creating the index trees of the table.

The InnoDB Plugin in MySQL 5.1 implements TRUNCATE by dropping the tablespace and creating a new one before creating the index trees. There, data_free should be 0, just like after CREATE TABLE. Can you confirm this?

Same problem with Plugin.

Before truncate:

Data_length     98304
Max_data_length 0
Index_length    114688
Data_free       4194304

After truncate:

Data_length     16384
Max_data_length 0
Index_length    32768
Data_free       4194304

After drop/create:

Data_length     0
Max_data_length 281474976710655
Index_length    1024
Data_free       0

Sveta, could you do DROP/TRUNCATE with the Plugin and innodb-file-per-table=1?
This should work as Marko described.

Michael,

re-tested with both settings. With --innodb_file_per_table results are:

before TRUNCATE;
Data_length     98304
Max_data_length 0
Index_length    114688
Data_free       0

after TRUNCATE;
Data_length     16384
Max_data_length 0
Index_length    32768
Data_free       0

after DROP/CREATE:
Data_length     0
Max_data_length 281474976710655
Index_length    1024
Data_free       0

Still interesting why Data_length is not 0 after TRUNCATE

let's leave it to Marko to sort it out. To me it's interesting that 
data_free was 0 before any action, with innodb-file-per-table, is it a test
deficiency or an InnoDB being generally ignorant of data_free, as
the rest of the bug suggests.
Now that MySQL and InnoDB are one it would be nice to handle
all those small things properly.
As for TRUNCATE action itself, well we do our best and there's a number of
bugs/feature requests that say we should do better, Marko is the expert here.

Sveta,
Your test demonstrates that TRUNCATE TABLE in InnoDB Plugin works as advertised. The table comprises three B-tree indexes:

* the clustered index (organized by PRIMARY KEY(id))
* two secondary indexes: UNIQUE KEY Id_File and KEY idx

The minimum size of the table is 3 B-tree root pages of 16384 bytes each, one per index. Thus, this is correct:

after TRUNCATE;
Data_length     16384
Max_data_length 0
Index_length    32768
Data_free       0

What is incorrect is that CREATE TABLE is not initializing the fields:

after DROP/CREATE:
Data_length     0
Max_data_length 281474976710655
Index_length    1024
Data_free       0

Well, after drop and recreate, the table seems to be a MyISAM table, and the incorrect value:

mysql> create table t1(f1 text);
Query OK, 0 rows affected (0.09 sec)

mysql> show table status like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| t1   | MyISAM |      10 | Dynamic    |    0 |              0 |           0 | 281474976710655 |         1024 |         0 |           NULL | 2010-09-29 20:01:06 | 2010-09-29 20:01:06 | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

If it is an InnoDB table, the fields are correctly initialized.

mysql> drop table t1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t1(f1 text) engine = innodb;
Query OK, 0 rows affected (0.14 sec)

mysql> show table status like 't1';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| t1   | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |   4194304 |           NULL | 2010-09-29 20:04:36 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

Jimmy,

correct, I forgot option engine in my test when created second table. I think in case of MyISAM this should be not a bug as it handles table data differently, so closing it as such.