Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1

From: sunpeng <bluevaley(at)gmail(dot)com>
To: PostgreSQL general <pgsql-general(at)postgresql(dot)org>
Subject: Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1
Date: 2014-07-01 12:42:07
Message-ID: CAOYKhLoXAmaSdHGZod7AaFukrNiJLG52D1J5TuFXh12zNhg04g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

When I do migration from Mysql to PostgreSQL:
firstly dump data from mysql in cmd(encoding is GBK) is WIN8:
mysqldump -v -nt --complete-insert=TRUE --compatible=postgresql
--default-character-set=utf8 --skip-add-locks --compact --no-create-info
--skip-quote-names -uroot -p test >dbdata.sql

then load data to postgresql in cmd(encoding is GBK) is WIN8:
psql -h localhost -d test -U postgres < dbdata.sql

I got the error:
ERROR: invalid byte sequence for encoding "UTF8": 0xff

I checked in dbdata.sql using UltraEdit, data "0xff" really exists as
followings:
蛾4?4抿\0xfF???±??x¤?
and I use UltraEdit menu-> view -> encoding page to get: 936 GBK.

DDL in Mysql 5.5 is:
CREATE TABLE `personpicture` (
`ID` char(32) NOT NULL,
`Picture` mediumblob,
...
KEY `personId` (`PersonID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DDL in PostgreSQL 9.1 is:
create database test encoding 'utf8';
CREATE TABLE personpicture (
ID char(32) NOT NULL,
Picture BYTEA,
....
PRIMARY KEY (ID)
);

The error is related to Picture mediumblob and BYTEA, which is stored pic
binary data.
My Operationg system is WIN8 64bit.

I've set postgresql.conf before starting everything, but error still occurs:
backslash_quote = on
escape_string_warning = off
standard_conforming_strings = off

How to resolve it?
Thanks!

peng

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Kevin Grittner 2014-07-01 13:18:28 Re: Migration error " invalid byte sequence for encoding "UTF8": 0xff " from mysql 5.5 to postgresql 9.1
Previous Message Rebecca Clarke 2014-07-01 10:25:36 Re: collecting employees who completed 5 and 10 years in the current month