Bug #63692 Unable to insert certain big ascii characters under utf8 encoding
Submitted: 9 Dec 2011 7:38 Modified: 9 Dec 2011 8:08
Reporter: Zhibin Liu Email Updates:
Status: Unsupported Impact on me:
Category:MySQL Server: General Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[9 Dec 2011 7:38] Zhibin Liu
I'm porting data from oracle to mysql. This string is stored as varchar in both. I find I can't insert two characters by any means, 'load data infile' or 'insert' or anything. Anybody could be kind to give me a hand?

I dumped the hex in both DBs, and the hex string in oracle is longer than that in mysql, 'F0919790F097A284', which is at the end of the string and I can't port.

What's more, if I don't indicate utf8 while connecting to mysql(so it is latin1), the data could be inserted, but I can't use it because my program should read it as utf8, in which it is mess.

I'll try to attach some snapshot later. The two chars are at the end of the 'name' field.

How to repeat:
mysql> show create database testing;
| Database | Create Database                                                    |
| testing  | CREATE DATABASE `testing` /*!40100 DEFAULT CHARACTER SET latin1 */ |

-------------I used two databases, whether latin1 or utf8 doesn't impact the result.

 show create table test;
| Table | Create Table                                                                                                                                                                                    |
| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  `hello` varbinary(4000) DEFAULT NULL,
  PRIMARY KEY (`sid`)

load data in mysql:
load data infile '/tmp/id_13879610.txt' into table test fields terminated by ',' optionally enclosed by '"' (id,name,hello);

I also tried to edit one shell script as below:


mysql -h121.14.36.21 -P6210 -uback_cen_slave -p'sdm4.vT3c' testing -e "set names utf8; insert into test (sid, name, hello) values (...);"
[9 Dec 2011 7:43] Zhibin Liu
The last two white squares with red underline can't be ported.

Attachment: snapshot.jpg (image/jpeg, text), 12.35 KiB.

[9 Dec 2011 7:44] Zhibin Liu
Data content to be ported.

Attachment: id_13879610.txt (text/plain), 135 bytes.

[9 Dec 2011 8:08] Zhibin Liu
useless input