MySQL Bugs: #1411: ALTER TABLE problem

Bug #1411	ALTER TABLE problem
Submitted:	26 Sep 2003 12:52	Modified:	26 Sep 2003 16:54
Reporter:	Seokhee Kim	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.0.14	OS:	Linux (Linux 2.4)
Assigned to:		CPU Architecture:	Any

Description:
ALTER TABLE is not working for changing varchar type to char type.
char to varchar is fine.

How to repeat:
create table sample ( id int, field1 varchar(100) );
alter table sample modify field1 char(100);

I cannot duplicate this on 4.0.12/Unixware or 4.0.13/Windows.

Are you trying to update a single VARCHAR column to CHAR on a table that has other dynamic length fields (such as other VARCHAR columns, TEXT columns)? If you have a table with two VARCHAR columns for example, you cannot update one of them to CHAR and leave the other as VARCHAR. If your table has any dynamic length columns, all CHAR columns are silently converted to VARCHAR. You would need to run an ALTER TABLE statement that converts all VARCHAR columns to CHAR as a unit, and not one at a time.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

As manual describes when MyISAM table has dynamic row format all CHAR columns longer than 3 bytes are silently converted to VARCHAR

In order to change dynamic row format to Fixed-length ALTER TABLE statement must change all VARCHAR columns to CHAR at once making usre also there are no TEXT or BLOB columns.

"How to repeat" section is wrong as it will always work in expected way.

# Create Dynamic row format table
create table sample ( id int, field1 varchar(100) ); 

# Alter table to Fixed-length row format
alter table sample modify field1 char(100);

show create table sample;
CREATE TABLE `sample` (
  `id` int(11) default NULL,
  `field1` char(100) default NULL
) TYPE=MyISAM