Bug #54679 alter table causes compressed row_format to revert to compact

• Submitted: 21 Jun 2010 21:38
• Modified: 10 Dec 2010 20:40
• Reporter: Rob Finch
• Status: Closed
• Category: MySQL Server: InnoDB Plugin storage engine
• Severity: S3 (Non-critical)
• Version: 5.1.48, 5.5.5-m3
• OS: Any
• Assigned to: Marko Mäkelä
• CPU Architecture: Any

[21 Jun 2010 21:38] Rob Finch

Description:
On tables created with row_format=compressed, doing an alter causes the table to revert to compact unless you implicitly add row_format=compressed at the end of your alter.

I would expect the existing row_format to be preserved across alters.

Possibly related to http://bugs.mysql.com/bug.php?id=39200 - also affects 5.1.46 (and likely other versions)

How to repeat:
Easy to repro, create a table with ENGINE=InnoDB & row_format=compressed then add a column - then check the information_schema.tables info about the row format.

Server version: 5.1.48-log MySQL Community Server (GPL)

CREATE DATABASE robtest;
use robtest;
CREATE TABLE robtest (
         test1 VARCHAR(100)
         ) ENGINE=InnoDB row_format=compressed;

Validate creation:

mysql> SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME='robtest';

| robtest    | Compressed | row_format=COMPRESSED |

Add some random data (not necessary)
mysql> INSERT INTO robtest (test1) VALUES ('sfdsdfdsdfs'); 

mysql> ALTER TABLE robtest ADD COLUMN test2 VARCHAR(100);

Validate the row_format revert occurred;

mysql> SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME='robtest';

| robtest    | Compact    | row_format=COMPRESSED |

Filesize with compact:

-rw-rw---- 1 mysql mysql 98304 2010-06-21 17:17 robtest.ibd

Alter back to compressed via alter table robtest ENGINE=InnoDB row_format=compressed 

Filesize after returning to compressed;

-rw-rw---- 1 mysql mysql 65536 2010-06-21 17:19 robtest.ibd

[22 Jun 2010 4:50] Valeriy Kravchuk

Verified as described, also with current mysql-trunk on Mac OS X:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE robtest (          test1 VARCHAR(100)          ) ENGINE=InnoDB row_format=compressed;
Query OK, 0 rows affected (0.72 sec)

mysql> SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME='robtest';
+------------+------------+-----------------------+
| TABLE_NAME | ROW_FORMAT | CREATE_OPTIONS        |
+------------+------------+-----------------------+
| robtest    | Compressed | row_format=COMPRESSED |
+------------+------------+-----------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO robtest (test1) VALUES ('sfdsdfdsdfs'); Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE robtest ADD COLUMN test2 VARCHAR(100);Query OK, 1 row affected (0.44 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT TABLE_NAME,ROW_FORMAT,CREATE_OPTIONS FROM information_schema.tables WHERE TABLE_NAME='robtest';
+------------+------------+-----------------------+
| TABLE_NAME | ROW_FORMAT | CREATE_OPTIONS        |
+------------+------------+-----------------------+
| robtest    | Compact    | row_format=COMPRESSED |
+------------+------------+-----------------------+
1 row in set (0.00 sec)

[24 Jun 2010 9:40] 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/112041

[24 Jun 2010 9:40] 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/112042

[24 Jun 2010 10:09] Jimmy Yang

Ok to push

[24 Jun 2010 10:47] 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/112048

[24 Jun 2010 10:49] 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/112050

[24 Jun 2010 10:50] 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/112051

[24 Jun 2010 10:52] 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/112056

[24 Jun 2010 11:07] 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/112064

[24 Jun 2010 11:07] 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/112065

[30 Jun 2010 6:15] Marko Mäkelä

Bug #39200 is a duplicate of this one.

[19 Jul 2010 14:36] Bugs System

Pushed into 5.1.49 (revid:[e-mail]-20100719143034-omcma40sblwmay3x) (version source revid:[e-mail]-20100704071244-3lo4okzels3kvy1p) (merge vers: 5.1.49) (pib:16)

[20 Jul 2010 23:07] John Russell

Added to 5.1.49 change log:

An ALTER TABLE statement could convert a compressed table (with
row_format=compressed) back to an uncompressed table (with
row_format=compact).

[23 Jul 2010 12:27] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100723121820-jryu2fuw3pc53q9w) (version source revid:[e-mail]-20100723121820-jryu2fuw3pc53q9w) (merge vers: 5.5.6-m3) (pib:18)

[23 Jul 2010 12:34] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100723121929-90e9zemk3jkr2ocy) (version source revid:[e-mail]-20100723121827-3bsh51m5sj6g4oma) (pib:18)

[4 Aug 2010 7:52] Bugs System

Pushed into mysql-trunk 5.5.6-m3 (revid:[e-mail]-20100731131027-1n61gseejyxsqk5d) (version source revid:[e-mail]-20100629125518-m3am4ia1ffjr0d0j) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:08] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:[e-mail]-20100629125518-m3am4ia1ffjr0d0j) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 8:24] Bugs System

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:[e-mail]-20100629125518-m3am4ia1ffjr0d0j) (merge vers: 5.1.49) (pib:18)

[4 Aug 2010 9:03] Bugs System

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:[e-mail]-20100629125518-m3am4ia1ffjr0d0j) (pib:20)

[4 Aug 2010 22:07] John Russell

Added to 5.5.6 change log.

[1 Sep 2010 18:59] Mattias Jonsson

bug#50300 is a duplicate of this.

[14 Oct 2010 8:34] Bugs System

Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (version source revid:[e-mail]-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)

[14 Oct 2010 8:50] Bugs System

Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:[e-mail]-20101014083757-5qo48b86d69zjvzj) (version source revid:[e-mail]-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)

[14 Oct 2010 9:04] Bugs System

Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:[e-mail]-20101014084420-y54ecj85j5we27oa) (version source revid:[e-mail]-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)

[15 Oct 2010 13:25] Jon Stephens

Already documented in the 5.1.49 changelog. Reverting to Closed state.

[5 Dec 2010 12:43] Bugs System

Pushed into mysql-trunk 5.6.1 (revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (version source revid:[e-mail]-20101205122447-6x94l4fmslpbttxj) (merge vers: 5.6.1) (pib:23)

[15 Dec 2010 5:51] Bugs System

Pushed into mysql-5.1 5.1.55 (revid:[e-mail]-20101215054055-vgwki317xg1wphhh) (version source revid:[e-mail]-20101215054055-vgwki317xg1wphhh) (merge vers: 5.1.55) (pib:23)

[16 Dec 2010 22:31] Bugs System

Pushed into mysql-5.5 5.5.9 (revid:[e-mail]-20101216101358-fyzr1epq95a3yett) (version source revid:[e-mail]-20101216101358-fyzr1epq95a3yett) (merge vers: 5.5.9) (pib:24)

[26 Dec 2010 23:27] Dave Kelly

I'm confused.  The documentation states that any options not explicitly specified in the ALTER TABLE statement will be dropped in the altered table.