Not the answer you need?
Register and ask your own question!

Setting collation connection

hendryhendry EntrantCurrent User Role Novice
I'm using the Percona mysql client in Archlinux to connect to a database, `mysql Ver 8.0.15-5 for Linux on x86_64 (Source distribution)`

[[email protected] db]$ ./
mysql> SHOW SESSION VARIABLES LIKE 'collation_connection';
Variable_name Value
collation_connection utf8mb4_general_ci
mysql> call unit_create_with_dummy_users;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_unicode_520_ci,IMPLICIT) and (utf8mb4_general_ci,IMPLICIT) for operation '='

How do I connect with utf8mb4_unicode_520_ci ? Many thanks,


  • hendryhendry Entrant Current User Role Novice
    I think it's to do with my dump not restoring the database to the correct charset/collation!
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi, character sets and collations can be challenging sometimes, here is a presentation set from Percona Live on MySQL 8 and collations, delivered by Oracle which might help with a bullet point overview.

    There is also a free webinar on troubleshooting character sets here

    And a number of blog posts etc if you search on our site, for example

    Are you using dump as a way of migrating or... what's your use case? I think some of the resources I've shared might well set you right.
  • hendryhendry Entrant Current User Role Novice
    Thank you for the pointers. I guess I am looking for a canonical sane settings to aim for, whilst using the database with Emojis et al.

    I am totally confused at what I should be using or whether what I have is good.

    | Variable_name            | Value                  |
    | character_set_client     | utf8mb4                |
    | character_set_connection | utf8mb4                |
    | character_set_database   | utf8mb4                |
    | character_set_filesystem | binary                 |
    | character_set_results    | utf8mb4                |
    | character_set_server     | latin1                 |
    | character_set_system     | utf8                   |
    | collation_connection     | utf8mb4_general_ci     |
    | collation_database       | utf8mb4_unicode_520_ci |
    | collation_server         | latin1_swedish_ci      |

    Is that an OK way to interact with a MySQL database?
    I.e. ignore collation_server, character_set_server, since they are not on a DB level ?
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Apologies, I missed this update, I'll see if I can get someone to take a look.
  • lorraine.pocklingtonlorraine.pocklington Percona Community Manager Legacy User Role Patron
    Hi again, one of the team suggested this might be useful for you (command and example output):
    $ diff -uNp <(db_connect platform-team01 -Bse "show variables like '%character%'") <(db_connect platform-team01 --default-character-set=latin1 -Bse "show variables like '%character%'")
    --- /dev/fd/63  2019-05-09 11:18:23.244226659 +0000
    +++ /dev/fd/62  2019-05-09 11:18:23.244226659 +0000
    &#64;&#64; -1,8 +1,8 &#64;&#64;
    -character_set_client   utf8
    -character_set_connection       utf8
    +character_set_client   latin1
    +character_set_connection       latin1
     character_set_database latin1
     character_set_filesystem       binary
    -character_set_results  utf8
    +character_set_results  latin1
     character_set_server   latin1
     character_set_system   utf8
     character_sets_dir     /usr/share/mysql/charsets/

    Also that you can set collation with
    SET NAMES 'xxx' COLLATE 'xxx'
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.