Unduplicatable "Illegal mix of collations" error

Question

I was going through the error log of my Rails application and found that someone had run into the following error:

"Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': SELECT * FROM `tags` WHERE (name = LOWER('?')) LIMIT 1"

I understand the reasoning for this error, however I am running into trouble trying to fix it because I cannot duplicate it. My database connection uses a utf8 connection, and the tags table's collation is latin1_swedish_ci, but no matter what I try to do I cannot duplicate the error.

Should I just change tags to use utf8_general_ci collation and hope this fixes the problem? Or does anyone have any other ideas?

Answer 1

I ran into this same issue last week. In my case it ended up being that I had one field listed as latin1_swedish_ci in a table that was otherwise UTF-8. If you are mainly running a UTF-8 database and we are talking about string data then I would just go ahead and change collation and save yourself some trouble. Right now you may not be using it in a way that errors, but if you forget and left join on that data for example then you may find trouble in the future.