Bug #11591 CHAR column with utf8 does not work properly (more chars than expected)
Submitted: 27 Jun 2005 13:10 Modified: 14 Jul 2005 16:01
Reporter: Andrey Hristov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.9-dev OS:
Assigned to: Alexander Barkov

[27 Jun 2005 13:10] Andrey Hristov
Description:
A column which is CHAR(2) can contain more characters than 2. See the how to repeat instructions. Firstly the char column is char(20) - enough to hold "andrey" (in cyrillic). Then the column is altered to be CHAR(2) which produces truncation warnings as it should be but at the end there are 3 chars in the column. SHOW CREATE TABLE still says that the column is CHAR(2). If the column is made larger - again CHAR(20) an ASCII string is inserted "123456" and then ALTER to CHAR(2) there is _no_ truncation warning and the the second value in the column is stil "123456"

How to repeat:
mysql> create table t567(c char(20) character set utf8);
Query OK, 0 rows affected (0.05 sec)

mysql> set names utf8;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t567 values('андрей');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t567;
+--------------+
| c            |
+--------------+
| андрей |
+--------------+
1 row in set (0.00 sec)

mysql> alter table t567 modify c char(2) character set utf8;
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'c' at row 1 |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

mysql> select * from t567;
+--------+
| c      |
+--------+
| анд |
+--------+
1 row in set (0.00 sec)

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

mysql> select char_length(c) from t567;
+----------------+
| char_length(c) |
+----------------+
|              3 |
+----------------+
1 row in set (0.01 sec)

mysql> select length(c) from t567;
+-----------+
| length(c) |
+-----------+
|         6 |
+-----------+
1 row in set (0.01 sec)

mysql> alter table t567 modify c char(20) character set utf8;
Query OK, 1 row affected (0.17 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> insert into t567 values("123456");
Query OK, 1 row affected (0.06 sec)

mysql> alter table t567 modify a char(2) character set utf8;
Query OK, 2 rows affected (0.14 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select a, char_length(a), length(a) from t567;
+--------+----------------+-----------+
| a      | char_length(a) | length(a) |
+--------+----------------+-----------+
| анд |              3 |         6 |
| 123456 |              6 |         6 |
+--------+----------------+-----------+
2 rows in set (0.07 sec)
[11 Jul 2005 8:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26866
[12 Jul 2005 14:30] Alexander Barkov
Reviewed by Gluh.
[14 Jul 2005 16:01] Paul Dubois
Noted in 4.1.13 changelog.