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:
Category:MySQL Server Severity:S1 (Critical)
Version:4.0.14 OS:Linux (Linux 2.4)
Assigned to: CPU Architecture:Any

[26 Sep 2003 12:52] Seokhee Kim
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);
[26 Sep 2003 13:09] Mike Hillyer
I cannot duplicate this on 4.0.12/Unixware or 4.0.13/Windows.
[26 Sep 2003 14:03] Mike Hillyer
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.
[26 Sep 2003 16:54] Alexander Keremidarski
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