RE: [PHP-DB] '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