[LRUG] MySQL/Rails Collation

[Simon Coffey]

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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.lrug.org/pipermail/chat-lrug.org/attachments/20120123/bb2e1898/attachment-0003.html>