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