Bug #67727 How do I remove table options
Submitted: 27 Nov 2012 15:11 Modified: 28 Dec 2012 12:54
Reporter: Mark Callaghan Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.5 OS:Any
Assigned to: CPU Architecture:Any

[27 Nov 2012 15:11] Mark Callaghan
Description:
How do I remove create options from a table? Below is one example where I convert a compressed InnoDB table to MyISAM and row_format=compressed is bogus for MyISAM but remains. Other examples occur without going from InnoDB to MyISAM. Just change from compressed to uncompressed InnoDB or even compress -> compressed InnoDB.

How to repeat:

create table ti(i int primary key) engine=innodb row_format=compressed;
alter table ti engine=myisam;
show create table ti;

mysql> show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
[27 Nov 2012 16:28] MySQL Verification Team
Looks like a work-around is to use row_format= default:

d:\dbs>d:\dbs\5.5\bin\mysql -uroot --port=3541 --prompt="mysql 5.5 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.5.30 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql 5.5 >use test
Database changed
mysql 5.5 >create table ti(i int primary key) engine=innodb row_format=compressed;
Query OK, 0 rows affected (0.03 sec)

mysql 5.5 >alter table ti engine=myisam;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.04 sec)

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

mysql 5.5 >create table ti(i int primary key) engine=innodb row_format=compressed;
Query OK, 0 rows affected (0.05 sec)

mysql 5.5 >show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)

mysql 5.5 >alter table ti engine=myisam row_format=default;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.5 >show create table ti\G
*************************** 1. row ***************************
       Table: ti
Create Table: CREATE TABLE `ti` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql 5.5 >
[28 Dec 2012 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[28 Dec 2012 1:11] Mark Callaghan
Is this considered fixed?
[28 Dec 2012 12:42] Valeriy Kravchuk
Hardly this can be considered fixed. In 5.5.29 we still have the same results:

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.29    |
+-----------+
1 row in set (0.16 sec)

mysql> create table ti(i int primary key) engine=innodb row_format=compressed;
Query OK, 0 rows affected, 2 warnings (1.50 sec)

mysql> alter table ti engine=myisam;
Query OK, 0 rows affected (0.84 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ti;
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| Table | Create Table
                                                     |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
| ti    | CREATE TABLE `ti` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED |
+-------+-----------------------------------------------------------------------
-----------------------------------------------------+
1 row in set (0.00 sec)

Yes, workaround works:

mysql> alter table ti row_format=default;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table ti;
+-------+-----------------------------------------------------------------------
-------------------------------+
| Table | Create Table
                               |
+-------+-----------------------------------------------------------------------
-------------------------------+
| ti    | CREATE TABLE `ti` (
  `i` int(11) NOT NULL,
  PRIMARY KEY (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)

But why option that does not apply any more is preserved when engine is changed?