Fatal error: Uncaught exception 'MySQLiQuery_Exception' with message 'Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) - with PHP5

Question

Hi, I've got this error:

Fatal error: Uncaught exception 'MySQLiQuery_Exception' with message 'Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': select id from 'addresses' where 'shiptozip'='13000' and 'shiptostreet'='Františka Křížka'

As you can see, I'm trying to get an ID from the table addresses.

mysql> show variables like 'character%';

+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

Where the table 'addresses' also has utf8_general_ci and utf8. I guess it's got something to do with the query Františka Křížka, as it's ok with other queries. The server_collation used to be latin_swedish_ci but I think I've managed to change that all now (as you can see from the above tables). Thanks in advance.

Answer 1

Can you append to your question the collation for columns "shiptoaddress" and "shiptozip" of:

SHOW FULL COLUMNS FROM addresses;

From the evidence you've presented, what's likely to be happening is that the shiptoaddress column still has latin1 encoding. When you set the encoding/collation for a table you're setting a default. This default can be overridden for individual columns.

If the query works for "where address = 'Frank'" but doesn't work for "where address = 'Františka'" that's because "Františka" isn't convertible to latin1.