0

I'm having problems with setting the default collation for the utf8mb4 and utf8 charsets on a Percona XtraDB setup. If I write a query such as this:

SET @ue = 'test@email.com';

SET @u = (SELECT u.ID FROM db.users u 
WHERE u.user_email = @ue);

It fails with the below message:

/* SQL Error (1267): Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '=' */

This seems to be because the collation_connection session value is always being set as utf8mb4_general_ci. The global value for that variable is utf8_unicode_ci, which is still not quite right but more preferable.

I cannot for the life of me find where that session variable is coming from. I've had a look inside information_schema.COLLATIONS and can see that utf8_general_ci and utf8mb4_general_ci are as follows:

--------------------------------------------------------
| COLLATION_NAME     | CHARACTER_SET_NAME | IS_DEFAULT |
--------------------------------------------------------
| utf8_general_ci    | utf8               | Yes        |
--------------------------------------------------------
| utf8mb4_general_ci | utf8mb4            | Yes        |
--------------------------------------------------------

And yet I really don't want these collations as the default?

I've tried putting these values in each of the nodes my.cnf files and it doesn't seem to have any impact:

[mysqld]
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake

Any help greatly appreciated.

Tom
  • 1,275
  • 1
  • 18
  • 51
  • As it happens, I just put live an article on collations and conversions, I wonder if it might help shed some light? https://www.percona.com/community-blog/2018/06/12/character-sets-migrating-utf8b4-pt_online_schema_change/ Disclosure: I work for Percona. – greenweeds Jun 12 '18 at 16:36
  • What parameters are you using when connecting? – Rick James Jun 13 '18 at 18:21
  • @RickJames nothing in particular, just the default ones that Workbench and Heidi connect with. How can I see the parameters? – Tom Jun 14 '18 at 12:02

0 Answers0