| 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: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 5.5 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[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?
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