Dear all,

I tried to migrate a UTF-8 database with Japanese content from Postgresql 
7.1.3 to Postgresql 7.2 unsucessfully.

I made a pg_dump of the 7.1.3 database and tried to upload it in 7.2.
Error message was "Invalid Unicode character sequence found".

I tried to upload data in a fresh PostgreSQL 7.1.3 database and everything 
worked OK. The problem can be reproduced on PostgreSQL 7.2.

Do not hesitate to ask me for a pg_dump of the involved tables. I cannot 
submit them on the list for privacy reasons. Thanks for your help.

Cheers,
Jean-Michel POURE

Tue, 19 Feb 2002 16:38:35 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
UTF-8 data migration problem in Postgresql 7.2
Jean-Michel POURE <jm.poure@freesurf.fr> writes:

> Dear all,
> 
> I tried to migrate a UTF-8 database with Japanese content from Postgresql 
> 7.1.3 to Postgresql 7.2 unsucessfully.
> 
> I made a pg_dump of the 7.1.3 database and tried to upload it in 7.2.
> Error message was "Invalid Unicode character sequence found".

Same here.

-- 
Trond Eivind Glomsrï¿½d
Red Hat, Inc.

19 Feb 2002 11:03:20 -0500
teg@redhat.com (Trond Eivind =?iso-8859-1?q?Glomsr=F8d?=)
Re: UTF-8 data migration problem in Postgresql 7.2
Le Mardi 19 Fï¿½vrier 2002 17:03, Trond Eivind Glomsrï¿½d a ï¿½crit :
> Same here.
I am stuck with this UTF-8 migration problem.

Is it safe to connect to 2 separate databases (Left=7.1.3, Right=7.2) and to 
migrate UTF-8 data using blind-native ADODB calls? I would say yes, but can 
some hacker confirm? Otherwize I can wait a few days, but not more.

Any help my friends?

Cheers,
Jean-Michel POURE

Tue, 19 Feb 2002 22:33:43 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [HACKERS] UTF-8 data migration problem in Postgresql 7.2
> I tried to migrate a UTF-8 database with Japanese content from Postgresql 
> 7.1.3 to Postgresql 7.2 unsucessfully.
> 
> I made a pg_dump of the 7.1.3 database and tried to upload it in 7.2.
> Error message was "Invalid Unicode character sequence found".
> 
> I tried to upload data in a fresh PostgreSQL 7.1.3 database and everything 
> worked OK. The problem can be reproduced on PostgreSQL 7.2.
> 
> Do not hesitate to ask me for a pg_dump of the involved tables. I cannot 
> submit them on the list for privacy reasons. Thanks for your help.

I have received the test data and got:

psql:dump_error.sql:40: ERROR:  copy: line 1, Invalid UNICODE character sequence found (0xe38227)

The error messages shows your data contains an invalid UTF-8 character
sequence. The first byte (e8) expects the UTF-8 sequence consists of 3
bytes, each of 8th bit on. However the third byte is 0x27, apparently
it does not satisfy the requirement. Check your data.
--
Tatsuo Ishii

Wed, 20 Feb 2002 09:50:55 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
Le Mercredi 20 Fï¿½vrier 2002 01:50, Tatsuo Ishii a ï¿½crit :
> The error messages shows your data contains an invalid UTF-8 character
> sequence. The first byte (e8) expects the UTF-8 sequence consists of 3
> bytes, each of 8th bit on. However the third byte is 0x27, apparently
> it does not satisfy the requirement. Check your data.

Dear Tatsuo,

This data was created using pg_dump in PostgreSQL 7.1.3. The data load well 
in PostgreSQL 7.1.3, but not into PostgreSQL 7.2.

Is this a bug or is the data faulty? How can I verify UTF-8 data already 
stored into PostgreSQL 7.1.3? 

Best regards,
Jean-Michel POURE

Wed, 20 Feb 2002 08:35:29 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: UTF-8 data migration problem in Postgresql 7.2
> Le Mercredi 20 Fï¿½vrier 2002 01:50, Tatsuo Ishii a ï¿½crit :
> > The error messages shows your data contains an invalid UTF-8 character
> > sequence. The first byte (e8) expects the UTF-8 sequence consists of 3
> > bytes, each of 8th bit on. However the third byte is 0x27, apparently
> > it does not satisfy the requirement. Check your data.

Dear all,

Can anyone help me understand where the problem stems from.

1) Is this a data perversion problem (=wrong UTF-8 sequences entered)? Then, 
how can I fix it? Why did 7.1.3 allow the input of such data?

2) The data was created with pg_dump. It loads without problem in 7.1.3. But 
not  in 7.2. Why?

3) Ultimately, please tell me if I can use ADODB odbc calls to migrate UTF-8 
data using VB. Are there special settings required? Should I wait for a fix 
in 7.2 or should I go for VB right now?

I am stuck and cannot work anymore (my new code is on 7.2, the old data is on 
7.1.3). Thank you for your help,

Cheers,
Jean-Michel POURE

Wed, 20 Feb 2002 11:15:11 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: UTF-8 data migration problem in Postgresql 7.2
> 1) Is this a data perversion problem (=wrong UTF-8 sequences entered)? Then, 
> how can I fix it?

Why don't you edit the output from pg_dump? It's just a text file.

> Why did 7.1.3 allow the input of such data?

It's just becuase no data validation checking was there before 7.2.
--
Tatsuo Ishii

Wed, 20 Feb 2002 23:12:18 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
Dear Tatsuo,

Thank you for this answer.

> Why don't you edit the output from pg_dump? It's just a text file.
I edited the output in Yudit. Nothing seems to be wrong.

> >ï¿½Why did 7.1.3 allow the input of such data?
> It's just becuase no data validation checking was there before 7.2.
Data was entered by a Japanese translator using IE5.5, japanese fonts, Win2K, 
Apache and Php with UTF-8 extensions. How can it be wrong? It displays well 
under IE5.5. Are you sure it is not a PostgreSQL 7.1.3 pg_dump related bug? 
Other users have the same problem when migrating to 7.2.

Furthermore, do you think I can migrate data using ODBC adodb calls?

Thank you for your help,
Jean-Michel POURE

Wed, 20 Feb 2002 15:40:51 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: UTF-8 data migration problem in Postgresql 7.2
Jean-Michel POURE <jm.poure@freesurf.fr> writes:
> 1) Is this a data perversion problem (=wrong UTF-8 sequences entered)? Then, 
> how can I fix it? Why did 7.1.3 allow the input of such data?

7.1 did not have validity checks for UTF sequences.  7.2 does.  This
does not seem like a bad change to me.

			regards, tom lane

Wed, 20 Feb 2002 10:13:00 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2 
> > Why don't you edit the output from pg_dump? It's just a text file.
> I edited the output in Yudit. Nothing seems to be wrong.

I don't know anything about Yuedit, but I guess it skips wrong UTF-8
sequences. I see an encoding converter called "lv" does something like
that. Maybe filtering the file using such kind of tools and save the
result to a file then input to psql works fine.

> > >ï¿½Why did 7.1.3 allow the input of such data?
> > It's just becuase no data validation checking was there before 7.2.
> Data was entered by a Japanese translator using IE5.5, japanese fonts, Win2K, 
> Apache and Php with UTF-8 extensions. How can it be wrong? It displays well 
> under IE5.5.

I do not trust M$ products:-)

> Are you sure it is not a PostgreSQL 7.1.3 pg_dump related bug? 
> Other users have the same problem when migrating to 7.2.

Hard to believe it's a pg_dump bug.

> Furthermore, do you think I can migrate data using ODBC adodb calls?

I'm totally unsure about ODBC. Hiroshi, what do you think?
--
Tatsuo Ishii

Thu, 21 Feb 2002 00:15:53 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
Dear friends,

> Hard to believe it's a pg_dump bug.
You are right, Yudit shows an invalid UTF-8 character.

For example, record OID 20643 has invalid =E3=82 character. But, when 
querying the PostgreSQL 7.1.3 database, I can see the missing character which 
displays well in Apache/PHP.

It seems to be a pg_dump bug or a peculiar kind of UTF-8 (is there any 
difference between Windows / Unix ?). There are hundreds of errors in the 
complete dump. But the data displays always well.

How can I test the database further in psql or in PHP. I would like to query 
record OID 20643 and see if E3/82 is there or not. Any idea how to proceed?

Thank you very much for your help.
Best regards,
Jean-Michel POURE

Wed, 20 Feb 2002 17:20:43 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: UTF-8 data migration problem in Postgresql 7.2
Jean-Michel POURE <jm.poure@freesurf.fr> writes:
> You are right, Yudit shows an invalid UTF-8 character.

> For example, record OID 20643 has invalid =E3=82 character. But, when 
> querying the PostgreSQL 7.1.3 database, I can see the missing character which
> displays well in Apache/PHP.

Does your Apache/PHP environment set any particular client encoding?

I am wondering if Apache is seeing a different translation of the
database contents than what pg_dump got.

BTW, it seems entirely likely that Windows apps might display something
for UTF-8 sequences that are invalid according to the published specs.
It'd be just like Microsoft to "extend" the standard...

			regards, tom lane

Wed, 20 Feb 2002 12:04:29 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2 
> Does your Apache/PHP environment set any particular client encoding?
> I am wondering if Apache is seeing a different translation of the
> database contents than what pg_dump got.
I recompiled PHP with UTF-8 internal encoding. So no transcoding is performed.

> BTW, it seems entirely likely that Windows apps might display something
> for UTF-8 sequences that are invalid according to the published specs.
> It'd be just like Microsoft to "extend" the standard...

It might be the case, I agree. Could it be a problem of surface (UCS-2 
characters embedded in UTF-8)? PostgreSQL 7.3 should accept more UTF-8 
characters anyway as I can display all Japanese characters. Many users may 
have the same problem. So why filter?

Again, Ithe problem might also come from pg_dump. The database has French and 
Dutch content. There are also errors in the Dutch file... Is there a way to 
perform some testing?

Cheers,
Jean-Michel POURE

Wed, 20 Feb 2002 18:13:03 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2
Jean-Michel POURE wrote:
> 
> Dear friends,
> 
> > Hard to believe it's a pg_dump bug.
> You are right, Yudit shows an invalid UTF-8 character.
> 
> For example, record OID 20643 has invalid =E3=82 character. But, when
> querying the PostgreSQL 7.1.3 database, I can see the missing character which
> displays well in Apache/PHP.

Could you send me the image(jpeg, bmp etc format) which
corresponds to the line(OID 20643) ? 

regards,
Hiroshi Inoue

Thu, 21 Feb 2002 09:19:01 +0900
Hiroshi Inoue <Inoue@tpf.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
> I recompiled PHP with UTF-8 internal encoding. So no transcoding is performed.

Recompiled PHP with UTF-8? That sounds new to me. As far as I know,
there is no such a compiling option in PHP.

> > BTW, it seems entirely likely that Windows apps might display something
> > for UTF-8 sequences that are invalid according to the published specs.
> > It'd be just like Microsoft to "extend" the standard...
> 
> It might be the case, I agree. Could it be a problem of surface (UCS-2 
> characters embedded in UTF-8)? PostgreSQL 7.3 should accept more UTF-8 
> characters anyway as I can display all Japanese characters. Many users may 
> have the same problem. So why filter?

You need to make sure that M$ or whatever define such kind of
"extention" before jumping to the conclusion.

I still hard to believe E3/82/27 is a valid UTF-8 character even in
M$'s extention...
--
Tatsuo Ishii

Thu, 21 Feb 2002 10:08:26 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2
> You are right, Yudit shows an invalid UTF-8 character.
> 
> For example, record OID 20643 has invalid =E3=82 character. But, when 
> querying the PostgreSQL 7.1.3 database, I can see the missing character which 
> displays well in Apache/PHP.

How did you judge "displays well"? Can you show me the window capture?

The broken UTF-8 (E3/82/27) should be E3/82/92 by guessing from a
consistency and grammertical correctness as Japanese according to
Hiroshi (and I agree with him).
--
Tatsuo Ishii

Thu, 21 Feb 2002 10:08:33 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
> Data was entered by a Japanese translator using IE5.5, japanese fonts, Win2K, 
> Apache and Php with UTF-8 extensions. How can it be wrong? It displays well 
> under IE5.5.

Can you be more specific on how did you enter the data?

o Were server/clien encodings UTF-8 for PostgreSQL?

o What are versions of these softwares? Especially of PHP? Is it a
PHP4? if so, what version? What is the "Php with UTF-8 extensions"?
I've never heard of it.
--
Tatsuo Ishii

Thu, 21 Feb 2002 13:04:58 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
Dear Tatsuo,

Thank you for your previous answer.

> o Were server/clien encodings UTF-8 for PostgreSQL?
Yes.

> o What are versions of these softwares? Especially of PHP? Is it a
> PHP4? if so, what version? What is the "Php with UTF-8 extensions"?
> I've never heard of it.
It is PHP 4.0.6 with :
--enable-mbstring : Enable mbstring functions. This option is required to use 
mbstring functions. 
--enable-mbstr-enc-trans : Enable HTTP input character encoding conversion 
using mbstring conversion engine. If this feature is enabled, HTTP input 
character encoding may be converted to mbstring.internal_encoding 
automatically. 

Now, some more information:
1) Dutch text was entered using IE5.5. It is not faulty.

2) Japanese text was entered using OpenOffice latest release (sorry, I said 
IE5 but I was wrong), saved under UTF-8 and imported in PostgreSQL. Only 
Japanese data has problems. 

3) When opening a faulty Japanese record using Apache/IE5, the record is 
displayed correctly. Each faulty character is replaced by a Japanese 30A7 
gryph (looks like a French cross with two horizontal lines). What is this 
gryph? Does it mean 'I don't know' in Japanese.

The record is saved correctly using this 30A1 gryph (then it looks like it is 
fixed as I can dump it and import it in 7.2, but this is not a solution).

4) In PostgreSQL 7.1.3 original dump, there is only one faulty UTF-8 
character repeated 700 times. If you open my file in Yudit, it is displayed 
as =E3=82' Why is it always the same character everywhere? Maybe you could 
have a look at my source file again. Sounds like a bug (Open Office or 
PostgreSQL).

5) Surrogate pairs
I heard PostgreSQL did not support surrogate pairs. Is this a problem of 
surrogate pair? Just my 0.02 cents, I know very little about UTF-8.

Any help appreciated,
Thanks, Jean-Michel POURE


Thu, 21 Feb 2002 10:13:23 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: UTF-8 data migration problem in Postgresql 7.2
> > o Were server/clien encodings UTF-8 for PostgreSQL?
> Yes.
> 
> > o What are versions of these softwares? Especially of PHP? Is it a
> > PHP4? if so, what version? What is the "Php with UTF-8 extensions"?
> > I've never heard of it.
> It is PHP 4.0.6 with :
> --enable-mbstring : Enable mbstring functions. This option is required to use 
> mbstring functions. 
> --enable-mbstr-enc-trans : Enable HTTP input character encoding conversion 
> using mbstring conversion engine. If this feature is enabled, HTTP input 
> character encoding may be converted to mbstring.internal_encoding 
> automatically. 

Oh, that's a general functionality for handling multibyte characters,
not only for UTF-8. What are settings for mbstring in php.ini?
(entries begin with "mbstring.")

BTW, PHP4.0.6 is very buggy when used with PostgreSQL (random
crashes). I recomend to upgrade to 4.1.1.

> Now, some more information:
> 1) Dutch text was entered using IE5.5. It is not faulty.

I assume the web page's encoding was UTF-8.

> 2) Japanese text was entered using OpenOffice latest release (sorry, I said 
> IE5 but I was wrong), saved under UTF-8 and imported in PostgreSQL. Only 
> Japanese data has problems. 

Can I take a look at the UTF-8 text generated by OpenOffice?

> 3) When opening a faulty Japanese record using Apache/IE5, the record is 
> displayed correctly. Each faulty character is replaced by a Japanese 30A7 
> gryph (looks like a French cross with two horizontal lines). What is this 
> gryph? Does it mean 'I don't know' in Japanese.

What do you mean by "gryph"? Is 30A7 is an EUC-JP?

> The record is saved correctly using this 30A1 gryph (then it looks like it is 
> fixed as I can dump it and import it in 7.2, but this is not a solution).

Again, what is "gryph"?

> 4) In PostgreSQL 7.1.3 original dump, there is only one faulty UTF-8 
> character repeated 700 times. If you open my file in Yudit, it is displayed 
> as =E3=82' Why is it always the same character everywhere? Maybe you could 
> have a look at my source file again. Sounds like a bug (Open Office or 
> PostgreSQL).
> 
> 5) Surrogate pairs
> I heard PostgreSQL did not support surrogate pairs. Is this a problem of 
> surrogate pair? Just my 0.02 cents, I know very little about UTF-8.

I don't think so.
--
Tatsuo Ishii

Thu, 21 Feb 2002 18:31:54 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: UTF-8 data migration problem in Postgresql 7.2
Hi Jean-Michel,

I just started browsing this list again after a long absence...

* Jean-Michel POURE <jm.poure@freesurf.fr> [020221 18:39]:
> 5) Surrogate pairs
> I heard PostgreSQL did not support surrogate pairs. Is this a problem of 
> surrogate pair? Just my 0.02 cents, I know very little about UTF-8.

Surrogate pairs only exist in UTF-16. They are used to access
characters which are not on the BMP.

UTF-8 has a different way to encode these characters. Encoding
surrogates in UTF-8 is invalid and should be rejected by any
application receiving a UTF-8 stream (actually, they used to be just
irregular, but starting with Unicode 3.2, they will be illegal).

Regarding your sequence E3/82/27, it cannot be valid under any scheme.
UTF-8 is done in a way that any subsequent byte is equal or above
0x80. For E3 in particular, the 3rd byte has to be between 80 and BF.

Anyway "UTF-8 encoded surrogates" can only start with ED, so that's
not your problem here.

Hope this helps.

Patrice

-- 
Patrice Hï¿œdï¿œ
email: patrice hede ï¿œ islande org
www  : http://www.islande.org/

Thu, 21 Feb 2002 19:19:11 +0100
Patrice =?iso-8859-15?Q?H=E9d=E9?= <patrice@idf.net>
Re: UTF-8 data migration problem in Postgresql 7.2
Le Jeudi 21 Février 2002 05:04, Tatsuo Ishii a écrit :
> o Were server/clien encodings UTF-8 for PostgreSQL?
Yes, I am absolutely sure. Everything from Web page, Apache to PostgreSQL.

> o What are versions of these softwares? Especially of PHP? Is it a
> PHP4? if so, what version? What is the "Php with UTF-8 extensions"?
> I've never heard of it.

PHP 4.06. PHP needs to be compiled with "--enable-mbstring" to allow the 
parsing of UTF-8 files and strings. Internal settings set to UTF-8.

I guess the error might come from PostgreSQL string parsing functions:
1) new.target_content:= translate(new.target_content, chr(146), chr (39)) ;
2) substring(text, int4). Is substring multibyte safe?

Cheers,
Jean-Michel

Fri, 22 Feb 2002 08:41:12 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2
> I guess the error might come from PostgreSQL string parsing functions:
> 1) new.target_content:= translate(new.target_content, chr(146), chr (39)) ;
> 2) substring(text, int4). Is substring multibyte safe?

I believe 7.2's translate and substring are multibyte safe.
Can you show us reproducable examples?
--
Tatsuo Ishii

Fri, 22 Feb 2002 18:05:20 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2
> I guess the error might come from PostgreSQL string parsing functions:
> 1) new.target_content:= translate(new.target_content, chr(146), chr (39)) ;
> 2) substring(text, int4). Is substring multibyte safe?

Oh, I think I found the source of the problem. Apparently you did 1)
above in PostgreSQL 7.1.3, right?

7.1's translate is not multibyte aware, so it simply replaces every
occurence of chr(146) = 0x92 to chr(39) = 0x27 even if it's part of
the multibyte sequece.

The broken seqneuce was E3/82/27, which is apparently replaced by
translate() from E3/82/92.
--
Tatsuo Ishii

Fri, 22 Feb 2002 18:23:10 +0900
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2
Le Vendredi 22 Février 2002 10:05, Tatsuo Ishii a écrit :
> I believe 7.2's translate and substring are multibyte safe.
> Can you show us reproducable examples?

No, this was under 7.1.3. Good to know 7.2 is multibyte safe.
Thanks, Jean-Michel

Fri, 22 Feb 2002 12:39:55 +0100
Jean-Michel POURE <jm.poure@freesurf.fr>
Re: [ODBC] UTF-8 data migration problem in Postgresql 7.2