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.