Rails Tests, MySQL Tests and Collations

Today I was trying to checkout Rails code to fix a bug and contribute it to Rails using git. I git-cloned the Rails code, and went on to rake test it, I created the databases activerecord_unittest and activerecord_unittest2, granted permissions and went on to run the tests.

One of the ActiveRecord tests failed, but this was expected, ran rake a second time (this time not expecting failures), but I was greeted with the following error:

  1) Error:
test_validate_case_insensitive_uniqueness(ValidationsTest):
ActiveRecord::StatementInvalid: Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_c
i,COERCIBLE) for operation '=': SELECT `title` FROM `topics`     WHERE (LOWER(`topics`.title) = '? ???? ??????????!')

This was in the ActiveRecord tests also, after a little check I noticed that actually, the databases were created with latin1_swedish_ci collation.

This is easy to correct, just go into your MySQL client, and do the following:

mysql> alter database activerecord_unittest character set utf8;
Query OK, 1 row affected (0.01 sec)

mysql> alter database activerecord_unittest2 character set utf8;
Query OK, 1 row affected (0.00 sec)

mysql> use activerecord_unittest;
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 VARIABLES LIKE 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

mysql> use activerecord_unittest2;
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 VARIABLES LIKE 'character_set_database';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)

Actually the last two commands are just to verify the change, you can just use the first two ones:

alter database activerecord_unittest character set utf8;
alter database activerecord_unittest2 character set utf8;

Et Voila! All tests pass now. As most of my posts, this is to keep as a self reminder, since I found no info on this error on the 'Net.

Thanks for reading, and please comment!

Gabriel Medina