MySQL Bugs: #6913: update database from 3.23 to 4.1 will cause char(1) become char(0)

Bug #6913	update database from 3.23 to 4.1 will cause char(1) become char(0)
Submitted:	1 Dec 2004 10:23	Modified:	20 Jan 2005 19:01
Reporter:	John Tam	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	4.1.7-standard	OS:	Linux (Fedora Core release 2 (Tettnang))
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
when upgrade MySQL from mysql-3.23.58-9 to 4.1.7-standard, the column's attrib of original table change.

gender char(1) not null default 'M'  =>  gender char(0)

How to repeat:
First, we have a new install FC2 on machine that will use mysql-3.23.58-9,
here is the create sentence:
create database testme;
create table member (mid int not null auto_increment, name varchar(100) , gender char(1) not null default 'M',primary key(mid)) TYPE=MyISAM;

then upgrade mysql to 4.0, the rpm are as list:
MySQL-bench-4.0.22-0.i386.rpm
MySQL-client-4.0.22-0.i386.rpm
MySQL-devel-4.0.22-0.i386.rpm
MySQL-server-4.0.22-0.i386.rpm
MySQL-shared-4.0.22-0.i386.rpm
MySQL-shared-compat-4.0.22-0.i386.rpm

then upgrade mysql 4.0 to 4.1, the rpm are as list:
MySQL-bench-4.1.7-0.i386.rpm
MySQL-client-4.1.7-0.i386.rpm
MySQL-devel-4.1.7-0.i386.rpm
MySQL-server-4.1.7-0.i386.rpm
MySQL-shared-4.1.7-0.i386.rpm
MySQL-shared-compat-4.1.7-0.i386.rpm

then run /usr/bin/mysql_fix_privilege_tables, then run mysql
use testme;
explain member;
you will see the column gender become char(0) and lost it's value

the varchar(255) will become varchar(127), is this problem cause from the language setting? My native language is zh_TW.Big5, so after upgrade , I try to dump the schema and there is the schema below:

use testme;

DROP TABLE IF EXISTS `member`;

CREATE TABLE `member` (
`mid` int(11) NOT NULL auto_increment,
`name` varchar(50) default NULL,
`gender` char(0) NOT NULL default 'M',
PRIMARY KEY  (`mid`)
) ENGINE=MyISAM DEFAULT CHARSET=big5;

You are right, it's because of charset=big5.

You had column length 255 bytes in 3.23, so 4.1 displays it at 127 characters (assuming 2 bytes per char).

As for char(1) - displaying it as char(1) would mean increasing column's length to two bytes, displaying it as char(0) would mean losing data.

Unfortunately there is no safe solution that would cover all cases :(
(at least I don't know one)

As Sergei notes, this is expected behavior and not much can be done about it, but the proposed patch at least prints a warning to the log when this sort of column conversion will happen.

The 'upgrading to 4.1' section of the documentation should probably also contain a special warning about changing the default-character-set of the server to a multibyte character set. (If it does not already -- I haven't checked.)

Warning added to the 4.1 tree.

Mentioned in 4.1.10 change notes, along with pointer to
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
where the apparent-change-of-length problem is discussed.