rails mysql encoding issue/question - Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

Question

Rails 2.3.5 and Ruby 1.8.7 and Mysql 5.1.53

Im loading in a csv file, it has a field that TM symbol in it (trademark)

Tart Deco™ - looks like this

I am trying to do an active record find:

Influencer.find(:first,:conditions => ["author_name = ? and url_discovered = ?",author_name,site_profile_url])

Mysql::Error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=': SELECT * FROM influencers WHERE (author_name = 'Tart Deco?' and url_discovered = 'http://www.joelnylund.com') LIMIT 1

In ruby debugger the String shows up as:

p author_name "Tart Deco\231"

My table is encoded "utf8_general_ci"

So what should I do? I dont really care to much if I store the TM, it would be nice, mainly I just dont want it to break...

Answer 1

Make sure your table sports the uft8 character set:

alter table `influencers` convert to character set utf8 collate utf8_general_ci;

Note: your collation (utf8_general_ci) is not your encoding (character set) - a common misunderstanding with MySQL.