[LRUG] MySQL/Rails Collation

[mudge]

Having gone through much pain with incorrect encodings with Rails and
MySQL in the past, I highly recommend you read Aleksandar's link as it
highlights an important issue: if you have been storing UTF-8 data in
a non-UTF-8 table then when changing encoding, MySQL will attempt to
translate your data (which, in this case, would be incorrect as it is
already UTF-8) which can result in corruption.

On Mon, Jan 23, 2012 at 5:05 PM, Aleksandar Simic <asimic at gmail.com> wrote:
> On Mon, Jan 23, 2012 at 4:56 PM, Andrew Stewart
> <boss at airbladesoftware.com> wrote:
>> 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)?
>
> Would this help:
>
> http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org