Description:
on RedHat Enterprise Linux 3, upgrading from mysql-3 to MySQL-4.1.12
changes the encoding of latin-1 tables to utf-8,
cuasing text fields length to be shorter.
char(1) field will become char(0) - which will raise an error during insert or update.
How to repeat:
on RedHat Enterprise Linux 3, I had the mysql-3.23.54a-11.i386.rpm installed.
I had a few databases up and running.
to upgrade, I removed the mysql-3 rpm and installed:
MySQL-client-standard-4.1.12-0.rhel3.i386.rpm
MySQL-devel-standard-4.1.12-0.rhel3.i386.rpm
MySQL-server-standard-4.1.12-0.rhel3.i386.rpm
MySQL-shared-standard-4.1.12-0.rhel3.i386.rpm
After the installation all the latin-1 tables were converted to uft-8, cutting varchar fields length by half.
for example:
show create table phpgw_accounts;
CREATE TABLE `phpgw_accounts` (
`account_id` int(11) NOT NULL auto_increment,
`account_lid` varchar(25) NOT NULL default '',
`account_pwd` varchar(100) NOT NULL default '',
`account_firstname` varchar(50) default NULL,
`account_lastname` varchar(50) default NULL,
`account_lastlogin` int(11) default NULL,
`account_lastloginfrom` varchar(255) default NULL,
`account_lastpwd_change` int(11) default NULL,
`account_status` char(1) NOT NULL default 'A',
`account_expires` int(11) default NULL,
`account_type` char(1) default NULL,
`person_id` int(11) default NULL,
`account_primary_group` int(11) NOT NULL default '0',
`account_email` varchar(100) default NULL,
PRIMARY KEY (`account_id`),
UNIQUE KEY `account_lid` (`account_lid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
became:
CREATE TABLE `phpgw_accounts` (
`account_id` int(11) NOT NULL auto_increment,
`account_lid` varchar(8) collate utf8_bin NOT NULL default '',
`account_pwd` varchar(33) collate utf8_bin NOT NULL default '',
`account_firstname` varchar(16) collate utf8_bin default NULL,
`account_lastname` varchar(16) collate utf8_bin default NULL,
`account_lastlogin` int(11) default NULL,
`account_lastloginfrom` varchar(85) collate utf8_bin default NULL,
`account_lastpwd_change` int(11) default NULL,
`account_status` char(0) collate utf8_bin NOT NULL default 'A',
`account_expires` int(11) default NULL,
`account_type` char(0) collate utf8_bin default NULL,
`person_id` int(11) default NULL,
`account_primary_group` int(11) NOT NULL default '0',
`account_email` varchar(33) collate utf8_bin default NULL,
PRIMARY KEY (`account_id`),
UNIQUE KEY `account_lid` (`account_lid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
notice especially the char(0) field.
this field, for instance, could be selected (and the correct, old value, returned)
but raised an error when updating or inserting.
