[LRUG] MySQL/Rails Collation

[Andrew Stewart]

Hola El Rug,

I have a Rails app which started life as Rails 2.x and is now Rails 3.0.y.  It sits on top of a MySQL database.

I have just started to see collation errors both at the Rails level and in the mysql console.  For example:

    mysql> select * from samples where code = 'abcd‐01‐s10‐036';
    ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

    mysql> show create table samples;
    [snip] ENGINE=InnoDB AUTO_INCREMENT=54307 DEFAULT CHARSET=latin1

In the Rails console:

    >> ActiveRecord::Base.connection.collation
    "latin1_swedish_ci"

Now I thought Rails ensured everything from top to toe was UTF-8; apparently not in this case.  Anyway, how do I best fix this (while staying with MySQL)?

Thanks in advance.

Yours,
Andy Stewart

-------
http://airbladesoftware.com