At 10:00 PM 29/03/2006, you wrote:

> > >> why? Is this a bug or a shortcoming? is it documented anywhere?
> > >> Should it be?
> > >
> > >Ehm, so a SERVER SIDE primary key can stop working
> > >if you use a different character set for a CLIENT connection?
> >
> > Exactly. I'm afraid that conversion from inserted string to index
> > key is performed using connection charset with default collation.
>
>So there's two things wrong here?
>
>1) the index key always uses the default collation, despite settings
>in the table column
>
>2) a client characterset can influence server STORAGE behaviour
>
>
>Is this correct?

Well, sort of. It looks as if there's confusion here about "default"
and charset vs collation. An index will have the collation of the
column it is declared on.

The problem with this particular case (apart from the design error of
putting a PK on a column with a big payload of semantics and
attributes) is that he defined the column using a charset that was
not the default charset of the database. Since the charset for your
connection has to be the default database charset, your incoming data
will be in whatever character set is the client charset - unless you
CAST the incoming data every single time - cast (incoming_data as
varchar(n) character set blah)

Now, from the POV of the index, it can only map characters that are
actually valid for the collation. I have no idea what goes on under
the hood for this situation (how it treats the "illegal" characters)
but, from the symptoms described, the attempt to create the primary
key constraint failed. I've been watching the messages but I haven't
seen one where the guy came back with the answer to what he saw using
isql>show table.

./heLen