Troubleshooting MySQL Illegal Mix of Collation Error between different PCs

Question

I am running code for a C# application that makes a call to a database to report on data. What's interesting is that the report works fine on my own PC, but when used at a client I got:

Illegal Mix of Collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) in concat

So far, I have checked the tables (ENGINE=InnoDB DEFAULT CHARSET=latin1;), which are the same between our two databases. Additionally, I can backup the client database, restore it on my own PC and run the code and it works fine.

I suspect that it would be a MySQL related error, as they run MySQL Server 5.0, whereas I have MySQL Server 5.1 on my own computer. Are there any obvious discrepancies between these two versions or other obvious places to look that would cause the error on another PC but not my own?

Here is the offending code, which I was able to fix:

Original:

"Case when s.type in (" + IDList + ") then concat(s.id,(case when coalesce(p.ID, ps.ID) in ('ABC') then '.M' else '.U' end)) else case when s.id is NULL OR s.other_id='' then s.symbol else s.id end end as 'BLAH' "

Fix apported:

"Case when s.type in (" + IDList + ") then concat(CAST(s.id as CHAR),(case when coalesce(p.ID, ps.ID) in ('ABC') then '.M' else '.U' end)) else case when s.id is NULL OR s.other_id='' then s.symbol else s.id end end as 'BLAH' "

Would this be caused by the default charset being used by MySQL on their PC? The my.ini files do have an additional difference, which is that mine specifies:

'character-set-server=latin1'

whereas there's only contains:

'default-character-set=latin1'

Edited to include that the table columns are also specified with collation latin1_swedish_ci in both databases.

Using the link in the first comment I used "show table status" and "show variables like "collation_database".

Show table status confirmed that each table's Collation was latin1_swedish_ci on both mine and the other system.

Show variables like collation_database confirmed that both my system and the other system have latin1_swedish_ci as the value

possible duplicate of [How to solve - Illegal mix of collations in mysql?](http://stackoverflow.com/questions/3029321/how-to-solve-illegal-mix-of-collations-in-mysql) — clami219, Jul 15 '14 at 14:36
I would like to think this is slightly different: I am trying to figure out why the original DOES work unedited on my setup but had to be changed at another location; especially considering that I have dumped and then restored the offending database at my location and had it work fine from here — user2464083, Jul 15 '14 at 15:13

score 0 · Accepted Answer · edited Jul 28 '14 at 00:12

As for my research the default collation and charset has been changed from MySQL-server 5.0 to 5.1.

Please try to add the following lines to your my.cnf and restart your server:

character_set_client=utf8
character_set_server=utf8
collation_server=utf8_general_ci

And check if the problem still exists. My problem was, that the client could request strings with own charset or collation until there is no server defined charset for the client.

Please be aware that the parameter names have been slightly changed during the last MySQL versions. I'm using MySQL 5.5 and the above parameters work fine for me and I did not have any illegal mix of collations errors since then.

Troubleshooting MySQL Illegal Mix of Collation Error between different PCs

1 Answers1