Illegal mix of collations error

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Illegal mix of collations error

Simon Buchanan
Hi There,

We have postfix storing its transport and alias data in mysql, but we are getting this error (which has just appeared out of knowwhere - yes well, OK, not knowwhere, but we dont know where!)

# cat /etc/postfix/mysql-transport.cf
user = mail-in1
password = ******
dbname = postfix
table = transport
hosts = 210.48.XX.XXX
select_field = transport
where_field = domain

Here is the error:

Jun  8 07:15:19 mail-in1 postfix/trivial-rewrite[23183]: warning: mysql query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Jun  8 07:15:19 mail-in1 postfix/trivial-rewrite[23183]: fatal: mysql:/etc/postfix/mysql-transport.cf(0,lock|fold_fix): table lookup problem

I have googled and have not really found a solution to this issue... can anyone assist please?

THanks

SImon
Reply | Threaded
Open this post in threaded view
|

Re: Illegal mix of collations error

Darren Pilgrim-7
Simon wrote:
> Jun  8 07:15:19 mail-in1 postfix/trivial-rewrite[23183]: warning: mysql
> query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
> (utf8_general_ci,COERCIBLE) for operation '='
> Jun  8 07:15:19 mail-in1 postfix/trivial-rewrite[23183]: fatal:
> mysql:/etc/postfix/mysql-transport.cf
> <http://mysql-transport.cf>(0,lock|fold_fix): table lookup problem
>
> I have googled and have not really found a solution to this issue... can
> anyone assist please?

This is usually due to comparing a string literal to a function return
or a table with collation set to something other than latin1_swedish_ci
(what it should be for email addresses).  Email addresses are always
latin1 case-insensitive.  This URL will give you some useful hints:

http://www.google.com/search?q=Illegal+mix+of+collations+site%3Amysql.com

Short answer: change the collation on your table or force collation on
your string literal(s).
Reply | Threaded
Open this post in threaded view
|

Re: Illegal mix of collations error

Simon Buchanan


On Mon, Jun 8, 2009 at 10:09 AM, Darren Pilgrim <[hidden email]> wrote:
Simon wrote:
Jun  8 07:15:19 mail-in1 postfix/trivial-rewrite[23183]: warning: mysql query failed: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='
Jun  8 07:15:19 mail-in1 postfix/trivial-rewrite[23183]: fatal: mysql:/etc/postfix/mysql-transport.cf <http://mysql-transport.cf>(0,lock|fold_fix): table lookup problem


I have googled and have not really found a solution to this issue... can anyone assist please?

This is usually due to comparing a string literal to a function return or a table with collation set to something other than latin1_swedish_ci (what it should be for email addresses).  Email addresses are always latin1 case-insensitive.  This URL will give you some useful hints:

http://www.google.com/search?q=Illegal+mix+of+collations+site%3Amysql.com

Short answer: change the collation on your table or force collation on your string literal(s).

Thanks for the reply on this. I have now changed the collation of the tables to latin1_swedish_ci, but am still getting these errors. Dont quite understand what todo from here? Can anyone assist further please?

Thanks!!

Simon

Reply | Threaded
Open this post in threaded view
|

Re: Illegal mix of collations error

Blake Hudson

> Thanks for the reply on this. I have now changed the collation of the
> tables to latin1_swedish_ci, but am still getting these errors. Dont
> quite understand what todo from here? Can anyone assist further please?
>
> Thanks!!
>
> Simon
>
The issue is that you are comparing two strings, one that uses one
character set and another which uses a different character set. Mysql
retains (and includes) character set information during string
comparisons - if you were to compare strings with different character
sets, you'd never have a match. It sounds like you have made some
changes to your SQL server recently (or perhaps the changes were made a
while ago and SQL was just recently restarted).

If the table definition defines 'domain' as atin1_swedish_ci, then the
utf8_general_ci is likely coming from the connection between postfix and
MySQL. You might check your my.cnf or startup command for something
similar to 'default-character-set=utf8'. If you find this, I would
suggest reverting to the previous setting (likely commented out or
missing altogether).

--Blake


Reply | Threaded
Open this post in threaded view
|

Re: Illegal mix of collations error

Simon Buchanan


On Tue, Jun 16, 2009 at 9:50 AM, Blake Hudson <[hidden email]> wrote:

Thanks for the reply on this. I have now changed the collation of the tables to latin1_swedish_ci, but am still getting these errors. Dont quite understand what todo from here? Can anyone assist further please?

Thanks!!

Simon

The issue is that you are comparing two strings, one that uses one character set and another which uses a different character set. Mysql retains (and includes) character set information during string comparisons - if you were to compare strings with different character sets, you'd never have a match. It sounds like you have made some changes to your SQL server recently (or perhaps the changes were made a while ago and SQL was just recently restarted).

If the table definition defines 'domain' as atin1_swedish_ci, then the utf8_general_ci is likely coming from the connection between postfix and MySQL. You might check your my.cnf or startup command for something similar to 'default-character-set=utf8'. If you find this, I would suggest reverting to the previous setting (likely commented out or missing altogether).

--Blake



Hi Blake, thanks for the reply.

The Mysql server that the postfix configuration is on indeed does have default-character-set=utf8 set and this was changed not so long ago.. but we need to have it as such for reasons. I have moved the config to another mysql server (without default-character-set=utf8) for the mean time, but is there a way we can still have default-character-set=utf8 on the mysql server and have the postfix config on it?

Thanks

Simon
Reply | Threaded
Open this post in threaded view
|

Re: Illegal mix of collations error

Brian Evans - Postfix List
Simon wrote:
> Hi Blake, thanks for the reply.
>
> The Mysql server that the postfix configuration is on indeed does have
> default-character-set=utf8 set and this was changed not so long ago..
> but we need to have it as such for reasons. I have moved the config to
> another mysql server (without default-character-set=utf8) for the mean
> time, but is there a way we can still have default-character-set=utf8
> on the mysql server and have the postfix config on it?
If you convert the map to the Postfix 2.2+ syntax, you can force the
character type using the query itself.

user = mail-in1
password = ******
dbname = postfix
hosts = 210.48.XX.XXX
query = SELECT transport from transport where domain = _utf8'%s'

Brian

Reply | Threaded
Open this post in threaded view
|

Re: Illegal mix of collations error

Victor Duchovni
On Wed, Jun 17, 2009 at 09:22:31AM -0400, Brian Evans - Postfix List wrote:

> Simon wrote:
> > Hi Blake, thanks for the reply.
> >
> > The Mysql server that the postfix configuration is on indeed does have
> > default-character-set=utf8 set and this was changed not so long ago..
> > but we need to have it as such for reasons. I have moved the config to
> > another mysql server (without default-character-set=utf8) for the mean
> > time, but is there a way we can still have default-character-set=utf8
> > on the mysql server and have the postfix config on it?
> If you convert the map to the Postfix 2.2+ syntax, you can force the
> character type using the query itself.
>
> user = mail-in1
> password = ******
> dbname = postfix
> hosts = 210.48.XX.XXX
> query = SELECT transport from transport where domain = _utf8'%s'

This is wrong, the data that Postfix replaces '%s' with is raw
binary data. It is NOT UTF-8, and saying that it is, is asking
for all kinds of trouble. SMTP is an ASCII protocol, there is
no unambiguous meaning to 8-bit data in envelopes and headers.

Use an encoding where all 8-bit patterns are valid, and ASCII
is mapped verbatim into the lower 7-bits. Any ISO-8859-X will
do. Multi-byte encodings (like UTF-8) are right out.

--
        Viktor.

Disclaimer: off-list followups get on-list replies or get ignored.
Please do not ignore the "Reply-To" header.

To unsubscribe from the postfix-users list, visit
http://www.postfix.org/lists.html or click the link below:
<mailto:[hidden email]?body=unsubscribe%20postfix-users>

If my response solves your problem, the best way to thank me is to not
send an "it worked, thanks" follow-up. If you must respond, please put
"It worked, thanks" in the "Subject" so I can delete these quickly.