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:
None 
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
Description:
Connection id:		6
Current database:	test
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:	utf8
Conn.  characterset:	utf8
UNIX socket:		/usr/local/mysql/mysql.sock
Uptime:			29 min 0 sec

Threads: 2  Questions: 37  Slow queries: 4  Opens: 36  Flush tables: 1  Open tables: 29  Queries per second avg: 0.21
--------------
when i insert  values into a table,
if the insert values is english everything is ok,but when insert chinese will occur a error,like this:
ERROR 1366 (HY000): Incorrect string value: '\xBF\xA8\xC9\xAF' for column '

How to repeat:
set status like my variables;
like this:
create table pet(
   id int(11) not null,
   name varchar(25) ,
   address varchar(100)
)engine=innodb;

insert into pet values(1,'中国','亚洲');
ERROR 1366 (HY000): Incorrect string value: '\xBF\xA8\xC9\xAF' for column '

the error like this ,
[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!