Обсуждение: ERROR: could not convert UTF8 character to ISO8859-1
I have seen several people with this same error. I get mine when trying to backup using pg_dump, causing my backups to fail. I am using PostgreSQL 8.1 on Windows XP.The databases are in UTF8. I get the same error from the command line and using pgadmin3. Has anyone found a solution for this? Looking around online, I see that this supposedly codes for a lowercase a with a circumflex (which I don't have in tha table thet triggeres the error), but that this may be the first part of a three-byte sequence that codes for something else. Does anyone know of a way to pinpoint the character in the database? I am not sure how to search for a raw unicode character through psql or pgadmin... Thanks for any help! JP
To answer my own mail, I shut down the 8.1 database, and started up the 8.0 PostgreSQL install that is still on my computer, and the backups work fine. I do get the message about how 8.1 handles sorting of unicode characters more correctly, but looking at my backups, I can see that the full back up failed about the time that I switched to the newer version. Is there a fix for this? Is this a bug? Is there a workaround? Thanks for any help. JP JP Glutting wrote: > I have seen several people with this same error. I get mine when > trying to backup using pg_dump, causing my backups to fail. > > I am using PostgreSQL 8.1 on Windows XP.The databases are in UTF8. I > get the same error from the command line and using pgadmin3. > > Has anyone found a solution for this? Looking around online, I see > that this supposedly codes for a lowercase a with a circumflex (which > I don't have in tha table thet triggeres the error), but that this may > be the first part of a three-byte sequence that codes for something else. > > Does anyone know of a way to pinpoint the character in the database? I > am not sure how to search for a raw unicode character through psql or > pgadmin... > > Thanks for any help! > > JP > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- ____________________________________________________ Oficina de Qualitat Tel: 973 703 103 Universitat de Lleida Fax: 973 702 036 Pl. Víctor Siurana, 1 25003 Lleida ____________________________________________________
More information. I backed up a database (one that failed to backup in 8.1) from the old 8.0 system, and re-imported it into the 8.0 system without any problem. Then I imported it into the 8.1 system without errors, and tried to do a backup again. It failed. So there seems to be either a flaw in the 8.1 system, or perhaps a problem with the way 8.0 backs up it's data which only shows up when imported into the newer system. Right now I need the data I have in the 81 system, and I can't back it up except by shutting down Postgres and copying the whole /data directory. I think I am going to try to go back to 8.0. Is it a very bad idea to shut down Postgres and try to replace the data directory for 8.0 withe the data directory for 8.1? Any help or suggestions appreciated. JP JP Glutting wrote: > To answer my own mail, I shut down the 8.1 database, and started up > the 8.0 PostgreSQL install that is still on my computer, and the > backups work fine. I do get the message about how 8.1 handles sorting > of unicode characters more correctly, but looking at my backups, I can > see that the full back up failed about the time that I switched to the > newer version. > > Is there a fix for this? Is this a bug? Is there a workaround? > > Thanks for any help. > > JP > > JP Glutting wrote: > >> I have seen several people with this same error. I get mine when >> trying to backup using pg_dump, causing my backups to fail. >> >> I am using PostgreSQL 8.1 on Windows XP.The databases are in UTF8. I >> get the same error from the command line and using pgadmin3. >> >> Has anyone found a solution for this? Looking around online, I see >> that this supposedly codes for a lowercase a with a circumflex (which >> I don't have in tha table thet triggeres the error), but that this >> may be the first part of a three-byte sequence that codes for >> something else. >> >> Does anyone know of a way to pinpoint the character in the database? >> I am not sure how to search for a raw unicode character through psql >> or pgadmin... >> >> Thanks for any help! >> >> JP >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 1: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly > > > > -- ____________________________________________________ Oficina de Qualitat Tel: 973 703 103 Universitat de Lleida Fax: 973 702 036 Pl. Víctor Siurana, 1 25003 Lleida ____________________________________________________
This is reproducible, and I think it is a bug, because I have the problem not only with data backed up from Postgres 8.0 and restored to Postgres 8.1, but with data generated in Postgres 8.1. It is reproducible, at least on Windows XP. Here is a table that I backed up from Postgres 8.0. It can be restored into a Postgres 8.0 or 8.1 database, but it cannot then be backed up in Postgres 8.1. (Works fine in Postgres 8.0). I can send a file for the whole database (it just has this one table in it, so space should not be an issue. Can anyone reproduce this on Linux or another platform? Is this a known bug? JP JP Glutting wrote: > More information. I backed up a database (one that failed to backup in > 8.1) from the old 8.0 system, and re-imported it into the 8.0 system > without any problem. Then I imported it into the 8.1 system without > errors, and tried to do a backup again. It failed. > > So there seems to be either a flaw in the 8.1 system, or perhaps a > problem with the way 8.0 backs up it's data which only shows up when > imported into the newer system. > > Right now I need the data I have in the 81 system, and I can't back it > up except by shutting down Postgres and copying the whole /data > directory. I think I am going to try to go back to 8.0. Is it a very > bad idea to shut down Postgres and try to replace the data directory > for 8.0 withe the data directory for 8.1? > > Any help or suggestions appreciated. > > JP > > JP Glutting wrote: > >> To answer my own mail, I shut down the 8.1 database, and started up >> the 8.0 PostgreSQL install that is still on my computer, and the >> backups work fine. I do get the message about how 8.1 handles sorting >> of unicode characters more correctly, but looking at my backups, I >> can see that the full back up failed about the time that I switched >> to the newer version. >> >> Is there a fix for this? Is this a bug? Is there a workaround? >> >> Thanks for any help. >> >> JP >> >> JP Glutting wrote: >> >>> I have seen several people with this same error. I get mine when >>> trying to backup using pg_dump, causing my backups to fail. >>> >>> I am using PostgreSQL 8.1 on Windows XP.The databases are in UTF8. I >>> get the same error from the command line and using pgadmin3. >>> >>> Has anyone found a solution for this? Looking around online, I see >>> that this supposedly codes for a lowercase a with a circumflex >>> (which I don't have in tha table thet triggeres the error), but that >>> this may be the first part of a three-byte sequence that codes for >>> something else. >>> >>> Does anyone know of a way to pinpoint the character in the database? >>> I am not sure how to search for a raw unicode character through psql >>> or pgadmin... >>> >>> Thanks for any help! >>> >>> JP >>> >>> ---------------------------(end of >>> broadcast)--------------------------- >>> TIP 1: if posting/reading through Usenet, please send an appropriate >>> subscribe-nomail command to majordomo@postgresql.org so that your >>> message can get through to the mailing list cleanly >>
Вложения
JP Glutting <jpglutting@oqua.udl.es> writes:
> This is reproducible, and I think it is a bug, because I have the
> problem not only with data backed up from Postgres 8.0 and restored to
> Postgres 8.1, but with data generated in Postgres 8.1. It is
> reproducible, at least on Windows XP. Here is a table that I backed up
> from Postgres 8.0. It can be restored into a Postgres 8.0 or 8.1
> database, but it cannot then be backed up in Postgres 8.1.
The error message seems perfectly clear to me: you've got a character in
that table that has no representation in LATIN1. The difference between
8.0 and 8.1 behavior is probably because you're somehow using different
client_encoding settings in the two cases.
regards, tom lane
On Mar 27, 2006, at 4:46 AM, JP Glutting wrote: > Can anyone reproduce this on Linux or another platform? Is this a > known bug? I took your dump and loaded it into a Pg 8.1.3 database on FreeBSD 6 with UTF-8 encoding. Then I did pg_dump on that database and it had no complaints. The data looks the same when scanned by eye (did not do direct comparison). So either it is a Windows related bug or you're not doing the same thing I did.
Thank you ver much for you responase. Yes, I had to change the client encoding for Postgres 8.1 (I had forgotten) to Latin1 because of a client encoding mismatch error while using the ODBC drivers on Windows. Stupid of me. Sorry for the bother - I changed the client encoding to UTF8 and it works now (not sure whay it was not working a month or so ago). Cheers, JP Tom Lane wrote: >JP Glutting <jpglutting@oqua.udl.es> writes: > > >>This is reproducible, and I think it is a bug, because I have the >>problem not only with data backed up from Postgres 8.0 and restored to >>Postgres 8.1, but with data generated in Postgres 8.1. It is >>reproducible, at least on Windows XP. Here is a table that I backed up >>from Postgres 8.0. It can be restored into a Postgres 8.0 or 8.1 >>database, but it cannot then be backed up in Postgres 8.1. >> >> > >The error message seems perfectly clear to me: you've got a character in >that table that has no representation in LATIN1. The difference between >8.0 and 8.1 behavior is probably because you're somehow using different >client_encoding settings in the two cases. > > regards, tom lane > >