# Setting collation connection

**URL:** https://forums.percona.com/t/setting-collation-connection/6982
**Category:** Other MySQL® Questions
**Created:** [April 25, 2019, 12:11am UTC](https://forums.percona.com/t/setting-collation-connection/6982 "2019-04-25T00:11:37Z")
**Posts on this page:** 6
**Page:** 1

<div class="post-metadata">

### Author: ![hendry](https://avatars.discourse-cdn.com/v4/letter/h/ac91a4/32.png) [@hendry](https://forums.percona.com/u/hendry)
#### Post date: [April 25, 2019, 12:11am UTC](https://forums.percona.com/t/setting-collation-connection/6982/1 "2019-04-25T00:11:37Z")

</div>

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,

---

<div class="post-metadata">

### Author: ![hendry](https://avatars.discourse-cdn.com/v4/letter/h/ac91a4/32.png) [@hendry](https://forums.percona.com/u/hendry)
#### Post date: [April 26, 2019, 12:18am UTC](https://forums.percona.com/t/setting-collation-connection/6982/2 "2019-04-26T00:18:29Z")

</div>

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](https://stackoverflow.com/questions/55860549/cleaner-mysql-dumps%5B/url%5D)

---

<div class="post-metadata">

### Author: ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)
#### Post date: [April 26, 2019, 2:14am UTC](https://forums.percona.com/t/setting-collation-connection/6982/3 "2019-04-26T02:14:29Z")

</div>

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](https://www.percona.com/live/e17/sites/default/files/slides/Collations%20in%20MySQL%208.0.pdf%5B/URL%5D) which might help with a bullet point overview.

There is also a free webinar on troubleshooting character sets here [URL][Upcoming Webinars](https://www.percona.com/resources/webinars/troubleshooting-issues-mysql-character-sets%5B/URL%5D)

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]](https://www.percona.com/blog/2018/04/10/migrating-database-charsets-to-utf8mb4/%5B/URL%5D)

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.

---

<div class="post-metadata">

### Author: ![hendry](https://avatars.discourse-cdn.com/v4/letter/h/ac91a4/32.png) [@hendry](https://forums.percona.com/u/hendry)
#### Post date: [April 29, 2019, 12:02am UTC](https://forums.percona.com/t/setting-collation-connection/6982/4 "2019-04-29T00:02:02Z")

</div>

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.

```auto
+--------------------------+------------------------+
| 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]](https://s.natalian.org/2019-04-29/collation.txt%5B/url%5D)  
I.e. ignore collation\_server, character\_set\_server, since they are not on a DB level ?

---

<div class="post-metadata">

### Author: ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)
#### Post date: [May 9, 2019, 5:22am UTC](https://forums.percona.com/t/setting-collation-connection/6982/5 "2019-05-09T05:22:55Z")

</div>

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

---

<div class="post-metadata">

### Author: ![lorraine.pocklington](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/lorraine.pocklington/32/37_2.png) [@lorraine.pocklington](https://forums.percona.com/u/lorraine.pocklington)
#### Post date: [May 9, 2019, 6:14am UTC](https://forums.percona.com/t/setting-collation-connection/6982/6 "2019-05-09T06:14:11Z")

</div>

Hi again, one of the team suggested this might be useful for you (command and example output):

```auto
$ 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

```auto
SET NAMES 'xxx' COLLATE 'xxx'

```