| 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!
