[LRUG] MySQL/Rails Collation

[Riccardo Tacconi]

I thought that migrations where defining the collation when creating
tables. So in my.cnf we should make sure that we have:

character-set-server=utf8
collation-server=utf8_general_ci


On 23 January 2012 18:40, Simon Coffey <simon at tribesports.com> wrote:

> Just as a follow-up to Aleksandar and mudge's answers, don't forget to
> alter the database's default charset and collation to utf8 and
> utf8_unicode_ci, or any tables created in future will be
> latin1/latin1_swedish_ci, and you'll have the same problem all over again.
>
> Why anyone thought latin1 with swedish collation was a good default
> setting is beyond me, but hey ho.
>
> Cheers,
> Simon
>
> On 23 January 2012 17:21, mudge <mudge at mudge.name> wrote:
>
>> 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
>> _______________________________________________
>> Chat mailing list
>> Chat at lists.lrug.org
>> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>>
>
>
>
> --
> Simon Coffey
> Developer, Tribesports
> simon at tribesports.com | 07960 004 857
>
> _______________________________________________
> Chat mailing list
> Chat at lists.lrug.org
> http://lists.lrug.org/listinfo.cgi/chat-lrug.org
>
>


-- 
Riccardo Tacconi
Ruby on Rails and PHP development - System Administration
VIRTUELOGIC LIMITED

http://github.com/rtacconi
http://riccardotacconi.blogspot.com
http://twitter.com/rtacconi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20120123/381c99b8/attachment-0003.html>