After upgrading from MySQL 5.0.17 to 5.0.18, stored procedures which used VARCHAR fields in WHERE clause does not work anymore. When I call such procedure I get error "ERROR 1267 (HY000): Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='". Only solution I have found for now is to mask MySQL 5.0.18 and downgrade back to 5.0.17. If that matters, data is stored in InnoDB tables. My make.conf: ------------- CFLAGS="-O2 -march=pentium4 -fomit-frame-pointer -pipe" CHOST="i686-pc-linux-gnu" CXXFLAGS="${CFLAGS}" MAKEOPTS="-j2" LINGUAS="lv" USE="gtk -gnome qt kde dvd alsa cdr dvdr apache2 mysql pam ssl xml xml2 berkdb innodb use cups foomaticdb ppds usb win32codecs mmx sse gd bash-completion unicode svg" FEATURES="distlocks parallel-fetch confcache fixpackages"
This may be related to upstream bug 16211. Anyway could you create a test case, an example follow: =============================== mysql> use test ; Database changed mysql> delimiter // mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50) -> NO SQL -> RETURN CONCAT('Hello, ',s,'!'); -> // Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> select charset(hello('world')); +-------------------------+ | charset(hello('world')) | +-------------------------+ | binary | +-------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE `t1` ( -> `f1` varchar(100) default NULL -> ) ENGINE=MyISAM DEFAULT CHARSET=utf8; Query OK, 0 rows affected (0.82 sec) mysql> insert into t1 values ("alpha"), ("Hello, world!"), ("beta"), ("gamma"); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM t1 -> WHERE t1.f1 = hello('world') ; +---------------+ | f1 | +---------------+ | Hello, world! | +---------------+ 1 row in set (0.00 sec) =============================== As you can see this case is working, the test case should be able to reproduce the error.
Please read http://blog.stuartherbert.com/gentoo.php/2006/01/11/fun_with_collations
(In reply to comment #2) > Please read > http://blog.stuartherbert.com/gentoo.php/2006/01/11/fun_with_collations > I think this is a different bug, does --[skip-]character-set-client-handshake option help in your case ? http://dev.mysql.com/doc/refman/5.0/en/mysqld-safe.html http://bugs.mysql.com/bug.php?id=9948
fixed in 5.0.19 ?
