I thought that migrations where defining the collation when creating tables. So in my.cnf we should make sure that we have:<div><br></div><div><span class="Apple-style-span" style="border-collapse:collapse;font-family:Arial,'Liberation Sans','DejaVu Sans',sans-serif;font-size:14px;line-height:18px"><pre class="lang-sql prettyprint" style="margin-top:0px;margin-right:0px;margin-bottom:10px;margin-left:0px;padding-top:5px;padding-right:5px;padding-bottom:5px;padding-left:5px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:rgb(238,238,238);font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif;overflow-x:auto;overflow-y:auto;width:auto;max-height:600px;background-repeat:initial initial">
<code style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:rgb(238,238,238);font-family:Consolas,Menlo,Monaco,'Lucida Console','Liberation Mono','DejaVu Sans Mono','Bitstream Vera Sans Mono','Courier New',monospace,serif;background-repeat:initial initial"><span class="pln" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:transparent;color:rgb(0,0,0);background-repeat:initial initial">character-set-server</span><span class="pun" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:transparent;color:rgb(0,0,0);background-repeat:initial initial">=</span><span class="pln" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:transparent;color:rgb(0,0,0);background-repeat:initial initial">utf8<br>
collation-server</span><span class="pun" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:transparent;color:rgb(0,0,0);background-repeat:initial initial">=</span><span class="pln" style="margin-top:0px;margin-right:0px;margin-bottom:0px;margin-left:0px;padding-top:0px;padding-right:0px;padding-bottom:0px;padding-left:0px;border-top-width:0px;border-right-width:0px;border-bottom-width:0px;border-left-width:0px;border-style:initial;border-color:initial;font-size:14px;vertical-align:baseline;background-image:initial;background-color:transparent;color:rgb(0,0,0);background-repeat:initial initial">utf8_general_ci</span></code></pre>
</span><br><div class="gmail_quote">On 23 January 2012 18:40, Simon Coffey <span dir="ltr"><<a href="mailto:simon@tribesports.com">simon@tribesports.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div>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.</div>
<div><br></div><div>Why anyone thought latin1 with swedish collation was a good default setting is beyond me, but hey ho.</div><div><br></div><div>Cheers,</div><div><div>Simon</div></div><div class="HOEnZb"><div class="h5">
<br><div class="gmail_quote">On 23 January 2012 17:21, mudge <span dir="ltr"><<a href="mailto:mudge@mudge.name" target="_blank">mudge@mudge.name</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Having gone through much pain with incorrect encodings with Rails and<br>
MySQL in the past, I highly recommend you read Aleksandar's link as it<br>
highlights an important issue: if you have been storing UTF-8 data in<br>
a non-UTF-8 table then when changing encoding, MySQL will attempt to<br>
translate your data (which, in this case, would be incorrect as it is<br>
already UTF-8) which can result in corruption.<br>
<div><div><br>
On Mon, Jan 23, 2012 at 5:05 PM, Aleksandar Simic <<a href="mailto:asimic@gmail.com" target="_blank">asimic@gmail.com</a>> wrote:<br>
> On Mon, Jan 23, 2012 at 4:56 PM, Andrew Stewart<br>
> <<a href="mailto:boss@airbladesoftware.com" target="_blank">boss@airbladesoftware.com</a>> wrote:<br>
>> Hola El Rug,<br>
>><br>
>> 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.<br>
>><br>
>> I have just started to see collation errors both at the Rails level and in the mysql console. For example:<br>
>><br>
>> mysql> select * from samples where code = 'abcd‐01‐s10‐036';<br>
>> ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='<br>
>><br>
>> mysql> show create table samples;<br>
>> [snip] ENGINE=InnoDB AUTO_INCREMENT=54307 DEFAULT CHARSET=latin1<br>
>><br>
>> In the Rails console:<br>
>><br>
>> >> ActiveRecord::Base.connection.collation<br>
>> "latin1_swedish_ci"<br>
>><br>
>> 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)?<br>
><br>
> Would this help:<br>
><br>
> <a href="http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion" target="_blank">http://alexking.org/blog/2008/03/06/mysql-latin1-utf8-conversion</a><br>
> _______________________________________________<br>
> Chat mailing list<br>
> <a href="mailto:Chat@lists.lrug.org" target="_blank">Chat@lists.lrug.org</a><br>
> <a href="http://lists.lrug.org/listinfo.cgi/chat-lrug.org" target="_blank">http://lists.lrug.org/listinfo.cgi/chat-lrug.org</a><br>
_______________________________________________<br>
Chat mailing list<br>
<a href="mailto:Chat@lists.lrug.org" target="_blank">Chat@lists.lrug.org</a><br>
<a href="http://lists.lrug.org/listinfo.cgi/chat-lrug.org" target="_blank">http://lists.lrug.org/listinfo.cgi/chat-lrug.org</a><br>
</div></div></blockquote></div><br><br clear="all"><div><br></div></div></div><span class="HOEnZb"><font color="#888888">-- <br>Simon Coffey<br>Developer, Tribesports<br><a href="mailto:simon@tribesports.com" target="_blank">simon@tribesports.com</a> | 07960 004 857<br>
</font></span><br>_______________________________________________<br>
Chat mailing list<br>
<a href="mailto:Chat@lists.lrug.org">Chat@lists.lrug.org</a><br>
<a href="http://lists.lrug.org/listinfo.cgi/chat-lrug.org" target="_blank">http://lists.lrug.org/listinfo.cgi/chat-lrug.org</a><br>
<br></blockquote></div><br><br clear="all"><div><br></div>-- <br>Riccardo Tacconi<br>Ruby on Rails and PHP development - System Administration<br>VIRTUELOGIC LIMITED<br><br><a href="http://github.com/rtacconi" target="_blank">http://github.com/rtacconi</a><br>
<a href="http://riccardotacconi.blogspot.com" target="_blank">http://riccardotacconi.blogspot.com</a><br><a href="http://twitter.com/rtacconi" target="_blank">http://twitter.com/rtacconi</a><br>
</div>