invalid byte sequence for encoding "UTF8"

hi, I describe my steps:

1. installed postgresql 8.2 via apt-get (ubuntu gutsy)
2. # sudo -u postgres psql template1
3. template1=# CREATE ROLE kokomo WITH LOGIN CREATEDB PASSWORD
'kokomo';
4. template1=# \q
5. # createdb kokomo_dev -U kokomo -W

Here I receive:
createdb: could not connect to database postgres: FATAL: Ident
authentication failed for user "kokomo"

So, given that I do not fail submitting the password for kokomo when
requested... what could be the problem? Have I to create a system user
for kokomo?
If I use pgadmin3 I see the kokomo user with it own encrypted password
and I can create the DB with kokomo as a owner. So what am I missing
in the command line?

Thanks for help.

On 28/11/2007 14:02, della wrote:

> createdb: could not connect to database postgres: FATAL: Ident
> authentication failed for user "kokomo"
>
> So, given that I do not fail submitting the password for kokomo when
> requested... what could be the problem? Have I to create a system user
> for kokomo?
> If I use pgadmin3 I see the kokomo user with it own encrypted password
> and I can create the DB with kokomo as a owner. So what am I missing
> in the command line?

Have a look at the docs for the various client authentication
mechanisms, and particularly the pg_hba.conf file.....you need to add a
line to it requiring a password for your new user and the host from
which it is connecting.

http://www.postgresql.org/docs/8.2/interactive/client-authentication.html

HTH,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
---------------------------------------------------------------

On 11/28/07, della <ildella(at)gmail(dot)com> wrote:
>
> hi, I describe my steps:
>
> 1. installed postgresql 8.2 via apt-get (ubuntu gutsy)
> 2. # sudo -u postgres psql template1
> 3. template1=# CREATE ROLE kokomo WITH LOGIN CREATEDB PASSWORD
> 'kokomo';
> 4. template1=# \q
> 5. # createdb kokomo_dev -U kokomo -W
>
> Here I receive:
> createdb: could not connect to database postgres: FATAL: Ident
> authentication failed for user "kokomo"
>
> So, given that I do not fail submitting the password for kokomo when
> requested... what could be the problem? Have I to create a system user
> for kokomo?
> If I use pgadmin3 I see the kokomo user with it own encrypted password
> and I can create the DB with kokomo as a owner. So what am I missing
> in the command line?
>
> Thanks for help.

Looks like you have a Database user kokomo but since your authentication
method in pg_hba.conf is set to ident, which means database user is mapped
to an OS user, and you need to be logged in as that OS user on which a DB
user is mapped. By default the mapping is on "sameuser" which means inorder
to logon as kokomo db user you will have to be authenticated /logged in as
kokomo OS user.

Change the pg_hba.conf auth method to trust or password

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Hi People,

I've setup a postgres 8.2 server and have a database setup with UTF8
encoding. I intend to read some of our legacy data into the table,
this legacy data is in ASCII format, and as far as I know is 8 bit
ASCII.

We have a migration tool from mertechdata.com to convert these files
that are in a DataFlex format into out postgres tables.

Some files convert over okay, and some come up with the error message
'invalid byte sequence for encoding "UTF8"'. the files that come up
with the error are created correctly and so are their index's, but as
soon as we come to insert the data we get this error.

Does anyone know why we're getting this error message? And uis there
a way to suppress it, or can we get around it using another format?

Our migration utility does ask us to select the correct encoding for
our database, and we select UTF8 but we still get the error. What do
you guys think? Possibly the migration tools fault?

I thought we may be able to get around it using SQL_ASCII encoding -
but it's ony 7 bit, so would we loose some data? Also our conversion
utility doesn't have the option to use SQL_ASCII.

Are there any more flexible formats we could use? I noticed we have
Latin 1-10 and ISO formats. Is there any reason why we shouldn't use
these?

Thanks
Glyn

___________________________________________________________
Yahoo! Answers - Got a question? Someone out there knows the answer. Try it
now.
http://uk.answers.yahoo.com/

On 11/30/07, Glyn Astill <glynastill(at)yahoo(dot)co(dot)uk> wrote:
>
> Hi People,
>
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.
>
> We have a migration tool from mertechdata.com to convert these files
> that are in a DataFlex format into out postgres tables.
>
> Some files convert over okay, and some come up with the error message
> 'invalid byte sequence for encoding "UTF8"'. the files that come up
> with the error are created correctly and so are their index's, but as
> soon as we come to insert the data we get this error.
>
> Does anyone know why we're getting this error message? And uis there
> a way to suppress it, or can we get around it using another format?
>
> Our migration utility does ask us to select the correct encoding for
> our database, and we select UTF8 but we still get the error. What do
> you guys think? Possibly the migration tools fault?
>
> I thought we may be able to get around it using SQL_ASCII encoding -
> but it's ony 7 bit, so would we loose some data? Also our conversion
> utility doesn't have the option to use SQL_ASCII.
>
> Are there any more flexible formats we could use? I noticed we have
> Latin 1-10 and ISO formats. Is there any reason why we shouldn't use
> these?
>
> Thanks
> Glyn

Latin1 is a single byte encoding, i can't think of any reason not to try it
if the characters you have are valid ISO8859 characters. Probably posting
the hex codes of some characters which are failing will help.

> ___________________________________________________________
> Yahoo! Answers - Got a question? Someone out there knows the answer. Try
> it
> now.
> http://uk.answers.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--
Usama Munir Dar http://linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

[Generally it's not a good idea to start a new thread by responding to an
existing one, it confuses people and makes it more likely for your question to
be missed.]

"Glyn Astill" <glynastill(at)yahoo(dot)co(dot)uk> writes:

> Hi People,
>
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.

ASCII is a 7-bit encoding. If you have bytes with the high bit set then you
have something else. Can you give any examples of characters with the high bit
set and what you think they represent?

> We have a migration tool from mertechdata.com to convert these files
> that are in a DataFlex format into out postgres tables.
>
> Some files convert over okay, and some come up with the error message
> 'invalid byte sequence for encoding "UTF8"'. the files that come up
> with the error are created correctly and so are their index's, but as
> soon as we come to insert the data we get this error.

This error indicates that you are trying to import data with client_encoding
set to UTF8 but the data isn't actually UTF8 and contains invalid byte
sequences for UTF8.

If your migration toolkit lets you set the client encoding separately from the
server encoding then you can set the client encoding to match your data and
the server encoding to the encoding you want the server to use.

Otherwise you'll have to recode the data to UTF8 or whatever encoding you want
the data to be. There are tools to do this (such as GNU "recode" for example).

> Are there any more flexible formats we could use? I noticed we have
> Latin 1-10 and ISO formats. Is there any reason why we shouldn't use
> these?

Well there are pros and cons. The 1-byte ISO formats will be more space
efficient and also allow some cpu optimizations so they perform somewhat
better. But if you ever need to store a character which doesn't fit in the
encoding you'll be stuck. Postgres doesn't support using multiple encodings in
the same database (or effectively even in the same initdb cluster).

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

On Fri, Nov 30, 2007 at 09:44:36AM +0000, Glyn Astill wrote:
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.

Your problem is that there is no such thing as "8-bit ASCII". Determine
what encoding the data is actually in and use that.

> Our migration utility does ask us to select the correct encoding for
> our database, and we select UTF8 but we still get the error. What do
> you guys think? Possibly the migration tools fault?

I think they mean to select the correct encoding for the data, what
encoding the database is in isn't relevent. The database can convert
any encoding you want to use to UTF-8 as required.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

Glyn Astill wrote:
> I've setup a postgres 8.2 server and have a database setup with UTF8
> encoding. I intend to read some of our legacy data into the table,
> this legacy data is in ASCII format, and as far as I know is 8 bit
> ASCII.
>
> We have a migration tool from mertechdata.com to convert these files
> that are in a DataFlex format into out postgres tables.

In which format are the data? Text files? SQL statements?
Something binary?

> Some files convert over okay, and some come up with the error message
> 'invalid byte sequence for encoding "UTF8"'. the files that come up
> with the error are created correctly and so are their index's, but as
> soon as we come to insert the data we get this error.

Well, so you claim, but can you prove it?
Do you use a PostgreSQL utility to import the data?
If yes, which tool? What is the exact command line?

> Does anyone know why we're getting this error message? And uis there
> a way to suppress it, or can we get around it using another format?

By "format" I believe that you mean "encoding".
It does not matter what encoding you use as long as the data can
be represented in it, you tell PostgreSQL what the encoding is, and
the data are correct.

There is no advantage of one encoding over the other in this respect.

> Our migration utility does ask us to select the correct encoding for
> our database, and we select UTF8 but we still get the error. What do
> you guys think? Possibly the migration tools fault?

If PostgreSQL says that the data is not UTF-8, we tend to believe it.

To say more, one would need more information.
Can you identify the string about which PostgreSQL complains?
What does it look like?

> I thought we may be able to get around it using SQL_ASCII encoding -
> but it's ony 7 bit, so would we loose some data? Also our conversion
> utility doesn't have the option to use SQL_ASCII.

If you use SQL_ASCII you may succeed in getting the incorrect data into
the database, but that will not make you happy because the data will
not stop being incorrect just because they are in the database.

Yours,
Laurenz Albe