Setting collation connection

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)

[hendry@t480s 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,

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

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.

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 ?

Apologies, I missed this update, I’ll see if I can get someone to take a look.

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'