"Illegal mix of collations" with PHP 4.3.10 and MySQL 4.1.7

I have noticed a very peculiar behavior when I upgraded my PHP
revision from 4.3.9 to 4.3.10 (I use Debian Linux). Suddenly,
the PHP MySQL client stopped accepting the default encoding / collation
when connecting to the server. When issuing any query containing text
field comparisons, I get the following error message:

Illegal mix of collations (latin2_general_ci,IMPLICIT) and
(latin1_swedish_ci,COERCIBLE) for operation '='

The appropriate values when using mysqladmin to check the variables
are as follows:

| character_set_client | latin2
| character_set_connection | latin2
| character_set_database | latin2
| character_set_results | latin2
| character_set_server | latin2
| collation_connection | latin2_general_ci
| collation_database | latin2_general_ci
| collation_server | latin2_general_ci

All the tables are converted to the latin2 charset, all the relevant
columns have "latin2_general_ci" set as the default collation.

The following entry in the my.cnf file also doesn't help:

[client]
character_set = latin2

The webserver is set to use ISO-8859-2 (latin2) as the base charset:
AddDefaultCharset iso-8859-2

The appropriate php packages are version 4.3.10-1 from dotdeb.org.
The libmysqlclient package is version 4.1.7-1 from the same source.
Same goes for the mysql-server package.

This problem has appeared only when migrating from PHP 4.3.9 to
4.3.10, before that everything worked fine. I have partially remedied
the problem by putting the 3 queries:

set names latin2;
set character set latin2;
set collation_connection='latin2_general_ci';

after the initializing code of every MySQL connection I make. However,
since I host multiple sites on the server and possibly have multiple
PHP applications that use SQL client connections, I cannot use this as
a final solution. Downgrading is also not a good option because I
cannot find the old .deb packages anywhere (besides, it might be
caused by some bizarre change done to the configuration file by the
4.3.10 preinstall script, but I've done a quite thorough search
through the config files and found naught). Do you perhaps know how
would I be able to 'force' the PHP MySQL client to accept the latin2
encoding as default?

--
Greetings,
Piotr Wilkin
pwl@druid.com.pl PGP key: http://pwl.yz.pl/pwlpgp.txt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: "Illegal mix of collations" with PHP 4.3.10 and MySQL 4.1.7

Hello Piotr,

> -----Original Message-----
> From: Piotr Wilkin [mailto:pwl@druid.com.pl]
> Sent: Tuesday, December 28, 2004 5:46 AM
> To: php-db@lists.php.net
> Subject: [PHP-DB] 'Illegal mix of collations' with PHP 4.3.10 and MySQL
> 4.1.7
>
> I have noticed a very peculiar behavior when I upgraded my PHP
> revision from 4.3.9 to 4.3.10 (I use Debian Linux). Suddenly,
> the PHP MySQL client stopped accepting the default encoding / collation
> when connecting to the server. When issuing any query containing text
> field comparisons, I get the following error message:
>
> Illegal mix of collations (latin2_general_ci,IMPLICIT) and
> (latin1_swedish_ci,COERCIBLE) for operation '='

I've got the same problem with PHP 5.0.2 and MySQL 4.1.8. I asked this
question many times on different mailing lists but still haven't got
satisfactory answer...

>
> The appropriate values when using mysqladmin to check the variables
> are as follows:
>
> | character_set_client | latin2
> | character_set_connection | latin2
> | character_set_database | latin2
> | character_set_results | latin2
> | character_set_server | latin2
> | collation_connection | latin2_general_ci
> | collation_database | latin2_general_ci
> | collation_server | latin2_general_ci
>
> All the tables are converted to the latin2 charset, all the relevant
> columns have "latin2_general_ci" set as the default collation.
>
> The following entry in the my.cnf file also doesn't help:
>
> [client]
> character_set = latin2

AFAIK have to be

default-character-set=latin2

at least this works for 'mysql' utility.

>
> The webserver is set to use ISO-8859-2 (latin2) as the base charset:
> AddDefaultCharset iso-8859-2

Will have no effect.

>
> The appropriate php packages are version 4.3.10-1 from dotdeb.org.
> The libmysqlclient package is version 4.1.7-1 from the same source.
> Same goes for the mysql-server package.
>
> This problem has appeared only when migrating from PHP 4.3.9 to
> 4.3.10, before that everything worked fine. I have partially remedied
> the problem by putting the 3 queries:
>
> set names latin2;
> set character set latin2;
> set collation_connection='latin2_general_ci';
>

I had to run such kind of queries to solve my problem too. But I use
PEAR::DB_DataObject, more precisely, my own classes derived from
DB_DataObject so I just added one line to my own class' constructor to
execute this query before all. It works fine to me.

But really I would like to know the way of setting default charset in MySQL
of course (if it does exist).

> after the initializing code of every MySQL connection I make. However,
> since I host multiple sites on the server and possibly have multiple
> PHP applications that use SQL client connections, I cannot use this as
> a final solution. Downgrading is also not a good option because I
> cannot find the old .deb packages anywhere (besides, it might be
> caused by some bizarre change done to the configuration file by the
> 4.3.10 preinstall script, but I've done a quite thorough search
> through the config files and found naught). Do you perhaps know how
> would I be able to 'force' the PHP MySQL client to accept the latin2
> encoding as default?

The thing is that source code of libmysql.dll/libmysqli.dll DOESN'T contain
code for setting connection charset (as I was said on the MySQL list). So
what we want seems to be impossible.

Are there any comments from libmysql.dll/libmysqli.dll developers?

>
> --
> Greetings,
> Piotr Wilkin
> pwl@druid.com.pl PGP key: http://pwl.yz.pl/pwlpgp.txt
>
> --
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php

Best regards, Denis Gerasimov
Outsourcing Services Manager,
VEKOS, Ltd.
www.vekos.ru

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re[2]: "Illegal mix of collations" with PHP 4.3.10 and MySQL 4.1.7

[cut]
DG> I've got the same problem with PHP 5.0.2 and MySQL 4.1.8. I asked this
DG> question many times on different mailing lists but still haven't got
DG> satisfactory answer...

That's the problem, I've reviewed different mailing lists as well but
saw no reply... all problems that were presented regarding collation /
charset were just simple "I converted to MySQL 4.1 and now my queries
won't work" problems :/

[cut]
DG> AFAIK have to be
DG> default-character-set=latin2
DG> at least this works for 'mysql' utility.

This generates an error for my system (unknown configuration option
"default-character-set"). This option works for the mysqld entry (the
server), but not for the clients. I read this solution (character_set)
in some mailing list reply. What's funny, when I did it, it SEEMED to
work - PHP started crashing complaining about the lack of the
latin2/Index.xml file. When I made a link to the file and added an
additional entry for charset-default-dir, it stopped complaining...
but didn't exactly start working :P

[cut]
DG> Will have no effect.

So I've noticed :)

DG> The thing is that source code of libmysql.dll/libmysqli.dll DOESN'T contain
DG> code for setting connection charset (as I was said on the MySQL list). So
DG> what we want seems to be impossible.

Well, the problem is, it DID work. I mean, I know a solution would be
to recompile libmysql.dll and add the default charset in it, but what
I mean is not using a default, arbitrarily chosen character set, but
chosing the character set implied by the locale or server default
character set (or database default character set).

DG> Are there any comments from libmysql.dll/libmysqli.dll developers?

I have not bothered them yet, I still don't know if this is a bug or
just a configuration problem on my side :(

--
Greetings,
Piotr Wilkin
pwl@druid.com.pl PGP key: http://pwl.yz.pl/pwlpgp.txt

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php