Bug #23404 ROW_FORMAT=FIXED option is lost is an index is added to the table
Submitted: 18 Oct 2006 2:06 Modified: 24 Jan 2007 20:50
Reporter: Lachlan Mulcahy Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.24a OS:Any (Any)
Assigned to: Sergey Vojtovich
Tags: bfsm_2006_11_02

[18 Oct 2006 2:06] Lachlan Mulcahy
Description:
A table created with ROW_FORMAT=FIXED will lose the option if an index is added to the table with CREATE INDEX -- it is not lost with ALTER TABLE ADD INDEX...

How to repeat:
mysql> create table t1 (a char(20)) ROW_FORMAT=FIXED;
Query OK, 0 rows affected (0.01 sec)

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` char(20) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=FIXED
1 row in set (0.00 sec)

mysql> create index i1 on t1 (a);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `a` char(20) default NULL,
  KEY `i1` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Retain the option when CREATE INDEX command is used.
[7 Dec 2006 14:32] 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/16595

ChangeSet@1.2563, 2006-12-07 18:32:40+04:00, svoj@mysql.com +3 -0
  BUG#23404 - ROW_FORMAT=FIXED option is lost is an index is added to the
              table
  
  ROW_FORMAT option is lost during CREATE/DROP INDEX.
  
  This fix forces CREATE/DROP INDEX to retain ROW_FORMAT by instructing
  mysql_alter_table() that ROW_FORMAT is not used during creating/dropping
  indexes.
[11 Dec 2006 12:11] Alexander Barkov
The patch "bk commit into 4.1 tree (svoj:1.2563) BUG#23404" is ok to push.
[27 Dec 2006 0:12] Antony Curtis
Pushed to 5.1.15-beta repository
[27 Dec 2006 2:24] Antony Curtis
Pushed to 5.0.34 repository
[27 Dec 2006 9:59] Antony Curtis
Pushed to 4.1.23 repository
[24 Jan 2007 20:50] Paul Dubois
Noted in 4.1.23, 5.0.34, 5.1.15 changelogs.

A table created with the ROW_FORMAT=FIXED table option
loses the option if an index is added or dropped with
CREATE INDEX or DROP INDEX.