Bug #20564 | invalid character translation | ||
---|---|---|---|
Submitted: | 20 Jun 2006 9:39 | Modified: | 20 Jun 2006 11:47 |
Reporter: | [ name withheld ] | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 5.0.22 | OS: | Windows (Windows 98) |
Assigned to: | CPU Architecture: | Any |
[20 Jun 2006 9:39]
[ name withheld ]
[20 Jun 2006 10:37]
Valeriy Kravchuk
Thank you for a problem report. Please, send the results of the SHOW VARIABLES like 'char%'; from the session were you created the table. I've got different results with your test: mysql> CREATE TABLE t (n1 integer, c1 VARCHAR(20) character set latin1 collate -> latin1_swedish_ci, c2 VARCHAR(20) CHARACTER SET utf8); Query OK, 0 rows affected (0.09 sec) mysql> insert into t values(1,'a','a'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(2,'á','á'); ERROR 1406 (22001): Data too long for column 'c2' at row 1 mysql> insert into t values(3,'ä','ä'); ERROR 1406 (22001): Data too long for column 'c2' at row 1 mysql> insert into t values(4,'d','d'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(5,'ď','ď'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(6,'i','i'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(7,'í','í'); ERROR 1406 (22001): Data too long for column 'c2' at row 1 mysql> show variables like 'char%'; +--------------------------+---------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ | +--------------------------+---------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> select * from t where c1='i'; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 6 | i | i | +------+------+------+ 1 row in set (0.00 sec) mysql> select * from t where c2='i'; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 6 | i | i | +------+------+------+ 1 row in set (0.00 sec) mysql> select * from t where c2='d'; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 4 | d | d | | 5 | d | d | +------+------+------+ 2 rows in set (0.01 sec) Hence the question above...
[20 Jun 2006 11:06]
[ name withheld ]
+--------------------------+-----------------------------------------------------+ | Variable_name | Value | +--------------------------+-----------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\Apache Group\MySQL\share\charsets\ | +--------------------------+-----------------------------------------------------+ it is default instalation. Why do you get error in this line ?: insert into t values(7,'í','í'); ERROR 1406 (22001): Data too long for column 'c2' at row 1
[20 Jun 2006 11:23]
Valeriy Kravchuk
Sorry, but I just can not get that "d with hiccup" in c2 column. Look: mysql> show variables like 'char%'; +--------------------------+---------------------------------------------------------+ | Variable_name | Value | +--------------------------+---------------------------------------------------------+ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ | +--------------------------+---------------------------------------------------------+ 8 rows in set (0.00 sec) mysql> CREATE TABLE t (n1 integer, c1 VARCHAR(20) character set latin1 collate -> latin1_swedish_ci, c2 VARCHAR(20) CHARACTER SET utf8); Query OK, 0 rows affected (0.08 sec) mysql> insert into t values(1,'a','a'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(2,'á','á'); Query OK, 1 row affected (0.05 sec) mysql> insert into t values(3,'ä','ä'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(4,'d','d'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(5,'ď','ď'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(6,'i','i'); Query OK, 1 row affected (0.03 sec) mysql> insert into t values(7,'í','í'); Query OK, 1 row affected (0.02 sec) mysql> select * from t where c1='i'; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 6 | i | i | | 7 | í | í | +------+------+------+ 2 rows in set (0.00 sec) mysql> select * from t where c2='i'; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 6 | i | i | | 7 | í | í | +------+------+------+ 2 rows in set (0.00 sec) mysql> select * from t where c2='d'; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 4 | d | d | | 5 | d | d | +------+------+------+ 2 rows in set (0.00 sec) mysql> select * from t; +------+------+------+ | n1 | c1 | c2 | +------+------+------+ | 1 | a | a | | 2 | á | á | | 3 | ä | ä | | 4 | d | d | | 5 | d | d | | 6 | i | i | | 7 | í | í | +------+------+------+ 7 rows in set (0.00 sec) mysql> select version(); +---------------------+ | version() | +---------------------+ | 5.0.22-community-nt | +---------------------+ 1 row in set (0.00 sec) So, for me it is converted to d upon insertion...
[20 Jun 2006 11:45]
[ name withheld ]
when I type : select *, ascii(c1) from t I get : +--------+--------+--------+-----------+ | n1 | c1 | c2 | ascii(c1) | +--------+--------+--------+-----------+ | 1 | a | a | 97 | | 2 |
[20 Jun 2006 11:47]
[ name withheld ]
when I type : select *, ascii(c1) from t I get : +--------+--------+--------+-----------+ | n1 | c1 | c2 | ascii(c1) | +--------+--------+--------+-----------+ | 1 | a | a | 97 | | 2 | | | 225 | | 3 | | | 228 | | 4 | d | d | 100 | | 5 | | | 239 | | 6 | i | i | 105 | | 7 | | | 237 | | 8 | | | 158 | | 9 | z | z | 122 | +--------+--------+--------+-----------+ So "d caron" in Windows1250 (my windows codepage) is ascii code is 239 (row 5) - my insert statement was written under this codepage. But in latin1 ascii code 239 is "Small i, dieresis or umlaut mark", so mysql interprets is as "i" not as "d" ... So it seems like my mystake :-) I must alter code page for table and for connection ...