MySQL, collations and Rails – or server, database, connection – that is not all

Last few weeks were somewhat crazy time. But I’m seeing finish line not so far away and I hope to find more time for blogging in upcoming weeks. At least I have few things to share, which could be interesting for other Rails developers.

So let’s start with MySQL and it’s collations. I have wrote about them few months ago. Few days ago, I had finally installed Linux on my notebook (last two months I was running Linux from USB drive). And it seems that there were some minor changes in my environment. I was working on CARtoteka, and when I was running migrations they were stopped in half with error like :

Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '=': 
SELECT count(DISTINCT  companies`.id) AS count_all FROM `companies`  
LEFT OUTER JOIN `addresses` ON  addresses.company_id = companies.id 
WHERE (city = 'Pozna?' and moderated)

What the heck? I have doublechecked I have set connection collation and other – utf_general_ci as I could expect.

After some digging I have found what was a cause.


MySQL support different collations for each table and even for each column in tables (of course only for text types). But this can lead to problems with defaults.

When Rails does create new tables and sets character set as UTF8, it does not select default collation for table. That means MySQL will use connection collation, and if it was not set by You then huge chance that it is again unforgettable Swedish Chef or latin1_swedish_ci in other words.

What to do?

If You can fall back to previous versions You can create tables with new defaults.

To make sure You are not depended on connection settings You just provide collation information when creating table. Just add :options to create_table specifying UTF8 as character set and utf_general_ci as default collation for tables :

create_table :tags, 
   :options => "DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci" do |t|
      t.column :name, :string
end

If it is not possible, then You have to use SQL and ALTER TABLE.

Join the Conversation

3 Comments

  1. This post just helped me fix a problem with my app that’s been driving me crazy – thanks very much!

    :)

  2. Hi,

    I came across the same problem as you but I found a solution that this additional options can be avoided. As long as the database’s collation is set to ‘utf8_general_ci’, all the tables created under it will use the same collation by default.

  3. I had this error message, but there was a different reason:

    A form on a static html page was encoded in iso instead of in utf8. Everything works fine, then one day a user put in an umlaut. The umlaut got encoded in iso with an int-value grater 127, and together with the next char it ended as an char outside the unicode range.
    The occurence of these errors also depends on the browser and the browser settings…

    To get rid of it, check your string parameters with the method “is_utf8?”

Leave a comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.