Mysql Fix Illegal mix of collations

If you get an error like this while using MYSQL

Mysql2::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation

this is probably due to using or mixing different collations in a select: in my case I was joining columns with different collations. How to fix that:

I set prudent defaults to my database so that it wont happens again:
mysql>>
ALTER DATABASE `database_name` CHARACTER SET utf8 COLLATE utf8_unicode_ci;
ALTER DATABASE `database_name` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

or in a rails migration
execute("ALTER DATABASE `#{ActiveRecord::Base.connection.current_database}` CHARACTER SET utf8 COLLATE utf8_unicode_ci;")
execute("ALTER DATABASE `#{ActiveRecord::Base.connection.current_database}` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;")
Then fix the tables you need to. If you dont know what are all problematic tables, ask the db:
SELECT table_schema, table_name, column_name, character_set_name, collation_name  FROM information_schema.columns  WHERE table_schema = 'database_name' AND collation_name <> 'utf8_unicode_ci' ORDER BY table_schema, table_name,ordinal_position;
    or

SELECT table_name FROM information_schema.columns  WHERE table_schema = 'databse_name' AND collation_name <> 'utf8_unicode_ci' GROUP BY table_name;
then for each table

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;



You can put all of this stuff in a single Rails migration.

Profit!