CODEWITHSUNDEEP
mysqlruby-on-railsrubyactiverecordcharacter-encoding
agent provocateur
agent provocateur

Reputation: 830

ActiveRecord - illegal mix of collations

I am having some issue with Ruby's ActiveRecord with the Redmine application.

Started PATCH "//issues/33135" for [ipaddress] at 2015-06-02 17:02:48 -0700
Processing by IssuesController#update as HTML
  Parameters: {"utf8"=>"✓", "authenticity_token"=>"[secret_token]", "issue"=>{"is_private"=>"0", "project_id"=>"949", "tracker_id"=>"4", "subject"=>"adgsasdg", "description"=>"asdggsad", "status_id"=>"1", "priority_id"=>"1", "assigned_to_id"=>"", "parent_issue_id"=>"", "start_date"=>"2015-06-02", "due_date"=>"2015-06-17", "done_ratio"=>"0", "custom_field_values"=>{"349"=>"", "32"=>"", "33"=>"", "67"=>"", "63"=>"", "221"=>"", "209"=>"0", "362"=>"", "234"=>"", "237"=>"", "235"=>[""], "435"=>""}, "notes"=>"", "private_notes"=>"0", "lock_version"=>"0"}, "was_default_status"=>"1", "time_entry"=>{"hours"=>"", "activity_id"=>"", "comments"=>"", "custom_field_values"=>{"388"=>"", "387"=>""}}, "last_journal_id"=>"", "commit"=>"Submit", "id"=>"33135"}
  Current user: [username] (id=3)
Mysql2::Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '>': INSERT INTO `journal_details` (`property`, `prop_key`, `old_value`, `value`, `journal_id`) VALUES ('cf', '32', '2015-06-25', '', 56006)
Completed 500 Internal Server Error in 811ms (ActiveRecord: 93.6ms)

ActiveRecord::StatementInvalid (Mysql2::Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,NUMERIC) for operation '>': INSERT INTO `journal_details` (`property`, `prop_key`, `old_value`, `value`, `journal_id`) VALUES ('cf', '32', '2015-06-25', '', 56006)):
  app/models/journal.rb:67:in `save'
  app/models/issue.rb:1566:in `create_journal'
  app/models/issue.rb:175:in `create_or_update'
  app/controllers/issues_controller.rb:479:in `block in save_issue_with_child_records'
  app/controllers/issues_controller.rb:467:in `save_issue_with_child_records'
  app/controllers/issues_controller.rb:180:in `update'

All my tables show collations of utf8 (some ...general_ci and some ...unicode_ci, but UTF8 nevertheless) and the error says I have some latin1...

I dont completely understand what this means...
Does it mean that it is trying to go from utf8 to latin1? or vice-versa? (does it matter?)

What I have Tried:

What is the issue? How can I solve it? Anyone else see something similar?

Im still trying to narrow it down to either the database or the application side of things.

Upvotes: 1

Views: 2215

Answers (1)

agent provocateur
agent provocateur

Reputation: 830

Summary

I forced a UTF8 collation and converted the data being compared to UTF8 for the actual comparison operation.

For some reason, a database trigger I was using (and have been using without issue for 4 months) is the source of the problem. I suspect a change in the database, but unsure.

In the trigger, there is a line (and only one line) that compares a new.value to a due_date with a ">" comparison operator.

I first tried adding COLLATE to the statement, but then my error changed to:

Mysql2::Error: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1': INSERT INTO `journal_details` (`property`, `prop_key`, `old_value`, `value`, `journal_id`) VALUES ('cf', '32', '2015-06-16', '', 56863)
Completed 500 Internal Server Error in 846ms (ActiveRecord: 69.0ms)

ActiveRecord::StatementInvalid (Mysql2::Error: COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'latin1': INSERT INTO `journal_details` (`property`, `prop_key`, `old_value`, `value`, `journal_id`) VALUES ('cf', '32', '2015-06-16', '', 56863)):"

So then I tried:

Adding both Convert() and Collate to the statement 

NEW.value > CONVERT(Due_date USING utf8)

COLLATE utf8_unicode_ci

and everything works fine again. =)

Extra:

MySQL DBA says there were no changes to the DB recently... so its Very odd that this kind of error would pop up at the time that it did... I would really like to know the real cause of the issue so I could fix THAT instead.
I still suspect something to do with the database collations & character sets, but one thing that makes me doubt this is the fact that my Dev environment machine has all character sets and collations set to UTF8, whereas my Prod environment database has 

character_set_client    utf8
character_set_connection    utf8
character_set_database  latin1
character_set_filesystem    binary
character_set_results   utf8
character_set_server    latin1
character_set_system    utf8
character_sets_dir  /usr/share/mysql/charsets/
collation_connection    utf8_general_ci
collation_database  latin1_swedish_ci
collation_server    latin1_swedish_ci

and I still get the same error in the same ways on my Dev vs Production environments.

Upvotes: 2

Related Questions