MySQL Bugs: #21355: Changing DEFAULT CHARACTER SET changes CHAR field size

Bug #21355	Changing DEFAULT CHARACTER SET changes CHAR field size
Submitted:	31 Jul 2006 5:27	Modified:	5 Nov 2006 13:21
Reporter:	Peter Lieverdink	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	4.1.11a-4sarge5	OS:	Linux (Debian stable ("Sarge"))
Assigned to:		CPU Architecture:	Any

Description:
After running into a few problems with collation in a few queries between two databases, and adding init-connect = "SET NAMES utf8" to the /etc/mysql/my.cnf file (this fixed the issue on another database) I got to the point where I decided to simply change the charset and collation on the affected databases, tables and fields.

I ran:
ALTER DATABASE `foo` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

and then for each table in the `foo` database:
ALTER TABLE `bar` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

This apparently resulted in CHAR fields being shortened, according to phpmyadmin (2.8.0.4). Fields that were CHAR(64) are now CHAR(43). CHAR(32) changed to CHAR(22) and CHAR(16) to CHAR(9).

No data loss seems to have occurred; a CHAR(16) that got changed to a CHAR(9) still contains strings consisting of 16 characters.

How to repeat:
Running:

ALTER DATABASE `foo` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER TABLE `bar` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

on a db/table containing latin1 CHAR fields.

Thank you for a problem report. Sorry, but I was not able to repeat the behavior described with 4.1.22-BK on Linux:

openxs@suse:~/dbs/4.1> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.22

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table tc1(c1 char(16));
Query OK, 0 rows affected (0.02 sec)

mysql> show create table tc1;
+-------+-----------------------------------------------------------------------
-------------------+
| Table | Create Table
                   |
+-------+-----------------------------------------------------------------------
-------------------+
| tc1   | CREATE TABLE `tc1` (
  `c1` char(16) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
-------------------+
1 row in set (0.00 sec)

mysql> insert into tc1 values('abc');
Query OK, 1 row affected (0.01 sec)

mysql> alter table tc1 default charset utf8 COLLATE utf8_unicode_ci;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show create table tc1;
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
| Table | Create Table
                                                              |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
| tc1   | CREATE TABLE `tc1` (
  `c1` char(16) character set latin1 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------+-----------------------------------------------------------------------
--------------------------------------------------------------+
1 row in set (0.00 sec)

So, please, check with a newer version, 4.1.21, and inform about the results.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I also ran into this issue, though not by altering a table.  This happens when changing the default character set in my.cnf from Latin1 to UTF-8.  Any tables which were previously using the old default character set have their char and varchar columns shortened to about 1/3 of the original size, for example varchar(32) changing to varchar(10).

This caused me a problem using Typo3 on Windows 2003 server/Apache 2
as it truncated the md5 hash keys. Once I updated the be_users table to reset the password field length, everything worked OK

Nick,

If you just change the default character set from Latin1 to UTF-8, and do not re-create the table created with Latin1, it is the expected result. See http://bugs.mysql.com/bug.php?id=21208 as yet another example of this behaviour.

All reporters: I am still waiting for any test case with ALTER TABLE that demonstrates the behaviour described here in 4.1.21.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".