Bug #59735 | can't insert into value | ||
---|---|---|---|
Submitted: | 26 Jan 2011 9:19 | Modified: | 2 Apr 2011 1:35 |
Reporter: | LI XU | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Charsets | Severity: | S3 (Non-critical) |
Version: | 5.5.8 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | ERROR 1366 (HY000): Incorrect string value: '\xBF\xA8\xC9\xAF' for column |
[26 Jan 2011 9:19]
LI XU
[26 Jan 2011 9:27]
Valeriy Kravchuk
Please, send the output of: echo $LANG from the environment you started mysql command line client from.
[27 Jan 2011 2:56]
LI XU
yeah,following your help: echo $LANG [root@MySQLServer ~]# echo $LANG en_US like this, mysql> set names latin1; Query OK, 0 rows affected (0.01 sec) mysql Ver 14.14 Distrib 5.5.8, for linux2.6 (i686) using readline 5.1 Connection id: 8 Current database: world Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.8-log MySQL Community Server (GPL) Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8 Db characterset: utf8 Client characterset: latin1 Conn. characterset: latin1 UNIX socket: /usr/local/mysql/mysql.sock Uptime: 1 hour 45 min 37 sec Threads: 2 Questions: 54 Slow queries: 5 Opens: 39 Flush tables: 1 Open tables: 29 Queries per second avg: 0.8 -------------- mysql> show tables; +-----------------+ | Tables_in_world | +-----------------+ | city | | country | | countrylanguage | | tmp | | zone | +-----------------+ 5 rows in set (0.02 sec) mysql> desc zone; +--------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+-------+ | ID | int(10) unsigned | YES | | NULL | | | z_name | varchar(25) | YES | | NULL | | +--------+------------------+------+-----+---------+-------+ 2 rows in set (0.01 sec) mysql> select * from zone; +------+----------+ | ID | z_name | +------+----------+ | 1 | alaska | | 12 | 社区音乐 | +------+----------+ 2 rows in set (0.01 sec) mysql> insert into zone values(3,'中国'); Query OK, 1 row affected (0.02 sec) mysql> select * from zone; +------+----------+ | ID | z_name | +------+----------+ | 1 | alaska | | 12 | 社区音乐 | | 3 | 中国 | +------+----------+ 3 rows in set (0.00 sec) when i set the charset latin1,i can insert chinese well. i don't think have any contact with system lang?
[28 Jan 2011 14:53]
Valeriy Kravchuk
Verified with current mysql-5.5 on Mac OS X: macbook-pro:5.5 openxs$ 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 7 Server version: 5.5.10-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> status -------------- bin/mysql Ver 14.14 Distrib 5.5.10, for osx10.5 (i386) using readline 5.1 Connection id: 7 Current database: test Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.5.10-debug Source distribution Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 UNIX socket: /tmp/mysql.sock Uptime: 19 hours 54 min 58 sec Threads: 1 Questions: 142 Slow queries: 0 Opens: 47 Flush tables: 1 Open tables: 40 Queries per second avg: 0.1 -------------- mysql> create table pet( -> id int(11) not null, -> name varchar(25) , -> address varchar(100) -> )engine=innodb; Query OK, 0 rows affected (0.06 sec) mysql> insert into pet values(1,'中国','亚洲'); Query OK, 1 row affected, 2 warnings (0.00 sec) mysql> show warnings\G *************************** 1. row *************************** Level: Warning Code: 1366 Message: Incorrect string value: '\xE4\xB8\xAD\xE5\x9B\xBD' for column 'name' at row 1 *************************** 2. row *************************** Level: Warning Code: 1366 Message: Incorrect string value: '\xE4\xBA\x9A\xE6\xB4\xB2' for column 'address' at row 1 2 rows in set (0.00 sec) mysql> select * from pet; +----+------+---------+ | id | name | address | +----+------+---------+ | 1 | ?? | ?? | +----+------+---------+ 1 row in set (0.00 sec) mysql> delete from pet; Query OK, 1 row affected (0.00 sec) mysql> set names latin1; Query OK, 0 rows affected (0.00 sec) mysql> insert into pet values(1,'中国','亚洲'); Query OK, 1 row affected (0.00 sec) mysql> select * from pet; +----+--------+---------+ | id | name | address | +----+--------+---------+ | 1 | 中国 | 亚洲 | +----+--------+---------+ 1 row in set (0.00 sec) mysql> exit Bye macbook-pro:5.5 openxs$ echo $LANG uk_UA.UTF-8
[28 Jan 2011 14:59]
Valeriy Kravchuk
Actually, it may be not a bug even. As soon as I use utf8 database (and table), everything works: macbook-pro:5.5 openxs$ 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 8 Server version: 5.5.10-debug Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database u charset utf8; Query OK, 1 row affected (0.00 sec) mysql> use u; Database changed mysql> create table pet( id int(11) not null, name varchar(25) , address varchar(100) )engine=innodb; Query OK, 0 rows affected (0.07 sec) mysql> insert into pet values(1,'中国','亚洲'); Query OK, 1 row affected (0.00 sec) mysql> select * from pet; +----+--------+---------+ | id | name | address | +----+--------+---------+ | 1 | 中国 | 亚洲 | +----+--------+---------+ 1 row in set (0.00 sec) mysql> show create table pet\G *************************** 1. row *************************** Table: pet Create Table: CREATE TABLE `pet` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `address` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Compare to the table from the previous test: mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> show create table pet\G *************************** 1. row *************************** Table: pet Create Table: CREATE TABLE `pet` ( `id` int(11) NOT NULL, `name` varchar(25) DEFAULT NULL, `address` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)
[1 Mar 2011 0:00]
Bugs System
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".
[1 Mar 2011 1:59]
LI XU
at frist, thank you ,Valeriy Kravchuk ! but i think you shoud test it with mysql 5.5.8 log-coummity version. [root@MySQLServer ~]# echo $LANG en_US
[1 Mar 2011 4:20]
Valeriy Kravchuk
Even if there was a bug in 5.5.8, we do not fix bugs in older versions. So, please, check with 5.5.9 or wait for a few days for the official release and check with 5.5.10.
[1 Apr 2011 23:00]
Bugs System
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".
[2 Apr 2011 1:35]
LI XU
i will use the lastest release version!