Bug #39200 optimize table does not recognize ROW_FORMAT=COMPRESSED
Submitted: 2 Sep 2008 20:34 Modified: 30 Jun 2010 6:15
Reporter: Jeff C Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S3 (Non-critical)
Version:5.1.42 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any
Tags: optimize table, row_format=compressed

[2 Sep 2008 20:34] Jeff C
Description:
I created a compressed table.  I added indexes to test speeds (great).
I noticed the size of the *.ibd had nearly doubled.

I removed the indexes and then optimized the table.

The *.idb was even larger now.

alter table <table> row_format=compressed;   <-- returned it back to the original state.

My question:  Should optimize table already recognize the table being compressed?

How to repeat:
create table testing (id int unsigned not null auto_increment,name varchar(100) not null, primary key(id)) engine=innodb;
insert ignore into testing (name) values (.....);
alter table testing row_format=compressed (barracuda already set as default from my.cnf)

Note size of *.idb

alter table testing add index (name);

Note size of *.ibd

alter table testing drop index name;

optimize table testing;

Note size of *.ibd

alter table testing row_format=compressed;

Suggested fix:
Maybe remap 'optimize table' to 'alter table .. row_format=compressed' if the table is innodb, barracuda, and compressed.

Thanks
[3 Sep 2008 6:18] Sveta Smirnova
Thank you for the report.

Verified as described with following test case:

$cat bug39200.test
--source include/have_innodb.inc

create table testing (id int unsigned not null auto_increment,name varchar(100) not null, primary key(id)) engine=innodb;
insert into testing (name) values ('foo');
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;

--exec ls -la $MYSQL_TEST_DIR/var/master-data/test

alter table testing row_format=compressed;

--exec ls -la $MYSQL_TEST_DIR/var/master-data/test

optimize table testing;

--exec ls -la $MYSQL_TEST_DIR/var/master-data/test

Result:
create table testing (id int unsigned not null auto_increment,name varchar(100) not null, primary key(id)) engine=innodb;
insert into testing (name) values ('foo');
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
total 120
drwxr-xr-x  2 ssmirnova ssmirnova  4096 Sep  3 09:17 .
drwxr-xr-x  4 ssmirnova ssmirnova  4096 Sep  3 09:17 ..
-rw-rw----  1 ssmirnova ssmirnova  8586 Sep  3 09:17 testing.frm
-rw-rw----  1 ssmirnova ssmirnova 98304 Sep  3 09:17 testing.ibd
alter table testing row_format=compressed;
total 88
drwxr-xr-x  2 ssmirnova ssmirnova  4096 Sep  3 09:17 .
drwxr-xr-x  4 ssmirnova ssmirnova  4096 Sep  3 09:17 ..
-rw-rw----  1 ssmirnova ssmirnova  8586 Sep  3 09:17 testing.frm
-rw-rw----  1 ssmirnova ssmirnova 65536 Sep  3 09:17 testing.ibd
optimize table testing;
Table   Op      Msg_type        Msg_text
test.testing    optimize        status  OK
total 120
drwxr-xr-x  2 ssmirnova ssmirnova  4096 Sep  3 09:17 .
drwxr-xr-x  4 ssmirnova ssmirnova  4096 Sep  3 09:17 ..
-rw-rw----  1 ssmirnova ssmirnova  8586 Sep  3 09:17 testing.frm
-rw-rw----  1 ssmirnova ssmirnova 98304 Sep  3 09:17 testing.ibd
[24 Sep 2008 6:43] Marko Mäkelä
When OPTIMIZE falls back to ALTER TABLE, it should preserve all table attributes. Apparently, it does not preserve ROW_FORMAT. Thus, I think that this is a MySQL server bug.

The InnoDB plugin could probably work around this bug by implementing the optimize method that is defined in the MySQL 5.1 handler class.
[7 May 2009 9:36] Tatiana Azundris Nuernberg
## uses UNIXisms, relies on certain file-sizes, etc.  Example only, do not add to repo!
## needs -master.opt with --innodb-file-per-table=1 --innodb_file_format=barracuda

--source include/have_innodb.inc

let $MYSQLD_DATADIR= `select @@datadir`;

create table testing (id int unsigned not null auto_increment,
                      name varchar(100) not null,
                      primary key(id))
                     engine=innodb;
# row_format=compressed;
insert into testing (name) values ('foo');
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;
insert into testing (name) select name from testing;

show create table testing;
select row_format from information_schema.tables where table_name='testing';
--exec ls -shk1 $MYSQLD_DATADIR/test/
--exec  od -x $MYSQLD_DATADIR/test/testing.frm|head -3|tail -1|cut -c31-33

# alter table testing row_format=redundant;
alter table testing row_format=compressed;
show create table testing;
select row_format from information_schema.tables where table_name='testing';
--exec ls -shk1 $MYSQLD_DATADIR/test/
--exec  od -x $MYSQLD_DATADIR/test/testing.frm|head -3|tail -1|cut -c31-33

optimize table testing;
show create table testing;
select row_format from information_schema.tables where table_name='testing';
--exec ls -shk1 $MYSQLD_DATADIR/test/
--exec  od -x $MYSQLD_DATADIR/test/testing.frm|head -3|tail -1|cut -c31-33

# --sleep 60
drop table testing;
[7 May 2009 9:43] Tatiana Azundris Nuernberg
...
optimize table testing;
test.testing	optimize	note	Table does not support optimize, doing recreate + analyze instead

show create table testing;
CREATE TABLE `testing` ... ( ... ) ENGINE=InnoDB ROW_FORMAT=COMPRESSED

select row_format from information_schema.tables where table_name='testing';
row_format
Compact
total 116
 12 testing.frm
104 testing.ibd

03

At this point, INFORMATION_SCHEMA says COMPACT, while SHOW CREATE says COMPRESSED. The .frm says 0x03, which is ROW_TYPE_COMPRESSED, and the .ibd file-size corresponds to COMPACT.

Note that all agreed on COMPACT at CREATE-time, and on COMPRESSED after ALTER.
[12 May 2009 9:44] Tatiana Azundris Nuernberg
bool mysql_recreate_table(THD *thd, TABLE_LIST *table_list)
{
  ...
  create_info.used_fields |= HA_CREATE_USED_ROW_FORMAT;

It's so obvious in retrospective.
[27 May 2009 16:31] Tatiana Azundris Nuernberg
firstpatch fixes bug described herein without obviously affecting maria.

this

--innodb-file-per-table=1 --innodb_file_format=barracuda --debug

CREATE TABLE t1 (a INT, b VARCHAR(100)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
ALTER TABLE t1 AUTO_INCREMENT = 1;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';

still gives

CREATE TABLE t1 (a INT, b VARCHAR(100)) ENGINE=InnoDB ROW_FORMAT=COMPRESSED;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
row_format
Compressed
ALTER TABLE t1 AUTO_INCREMENT = 1;
SELECT row_format FROM INFORMATION_SCHEMA.TABLES WHERE table_name='t1';
row_format
Compact

though, which is ... not good.

=== modified file 'sql/sql_table.cc'
--- sql/sql_table.cc    2009-05-19 04:25:36 +0000
+++ sql/sql_table.cc    2009-05-26 14:23:11 +0000
@@ -6307,7 +6307,10 @@ view_err:
   }

   if (create_info->row_type == ROW_TYPE_NOT_USED)
+  {
     create_info->row_type= table->s->row_type;
+    create_info->used_fields|= HA_CREATE_USED_ROW_FORMAT;
+  }

fixes this issue, but will affect maria3.result

Cleared new behavior with monty, extended tests for 6.0 -- thanks monty, thanks Davi!
[29 May 2009 14:55] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75258

2910 Tatiana A. Nurnberg	2009-05-29
      Bug#39200: optimize table does not recognize ROW_FORMAT=COMPRESSED
      
      When doing ALTER TABLE, we forgot to point out that we actually have
      ROW_FORMAT information (from the original table), so we dropped to
      "sensible defaults". This affects both ALTER TABLE and OPTIMIZE TABLE
      which may fall back on ALTER TABLE for InnoDB.
      
      We now flag that we do indeed know the row-type, thereby preserving
      compression-type etc.
      
      No .test in 5.1 since we'd need a reasonable new plugin from InnoDB to
      show this properly; in higher versions, maria can demonstrate this.
     @ sql/sql_table.cc
        In mysql_alter_table() flag that we have row-type info
        from old table. In compare_tables(), we must explicitly
        check whether row-type has changed (rather than rely on
        the flag which will always be set at this point now).
[29 May 2009 15:30] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/75268

3324 Tatiana A. Nurnberg	2009-05-29 [merge]
      Bug#39200: optimize table does not recognize ROW_FORMAT=COMPRESSED
      
      When doing ALTER TABLE, we forgot to point out that we actually have
      ROW_FORMAT information (from the original table), so we dropped to
      "sensible defaults". This affects both ALTER TABLE and OPTIMIZE TABLE
      which may fall back on ALTER TABLE for InnoDB.
      
      We now flag that we do indeed know the row-type, thereby preserving
      compression-type etc.
     @ mysql-test/suite/maria/r/maria3.result
        test updated and extended after clearing with monty.
     @ mysql-test/suite/maria/t/maria3.test
        test updated and extended after clearing with monty.
     @ sql/sql_table.cc
        In mysql_alter_table() flag that we have row-type info
        from old table. In compare_tables(), change whether row
        type has actually changed, rather than rely on the flag
        (which will always be set now).
[29 May 2009 16:16] Tatiana Azundris Nuernberg
queued for 5.1.36, 6.0.12 in bugteam
[16 Jun 2009 11:03] Bugs System
Pushed into 5.1.36 (revid:joro@sun.com-20090616102155-3zhezogudt4uxdyn) (version source revid:azundris@mysql.com-20090529164935-xe3dceff53d7pywb) (merge vers: 5.1.36) (pib:6)
[17 Jun 2009 19:27] Bugs System
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090616183122-chjzbaa30qopdra9) (version source revid:azundris@mysql.com-20090529170733-wxq9j0idmss9rllz) (merge vers: 6.0.12-alpha) (pib:11)
[21 Jul 2009 19:10] Paul DuBois
Noted in 5.1.36, 5.4.4 changelogs.

ALTER TABLE neglected to preserve ROW_FORMAT information from the
original table, which could cause subsequent ALTER TABLE and OPTIMIZE
TABLE statements to lose the row format for InnoDB tables.
[12 Aug 2009 22:50] Paul DuBois
Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.
[15 Aug 2009 2:07] Paul DuBois
Ignore previous comment about 5.4.2.
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-7.0.8 (revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[26 Aug 2009 13:46] Bugs System
Pushed into 5.1.37-ndb-6.3.27 (revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (version source revid:jonas@mysql.com-20090826105955-bkj027t47gfbamnc) (merge vers: 5.1.37-ndb-6.3.27) (pib:11)
[26 Aug 2009 13:48] Bugs System
Pushed into 5.1.37-ndb-6.2.19 (revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (version source revid:jonas@mysql.com-20090825194404-37rtosk049t9koc4) (merge vers: 5.1.37-ndb-6.2.19) (pib:11)
[27 Aug 2009 16:33] Bugs System
Pushed into 5.1.35-ndb-7.1.0 (revid:magnus.blaudd@sun.com-20090827163030-6o3kk6r2oua159hr) (version source revid:jonas@mysql.com-20090826132541-yablppc59e3yb54l) (merge vers: 5.1.37-ndb-7.0.8) (pib:11)
[6 Oct 2009 9:01] Bugs System
Pushed into 5.1.40 (revid:joro@sun.com-20091006073316-lea2cpijh9r6on7c) (version source revid:satya.bn@sun.com-20090923121212-4x70dbou02bbq88k) (merge vers: 5.1.40) (pib:11)
[8 Oct 2009 19:42] Paul DuBois
The 5.4 fix has been pushed to 5.4.2.
[22 Oct 2009 6:36] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha) (pib:13)
[22 Oct 2009 7:09] Bugs System
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version source revid:alik@sun.com-20091013094238-g67x6tgdm9a7uik0) (merge vers: 5.5.0-beta) (pib:13)
[22 Oct 2009 19:50] Paul DuBois
Noted in 5.5.0, 6.0.14 changelogs.
[15 Dec 2009 22:10] James Day
The fix for this bug introduced bug #46760, blocking fast alter table for InnoDB tables. This was fixed in versions 5.1.40, 5.5.0 (and the former 6.0.14).
[18 Dec 2009 10:40] Bugs System
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 10:56] Bugs System
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 11:10] Bugs System
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 11:24] Bugs System
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)
[28 Jan 2010 16:39] Harrison Fisk
This bug fix does not seem to work any more.  I believe that the fix for Bug #46760 reverted it.  It looks like the test case for that bug uses SHOW CREATE TABLE which reports one thing, whereas SHOW TABLE STATUS reports another value.

Here is a test case with 5.1.42/InnoDB plugin 1.0.6:

mysql> show variables like 'innodb_file_%';
+--------------------------+-----------+
| Variable_name            | Value     |
+--------------------------+-----------+
| innodb_file_format       | Barracuda |
| innodb_file_format_check | Barracuda |
| innodb_file_per_table    | ON        |
+--------------------------+-----------+
3 rows in set (0.00 sec)

mysql> create table t1 (id int, b varchar(100), primary key (id)) engine=innodb row_format=compressed;
Query OK, 0 rows affected (0.01 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compressed
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-01-28 11:33:59
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

mysql> optimize table t1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| test.t1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.03 sec)

mysql> show table status like 't1'\G
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2010-01-28 11:34:13
    Update_time: NULL
     Check_time: NULL
      Collation: latin1_swedish_ci
       Checksum: NULL
 Create_options: row_format=COMPRESSED
        Comment: 
1 row in set (0.00 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL DEFAULT '0',
  `b` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)

Notice that the Row_format has changed in SHOW TABLE STATUS, whereas SHOW CREATE TABLE still shows the correct thing.

I tested this with 5.1.38 and it worked fine.
[29 Jan 2010 17:26] Valeriy Kravchuk
Bug #50733 was marked as a duplicate of this one.
[24 Apr 2010 13:55] Harald Reindl
The same happens with 5.1.46

> InnoDB Plugin has been upgraded to version 1.0.7. 
> This version is considered of General Availability (GA) quality

Not really :-(
[8 Jun 2010 20:59] Rob Finch
I can confirm this is still an issue in 5.1.46. I recently went through a large optimize of our innodb files using file_per_table and they ended up in row_format=compact. Here's an illustration of the bug being present in 5.1.46 on a test machine that was synced with real data not long ago. There was no other activity on this machine.

mysql> select ENGINE,ROW_FORMAT,CREATE_OPTIONS from information_schema.TABLES where ENGINE='InnoDB' and TABLE_NAME='dvo_ro_dups'
+--------+------------+-----------------------+
| ENGINE | ROW_FORMAT | CREATE_OPTIONS        |
+--------+------------+-----------------------+
| InnoDB | Compressed | row_format=COMPRESSED |
+--------+------------+-----------------------+

Note the CREATE_OPTIONS.

Initial size:
-rw-rwx--- 1 mysql mysql  4290772992 2010-05-31 08:13 dvo_ro_dups.ibd

Optimize:

mysql> optimize table dvo_ro_dups;

| ng1.dvo_ro_dups | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ng1.dvo_ro_dups | optimize | status   | OK

Verify the bug is present and reverts table to compact:

mysql> select ENGINE,ROW_FORMAT,CREATE_OPTIONS from information_schema.TABLES where ENGINE='InnoDB' and TABLE_NAME='dvo_ro_dups';
+--------+------------+-----------------------+
| ENGINE | ROW_FORMAT | CREATE_OPTIONS        |
+--------+------------+-----------------------+
| InnoDB | Compact    | row_format=COMPRESSED |
+--------+------------+-----------------------+

Current filesize with compact:

-rw-rw---- 1 mysql mysql  5452595200 2010-06-08 16:29 dvo_ro_dups.ibd

Switch back to compressed:

mysql> alter table dvo_ro_dups row_format=compressed;

Validate it switched:

mysql> select ENGINE,ROW_FORMAT,CREATE_OPTIONS from information_schema.TABLES where ENGINE='InnoDB' and TABLE_NAME='dvo_ro_dups'
+--------+------------+-----------------------+
| ENGINE | ROW_FORMAT | CREATE_OPTIONS        |
+--------+------------+-----------------------+
| InnoDB | Compressed | row_format=COMPRESSED |
+--------+------------+-----------------------+

New file size:
-rw-rw---- 1 mysql mysql  2738880512 2010-06-08 16:56 dvo_ro_dups.ibd

This is kind of painful when defragmenting large tables on production systems, I don't see a way to not have to recreate the table twice without being messy.
[30 Jun 2010 6:15] Marko Mäkelä
This has a common underlying cause with Bug #54679.