0

NOTE: The Generic SQL answer is Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '='

This question is specific to rails/active-record, when you have:

ActionView::Template::Error: Mysql2::Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT)

The accepted answer says to COLLATE but I was hoping for some examples specific to rails.

Community
  • 1
  • 1
Blair Anderson
  • 19,463
  • 8
  • 77
  • 114

1 Answers1

0

I solved my own problem and wanted to document

Before:

Membership.joins(person: :account).where(foo: :bar)

it was showing I had some collation mismatches and needed to update.

After:

Membership
.joins('INNER JOIN `people` ON `people`.`id` = `memberships`.`person_id`')
.joins('INNER JOIN `accounts` ON `accounts`.`person_id` COLLATE utf8_general_ci = `people`.`id` COLLATE utf8_general_ci')

More specifically you add COLLATE <UTF8_BLAH> after your column names.

instead of:

joins accounts on person_id = people.id

use:

joins accounts on person_id COLLATE utf8_general_ci = people.id COLLATE utf8_general_ci

you can also solve this problem by fixing the collation for all your tables and your entire database.

Blair Anderson
  • 19,463
  • 8
  • 77
  • 114