From kraj@illumen.com Tue Aug 8 02:08:35 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id EC7579FB22B for ; Mon, 7 Aug 2006 23:08:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 59290-03 for ; Tue, 8 Aug 2006 02:08:26 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from stash.illumen.com (unknown [64.207.29.137]) by postgresql.org (Postfix) with ESMTP id C32739FB21F for ; Mon, 7 Aug 2006 23:08:25 -0300 (ADT) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6BA8F.87161A79" Subject: Strange UTF8 issue with pg_dump/pg_restore X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Mon, 7 Aug 2006 20:08:23 -0600 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Strange UTF8 issue with pg_dump/pg_restore Thread-Index: Aca6ifctc6KKIygSSX2NOJRBY3kKAQABLhnw From: "Benjamin Krajmalnik" To: "pgsql admin" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/81 X-Sequence-Number: 22625 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6BA8F.87161A79 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable I have a database whose records are inserted via ODBC. I turned on logging, and the client is explicitly setting the client encoding to UTF. =20 Rows are inserted into the tables. When I do a pg_dump, I do not get any errors. When I try tor restore, I am getting an error regarding an invalid UTF-8 character sequence. Of course, this renders compressed backups useless, since I have no way of manually fixing the file. The process of fixing the file is, of course, painful. =20 Is this a known issue? I am running PG 8.1.4. The problem happened on PG Windows. Tomorrow I will try to see if the problem exists in the FreeBSD compile. If I get it to reproduce, I will be able to send some data dumps, but will need to know who to send it to. =20 Regards, =20 Benjamin =20 =20 ------_=_NextPart_001_01C6BA8F.87161A79 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable
I have a database whose records are inserted = via=20 ODBC.
I turned on logging, and the client is = explicitly setting=20 the client encoding to UTF.
 
Rows are inserted into the tables.  When I = do a=20 pg_dump, I do not get any errors.  When I try tor restore, I am = getting an=20 error regarding an invalid UTF-8 character sequence.
Of course, this renders compressed backups = useless, since I=20 have no way of manually fixing the file.  The process of fixing the = file=20 is, of course, painful.
 
Is this a known issue?  I am running PG = 8.1.4. =20 The problem happened on PG Windows.
Tomorrow I will try to see if the problem = exists in the=20 FreeBSD compile.  If I get it to reproduce, I will be able to send = some=20 data dumps, but will need to know who to send it to.
 
Regards,
 
Benjamin
 
 
------_=_NextPart_001_01C6BA8F.87161A79-- From jnasby@pervasive.com Wed Aug 9 02:54:25 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id EBF249FB2D8 for ; Tue, 8 Aug 2006 23:54:25 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 20216-02 for ; Wed, 9 Aug 2006 02:54:25 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 158E89FB219 for ; Tue, 8 Aug 2006 23:54:24 -0300 (ADT) Received: from [192.168.42.113] (natpool.bovine.net [67.100.216.14]) (using TLSv1 with cipher RC4-SHA (128/128 bits)) (No client certificate requested) by noel.decibel.org (Postfix) with ESMTP id 9ADDA5643F; Tue, 8 Aug 2006 21:54:23 -0500 (CDT) In-Reply-To: References: Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <773783AD-1DEC-411E-9BF1-16293664A090@pervasive.com> Cc: "pgsql admin" Content-Transfer-Encoding: 7bit From: Jim Nasby Subject: Re: Strange UTF8 issue with pg_dump/pg_restore Date: Tue, 8 Aug 2006 11:48:37 -0500 To: Benjamin Krajmalnik X-Mailer: Apple Mail (2.752.2) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200608/101 X-Sequence-Number: 22644 On Aug 7, 2006, at 9:08 PM, Benjamin Krajmalnik wrote: > I have a database whose records are inserted via ODBC. > I turned on logging, and the client is explicitly setting the > client encoding to UTF. > > Rows are inserted into the tables. When I do a pg_dump, I do not > get any errors. When I try tor restore, I am getting an error > regarding an invalid UTF-8 character sequence. > Of course, this renders compressed backups useless, since I have no > way of manually fixing the file. The process of fixing the file > is, of course, painful. > > Is this a known issue? I am running PG 8.1.4. The problem > happened on PG Windows. > Tomorrow I will try to see if the problem exists in the FreeBSD > compile. If I get it to reproduce, I will be able to send some > data dumps, but will need to know who to send it to. Are the dumps from an older version? There's a known issue that older versions weren't doing proper checking, in which case you can have invalid UTF8 characters. The fix is to run the dump through iconv. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From postgresql@aranya.com Tue Aug 8 18:36:56 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4CB1C9FB218 for ; Tue, 8 Aug 2006 15:36:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 25156-04 for ; Tue, 8 Aug 2006 15:36:47 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.187]) by postgresql.org (Postfix) with ESMTP id 350159FA621 for ; Tue, 8 Aug 2006 15:36:47 -0300 (ADT) Received: by nf-out-0910.google.com with SMTP id n28so346233nfc for ; Tue, 08 Aug 2006 11:36:46 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:references:x-google-sender-auth; b=jPhDiFTOstAo3KxnVRmom0YwbO8e1K5Fu9395O60gqxvAkUoFkr2c7qYjamYm1RhuusgrxsR7KKgLydKSnRYSWMeoc++VApzp0S5wc+QJlDRIPRPWupQjmZn/HwI8Zy1j5GGhYZqFIiYlywUdWB1smbnfCOh3ujwoawmC8c0g1A= Received: by 10.78.127.2 with SMTP id z2mr2879204huc; Tue, 08 Aug 2006 11:36:45 -0700 (PDT) Received: by 10.78.32.10 with HTTP; Tue, 8 Aug 2006 11:36:45 -0700 (PDT) Message-ID: Date: Tue, 8 Aug 2006 13:36:45 -0500 From: "Aaron Bono" To: "Benjamin Krajmalnik" Subject: Re: Strange UTF8 issue with pg_dump/pg_restore Cc: "pgsql admin" In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14532_6333156.1155062205092" References: X-Google-Sender-Auth: 9e06aeb861b4f551 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.756 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, HTML_40_50, HTML_MESSAGE, SPF_PASS X-Spam-Level: X-Archive-Number: 200608/94 X-Sequence-Number: 22638 ------=_Part_14532_6333156.1155062205092 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/7/06, Benjamin Krajmalnik wrote: > > I have a database whose records are inserted via ODBC. > I turned on logging, and the client is explicitly setting the client > encoding to UTF. > > Rows are inserted into the tables. When I do a pg_dump, I do not get any > errors. When I try tor restore, I am getting an error regarding an invalid > UTF-8 character sequence. > Of course, this renders compressed backups useless, since I have no way of > manually fixing the file. The process of fixing the file is, of course, > painful. > Why can't you manually fix it? I know you cannot fix it while compressed but you should be able to fix it after it is uncompressed, right? Have you tried to set the encoding just like the client before you do a restore from backup? ================================================================== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com ================================================================== ------=_Part_14532_6333156.1155062205092 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline On 8/7/06, Benjamin Krajmalnik <kraj@illumen.com> wrote:
I have a database whose records are inserted via ODBC.
I turned on logging, and the client is explicitly setting the client encoding to UTF.
 
Rows are inserted into the tables.  When I do a pg_dump, I do not get any errors.  When I try tor restore, I am getting an error regarding an invalid UTF-8 character sequence.
Of course, this renders compressed backups useless, since I have no way of manually fixing the file.  The process of fixing the file is, of course, painful.

Why can't you manually fix it?  I know you cannot fix it while compressed but you should be able to fix it after it is uncompressed, right?

Have you tried to set the encoding just like the client before you do a restore from backup?

==================================================================
   Aaron Bono
   Aranya Software Technologies, Inc.
   http://www.aranya.com
   http://codeelixir.com
================================================================== ------=_Part_14532_6333156.1155062205092-- From jnasby@pervasive.com Wed Aug 9 18:21:22 2006 X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id 478409FB2F1 for ; Wed, 9 Aug 2006 15:21:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 45919-10 for ; Wed, 9 Aug 2006 18:21:21 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from ausimss.pervasive.com (ausimss.pervasive.com [66.45.103.246]) by postgresql.org (Postfix) with ESMTP id 3C7F09FB2C4 for ; Wed, 9 Aug 2006 15:21:20 -0300 (ADT) Received: from ausbayes2.aus.pervasive.com ([172.16.8.6]) by ausimss.pervasive.com with InterScan Messaging Security Suite; Wed, 09 Aug 2006 13:21:15 -0500 Received: from AUSOWA.aus.pervasive.com ([172.16.4.8]) by ausbayes2.aus.pervasive.com with Microsoft SMTPSVC(5.0.2195.6713); Wed, 9 Aug 2006 13:21:15 -0500 Received: from ausmailid.aus.pervasive.com ([172.16.4.64]) by AUSOWA.aus.pervasive.com with Microsoft SMTPSVC(6.0.3790.1830); Wed, 9 Aug 2006 13:20:07 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.5.7226.0 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C6BBE0.711B7B62" Subject: Re: Strange UTF8 issue with pg_dump/pg_restore Date: Wed, 9 Aug 2006 13:20:06 -0500 Message-ID: <4D27CB1096EF1C408F4BFAB0046EC7B6099FF5@ausmailid.aus.pervasive.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore Thread-Index: Aca7XyGHmXi2dNGRTWCrYu79L/ghGQABSJIKAB8AAlA= From: "Jim Nasby" To: "Benjamin Krajmalnik" Cc: "Pgsql-Admin \(E-mail\)" X-OriginalArrivalTime: 09 Aug 2006 18:20:07.0308 (UTC) FILETIME=[713624C0:01C6BBE0] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200608/118 X-Sequence-Number: 22661 This is a multi-part message in MIME format. ------_=_NextPart_001_01C6BBE0.711B7B62 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Please include the list in your replies so other people can help. -----Original Message----- From: Benjamin Krajmalnik [mailto:kraj@illumen.com] Sent: Tuesday, August 08, 2006 10:31 PM To: Jim Nasby Subject: RE: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore I had corrupt rows inserted on 8.1.4/Windows. I had truncated the = table, and it startd collecting data again. I have since moved to 8.1.4/FreeBSD. I have been running the data for 2 = days now, so the odds of having a corrupt record is pretty good if the = problem exists. I will wait a few more days and will pg_dump the table = which had the problems and attempt to restor it on a dev server to see = if I see a problem. =20 Is iconv a utility that comes woth PG? How can I run the data through = it (I am doing pg_dump in custom format). =20 No, it's a unix utility. You'd have to dump in plain text output, and = then run that through iconv. There's info about this in the 8.1 release = notes. _____ =20 From: Jim Nasby [mailto:jnasby@pervasive.com] Sent: Tue 8/8/2006 10:48 AM To: Benjamin Krajmalnik Cc: pgsql admin Subject: Re: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore On Aug 7, 2006, at 9:08 PM, Benjamin Krajmalnik wrote: > I have a database whose records are inserted via ODBC. > I turned on logging, and the client is explicitly setting the=20 > client encoding to UTF. > > Rows are inserted into the tables. When I do a pg_dump, I do not=20 > get any errors. When I try tor restore, I am getting an error=20 > regarding an invalid UTF-8 character sequence. > Of course, this renders compressed backups useless, since I have no=20 > way of manually fixing the file. The process of fixing the file=20 > is, of course, painful. > > Is this a known issue? I am running PG 8.1.4. The problem=20 > happened on PG Windows. > Tomorrow I will try to see if the problem exists in the FreeBSD=20 > compile. If I get it to reproduce, I will be able to send some=20 > data dumps, but will need to know who to send it to. Are the dumps from an older version? There's a known issue that older=20 versions weren't doing proper checking, in which case you can have=20 invalid UTF8 characters. The fix is to run the dump through iconv. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com = work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ------_=_NextPart_001_01C6BBE0.711B7B62 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Re: [ADMIN] Strange UTF8 issue with pg_dump/pg_restore
Please=20 include the list in your replies so other people can = help.
-----Original Message-----
From: Benjamin = Krajmalnik=20 [mailto:kraj@illumen.com]
Sent: Tuesday, August 08, 2006 = 10:31=20 PM
To: Jim Nasby
Subject: RE: [ADMIN] Strange UTF8 = issue=20 with pg_dump/pg_restore

I had corrupt rows inserted = on=20 8.1.4/Windows.  I had truncated the table, and it startd = collecting data=20 again.
I have since moved to=20 8.1.4/FreeBSD.  I have been running the data for 2 days now, so = the odds=20 of having a corrupt record is pretty good if the problem exists.  = I will=20 wait a few more days and will pg_dump the table which had the problems = and=20 attempt to restor it on a dev server to see if I see a = problem.
 
Is iconv a utility that = comes woth=20 PG?  How can I run the data through it (I am doing pg_dump in = custom=20 format).
 
No,=20 it's a unix utility. You'd have to dump in plain text output, and then = run that=20 through iconv. There's info about this in the 8.1 release=20 notes.


From: Jim Nasby=20 [mailto:jnasby@pervasive.com]
Sent: Tue 8/8/2006 10:48=20 AM
To: Benjamin Krajmalnik
Cc: pgsql=20 admin
Subject: Re: [ADMIN] Strange UTF8 issue with=20 pg_dump/pg_restore

On Aug 7, 2006, at 9:08 PM, Benjamin Krajmalnik = wrote:
>=20 I have a database whose records are inserted via ODBC.
> I = turned on=20 logging, and the client is explicitly setting the 
> client = encoding to UTF.
>
> Rows are inserted into the = tables.  When=20 I do a pg_dump, I do not 
> get any errors.  When I = try tor=20 restore, I am getting an error 
> regarding an invalid = UTF-8=20 character sequence.
> Of course, this renders compressed backups = useless, since I have no 
> way of manually fixing the = file. =20 The process of fixing the file 
> is, of course,=20 painful.
>
> Is this a known issue?  I am running PG=20 8.1.4.  The problem 
> happened on PG Windows.
> = Tomorrow I will try to see if the problem exists in the = FreeBSD 
>=20 compile.  If I get it to reproduce, I will be able to send=20 some 
> data dumps, but will need to know who to send it=20 to.

Are the dumps from an older version? There's a known issue = that=20 older 
versions weren't doing proper checking, in which case = you can=20 have 
invalid UTF8 characters. The fix is to run the dump = through=20 iconv.
--
Jim C. Nasby, Sr. Engineering=20 Consultant      = jnasby@pervasive.com
Pervasive=20 Software      http://pervasive.com   = work:=20 512-231-6117
vcard: http://jim.nasby.net/pervasiv= e.vcf      =20 cell: 512-569-9461

------_=_NextPart_001_01C6BBE0.711B7B62--