query mysql caused error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT)

Question

I have a grails web application and use Mysql, when I query Persian string, I've seen this error:

ERROR util.JDBCExceptionReporter  - Illegal mix of collations        (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

this is my datasource config:

        dataSource {
        driverClassName = "com.mysql.jdbc.Driver"
        dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
        username = "user"
        password = "pass"
        url = "jdbc:mysql://localhost/nicefamilytree?useUnicode=yes&characterEncoding=UTF-8"
        }

I query mysql variable(show variables like 'collation%') and result is like this:

mysql> show variables like '%collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+

I ran below command to alter database charset:

ALTER DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

but collation_server didn't change and I have same error in query.

Alter command does not convert existing tables, it only sets the default for newly created tables. see http://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8 — ABC, Aug 19 '16 at 13:13

score 1 · Accepted Answer · answered Aug 23 '16 at 06:56

Change your DataSourceConfig as below:

   dataSource {
        driverClassName = "com.mysql.jdbc.Driver"
        dialect = "org.hibernate.dialect.MySQL5InnoDBDialect"
        username = "user"
        password = "pass"
        url = "jdbc:mysql://localhost/nicefamilytree?useUnicode=yes
        }

Don't do any conversions and just save what you are getting.

Not sure if it helps!!

query mysql caused error: Illegal mix of collations (latin1_swedish_ci,IMPLICIT)

1 Answers1