Set MySQL server variable collation_connection to utf8_unicode_ci on AWS RDS

Question

So my goal is to set all the character sets and collations to utf8 and utf8_unicode_ci.

Im using an AWS RDS to host the MySQL server.

Ive set the collation_connection variable to utf8_unicode_ci in the Parameter group for the RDS. .

Ive then rebooted my RDS and when going to MySQL console it shows the following values for variables.

mysql> show variables like "%character%";show variables like "%collation%";
+--------------------------+-------------------------------------------+
| Variable_name            | Value                                     |
+--------------------------+-------------------------------------------+
| character_set_client     | utf8                                      |
| character_set_connection | utf8                                      |
| character_set_database   | utf8                                      |
| character_set_filesystem | binary                                    |
| character_set_results    | utf8                                      |
| character_set_server     | utf8                                      |
| character_set_system     | utf8                                      |
| character_sets_dir       | /rdsdbbin/mysql-5.6.22.R1/share/charsets/ |
+--------------------------+-------------------------------------------+
8 rows in set (0.01 sec)

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

Im not sure why the collation_connection is set as utf8_general_ci

Answer 1

The variables which you see here are the current client session's variables:

+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+

So, you need to run the following commands, when you initiate the connection, on the client from which you are connecting:

SET collation_connection = 'utf8_unicode_ci';

Answer 2

The collation_connection variable is set by the client; clients may be compiled with different settings - in this case, the default collation for utf8 appears to be utf8_general_ci.

The problem is that the MySQL client allows the user to set the charset (default-character-set), but not the collation. If one needs a specific collation, this can't be directly worked around.

In this case, what one can do (besides runtime changes) is to set the server character-set-client-handshake variable off (0). This will skip the character set (and implicitly, collation) negotiation with the client(s), and will impose the server charset/collation settings on any client connection.

Answer 3

In the Parameter group, set the variable init_connect to

SET COLLATION_CONNECTION=utf8_unicode_ci

Answer 4

I did it - prescribed in /etc/mysql/mariadb.conf.d/50-server.cnf

character-set-client-handshake = FALSE

I don’t know/don’t understand how identical the two parameters (--skip... and FALSE), but if they are identical, then in the official documentation it says

Don't ignore client side character set value sent during handshake. (Defaults to on; use --skip-character-set-client-handshake to disable.)

Also in the documentation, I did not find anything about the value FALSE, but it works.

Answer 5

If you have any existing tables with different CHARACTER SET or COLLATION, they need converting; see ALTER TABLE ... CONVERT TO ....

If you code does not connect as utf8, that needs changing. The details are very language specific (Java vs PHP vs ...).

And, if you are doing html pages, they need to start with

<meta charset=UTF-8>

As long as you are going through this exercise, you should go all the way, so that you can handle all of Chinese and Emoji: utf8mb4 and utf8mb4_unicode_520_ci.