mysql - Illegal mix of collations (cp1251_general_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) in Grails -

i try use mysql in cp1251_general_ci encoding(with russian characters support) in grails. when start application, while bootstrap.gsp executes, error:

2015-06-04 15:52:28,889 [localhost-startstop-1]      error spi.sqlexceptionhelper  - illegal mix of collations (cp1251_general_ci,implicit) , (latin1_swedish_ci,coercible)      operation '=' 

in mysql deleted old database , created new required encoding, error still. can fix it?

the fix depends on statement that's throwing error.

basically, mysql complaining expressions on either side of =, characterset/collations aren't compatible.

the error due mismatch in charactersets between 2 expressions being compared.

i run error when run statement creates inline view (derived table), , derived table (as expected) created characterset of client connection, , in outer query equality comparison made column table has incompatible characterset, example:

   select l.latin1col      latin1table l      join (select 'utf8string' utf8col) s        on s.utf8col = l.latin1col 

the characterset of column in inline view isn't specified, derived table inherits character_set_connection variable. 1 possible fix change setting of variable session. (then when statement runs, column in derived table characterset.)

the other fix specify characterset column in inline view, e.g.

   select l.latin1col      latin1table l      join (select _latin1'utf8string' latin1col) s --                ^^^^^^^         on s.latin1col = l.latin1col 

---

to fix problem, need figure out expression characterset, , make suitable adjustment on 1 side or other.

that is, cp1251_general_ci collation coming client connection, or table column.

if you're using literal in statement, can converted suitable characterset/collation comparison column of different characterset/collation convert function...

for example, rather this:

 col = 'myliteral' 

you can this:

 col = convert('myliteral' using latin1) 

-or- literal values, can this

 col = _latin1'myliteral' 

if need specify collation, can follow collate keyword , specify collation valid characterset:

 col = convert('myliteral' using latin1) collate latin1_swedish_ci 

---

it's not clear question character_set_connection , collation_connection variables set to.

 select @@session.character_set_connection       , @@session.collation_connection        , @@global.character_set_connection       , @@global.collation 

or characterset , collation of character column(s) in database set to,

 show create table mytable; 

---

note:

the characterset setting @ database level default value that's applied new tables added database not have characterset/collation defined.

the "default characterset" setting of table characterset used new column added doesn't specify characterset/collation.

issuing statement change characterset table this:

alter table mytable character set latin1 collate latin1_swedish_ci 

does not change characterset or collation columns in table. affects new columns added don't have characterset/collation specified. same thing applies alter database statement... doesn't have affect on existing tables , columns, on new tables created don't specify characterset/collation.