MySQL Bugs: #39200: optimize table does not recognize ROW

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

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

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

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.

## 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;

...
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.

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.

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!

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).

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).

queued for 5.1.36, 6.0.12 in bugteam

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)

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)

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.

Noted in 5.4.2 changelog because next 5.4 version will be 5.4.2 and not 5.4.4.

Ignore previous comment about 5.4.2.

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)

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)

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)

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)

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)

The 5.4 fix has been pushed to 5.4.2.

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)

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)

Noted in 5.5.0, 6.0.14 changelogs.

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).

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)

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)

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)

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)

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.

Bug #50733 was marked as a duplicate of this one.

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 :-(

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.

This has a common underlying cause with Bug #54679.