"Illegal mix of collations" error from MySql while running rails test suite

Question

I've recently dusted off an old Ruby on Rails project of mine. In the past, I've never had any problems getting all the tests to pass, but now there is one test that gives me the following error:

ActiveRecord::StatementInvalid: Mysql::Error: #HY000Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': SELECT * FROM cards WHERE (cards.l1_description = '是' AND cards.l2_word = '')

So I go to my test db and ask:

mysql> use flashcard_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show full columns from cards;
+----------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| Field          | Type         | Collation         | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
| id             | int(11)      | NULL              | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| l2_word        | varchar(255) | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_description | text         | latin1_swedish_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_id          | int(11)      | NULL              | YES  |     | NULL    |                | select,insert,update,references |         |
| l2_id          | int(11)      | NULL              | YES  |     | NULL    |                | select,insert,update,references |         |
+----------------+--------------+-------------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.01 sec)

And as you can see, the collation is latin1_swedish_ci, and presumably if it were "utf8_general_ci", my problems would be solved. Thankfully, my development database is already okay, so I go and

rake db:test:clone_structure

and back to MySql and check again in the test db

mysql> show full columns from cards;
+----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field          | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id             | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         |
| l2_word        | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_description | text         | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         |
| l1_id          | int(11)      | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
| l2_id          | int(11)      | NULL            | YES  |     | NULL    |                | select,insert,update,references |         |
+----------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
5 rows in set (0.00 sec)

Ah, so now everything is looking good, so once again I

rake test

But I get the same problem all over again, and when I check my test db, I find that the collation column has been reset to latin1_swedish_ci.

I do not understand very well how rake test works, but my working hypothesis is that it recreates the DB using schema.rb. Now, in one of my migrations, I've got

 class CreateCards < ActiveRecord::Migration
   def self.up
     create_table :cards, :options => "DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci"  do |t|
       t.column :english_word, :string
       t.column :chinese_description, :text
     end
   end

   def self.down
     drop_table :cards
   end
 end

And this apparently has taken care of the collate problem there. (I've got another migration which renames english_word and chinese_description to l2_word and l1_description, respectively.) But this information has not made it into schema.rb. And somehow, apparently, MySql has decided to assume that I want latin1_swedish_ci.

So, to summarize, what I think I need to do is somehow edit something so that I'll be using the utf8_general_ci collation, and then my problems will go away (right?). But I cannot figure out how to make the code that gets run when you "rake test" do this. Can anybody help?

For what it's worth, both the test and development databases were created as

create database flashcard_test default character set utf8 default collate utf8_general_ci;

and

create database flashcard_development default character set utf8 default collate utf8_general_ci;

And my database.yml has

development:
  adapter: mysql
  database: flashcard_development
  username: root
  password: 
  encoding: utf8

test:
  adapter: mysql
  database: flashcard_test
  username: root
  password: 
  encoding: utf8
  collation: utf8_general_ci

http://nhw.pl/wp/2008/09/16/mysql-collate-setting-in-rails-application seems to suggest that this problem has something to do with the connection between RoR and MySql, but I haven't had any luck with the suggestions there.

Answer 1

Adding the collation: utf8_general_ci to your database.yml file like you have done should do the trick. Try recreating the test database using "rake RAILS_ENV=test db:migrate:reset db:fixtures load" - warning this will clear all data you have there beyond the fixtures.

That worked for me. To verify see the collation on the database, tables, and columns you can execute the following:

-- Database Collations:
SELECT schema_name,default_character_set_name,default_collation_name 
FROM information_schema.SCHEMATA 
WHERE schema_name not IN ('mysql');

-- Table Collations:
SELECT T.table_schema, T.table_name, T.TABLE_COLLATION, CCSA.CHARACTER_SET_NAME 
FROM information_schema.`TABLES` T,
 information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
AND T.table_schema not IN ('mysql');

-- Column Collations:
SELECT table_schema, table_name, column_name, collation_name, character_set_name
FROM information_schema.`COLUMNS` C
WHERE C.table_schema not IN ('mysql')
ORDER BY 1,2,4;

Everything in your test database should now have the collation specified in database.yml.