How to change the connection collation of Mysql

Question

How can I change connection collation of mysql database?

I am using Mysql workbench 5.5 and mysql 5.5 in ubuntu 14.

When I execute a stored procedure, an error occurs:

Error Code: 1267. Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

I have search though the internet, which has a temp solution that is to amend

COLLATE utf8_unicode_ci;

in the stored procedure.

But I want to fix this problem for all stored procedures in the future. I have found

SHOW VARIABLES LIKE 'collation%';

which return this.

collation_connection    utf8_general_ci
collation_database  utf8_unicode_ci
collation_server    latin1_swedish_ci

how can I change utf8_general_ci to utf8_unicode_ci?

First of all, your MySQL Workbench version is way too old. Update it before retrying. — Raptor, Aug 15 '16 at 03:59
Let's see the query that got the error message, plus `SHOW CREATE TABLE` for the table involved. Also, `SHOW VARIABLES LIKE 'char%';`. — Rick James, Aug 15 '16 at 23:55

Raptor · Answer 1 · 2018-09-10T02:32:53.313

14

Look into your my.cnf, find the contents below near collation_server:

[mysqld]
init_connect='SET collation_connection = utf8_unicode_ci'
init_connect='SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_unicode_ci 
skip-character-set-client-handshake

Then change your collation variables to:

collation_connection    utf8_unicode_ci
collation_server        latin1_swedish_ci

Remember to restart MySQL server service.

For DB collation, you can use the following SQL:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

or you can do it at Alter database screen in MySQL Workbench (always update this to the latest version!)

edited Sep 10 '18 at 02:32

answered Aug 15 '16 at 04:02

Raptor

53,206
45
230
366

10

Keep in mind that `init_connect` is ignored when connecting as `root`. – Rick James Aug 15 '16 at 23:54
What is the solution then if i connect using root – Koder Sep 09 '18 at 14:11
2

From the docs, *For users that have the CONNECTION_ADMIN or SUPER privilege, the content of init_connect is not executed. This is done so that an erroneous value for init_connect does not prevent all clients from connecting. For example, the value might contain a statement that has a syntax error, thus causing client connections to fail. Not executing init_connect for users that have the CONNECTION_ADMIN or SUPER privilege enables them to open a connection and fix the init_connect value.* If you connect using `root` and wish to use custom connection collation, please use a non-SUPER account. – Raptor Sep 10 '18 at 02:35
Docs link: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_init_connect – Raptor Sep 10 '18 at 02:36
It should be noted that `utf8` is not actually UTF-8 in mysql collations. Use `utf8mb4` to use that. [What is the difference between utf8mb4 and utf8 charsets in MySQL?](https://stackoverflow.com/q/30074492/2415524) – mbomb007 Jul 25 '23 at 16:10

score 0 · Answer 2 · edited Nov 19 '16 at 08:28

Firstly, I think the error message is because of the collation of stored procedure (connection) doesn't match the table. But I was wrong, the reason for the error is because of the collation of the column doesn't match the collation of the table.

I want to change to collation of column to 'utf8_unicode_ci' in my case. So I have run this statement:

alter table <YourTableName> 
    MODIFY <YourColumnName> VARCHAR(XXX) COLLATE 'utf8_unicode_ci';

Please be aware that change of collation may result in data loss. For me, General -> Unicode, with all English in varchar column. There is none.

Further reading: http://dev.mysql.com/doc/refman/5.7/en/charset-column.html

http://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

http://dev.mysql.com/doc/refman/5.7/en/charset-database.html

How to change the connection collation of Mysql

2 Answers2

Linked