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]$ ./connect.sh
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 '='
mysql>


How do I connect with utf8mb4_unicode_520_ci ? Many thanks,

Comments

  • hendryhendry Entrant Current User Role Novice
    I think it's to do with my dump not restoring the database to the correct charset/collation! https://stackoverflow.com/questions/55860549/cleaner-mysql-dumps
  • 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 https://www.percona.com/live/e17/sites/default/files/slides/Collations%20in%20MySQL%208.0.pdf which might help with a bullet point overview.

    There is also a free webinar on troubleshooting character sets here https://www.percona.com/resources/webinars/troubleshooting-issues-mysql-character-sets

    And a number of blog posts etc if you search on our site, for example https://www.percona.com/blog/2018/04/10/migrating-database-charsets-to-utf8mb4/

    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? https://s.natalian.org/2019-04-29/collation.txt
    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.