Setting collation connection

MySQL & MariaDB Other MySQL® Questions
1

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,

2

I think it’s to do with my dump not restoring the database to the correct charset/collation! [url]database - Cleaner mysql dumps - Stack Overflow

3

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 [URL]Percona Live Europe 2017 which might help with a bullet point overview.

There is also a free webinar on troubleshooting character sets here [URL]Upcoming Webinars

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

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.

4

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? [url]https://s.natalian.org/2019-04-29/collation.txt[/url]
I.e. ignore collation_server, character_set_server, since they are not on a DB level ?

5

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

6

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'