Can't change column types from varchar to char?

[danomac]

I have a table which I was trying to change the columns to. For some reason, mysql will not change the column from a varchar to a char type.

Table:

TABLE test {
  fld1 varchar(20) not null unique
  fld2 varchar(30) not null default 'default'
  fld3 int(10) not null default 5
}

When I do (in mysql console or PHP):

ALTER TABLE `test` CHANGE `fld2` `fld2` CHAR(30) DEFAULT 'default' NOT NULL;

ALTER TABLE `test` CHANGE `fld1` `fld1` CHAR(20) NOT NULL

neither will change the column type to char; both columns remain as a varchar. It is doing something though, as if I change the default value in the first alter table statement, the new default gets set.

At first I thought it was because of the unique setting but it does this on both fields.

I'm using mysql 4.0.24. Does anyone know why I can't change these two column types? I have tested numeric types (i.e. bigint -> int) with alter tables and it works correctly ??

 

[TonyGroves]

MySQL will automatically convert a CHAR field to a VARCHAR if it determines that the positive effects of the saved space would outweigh the negative effects of having variable-length records. As far as I know, there is no way to overrule such a decision.

However, it won't affect your applications; CHAR and VARCHAR fields are identical as far as the user is concerned.

 

[danomac]

Thanks TonyGroves.

I spent the last couple hours or so on mysql.com reading through docs trying to figure out why the column was not changing, and I stumbled on a chapter "Silent changes to column types" (or similar to that.) It explains that information there as well.

I found a way to override it in this case though (see below.) I probably won't bother converting these columns anymore now that I know mysql will figure it out on its own.

Just to sum up so others reading this thread know what's going on:

If you have any variable length columns mysql will assume the entire definition will be variable. All char become varchar. This is especially true when trying to alter existing tables.

I did some experimenting with mysql console and found that while my first attempt does not work, this one does:

ALTER TABLE `test` DROP `fld2`;

ALTER TABLE `test` CHANGE `fld1` `fld1` CHAR(20) NOT NULL; 

ALTER TABLE `test` ADD `fld2` CHAR(30) DEFAULT 'default' NOT NULL;

At first I thought my scripts were misbehaving though.