aws aurora rds (mysql) in a cluster cannot insert emoji

Question

I am using an aurora rds (mysql) in a cluster and cannot insert emoji's. The column which I am trying to insert into has the collation:

utf8mb4 - utf8mb4_unicode_ci

I have tried inserting using client and also from mysql workbench writing the query but in both cases I just see ???? in the field.

I have updated the table default character set: utf8mb4 and default collation: utf8mb4_unicode_ci

But still getting ??? instead of emoji

Edit 1:

I've tried to edit the parameter group of the cluster and set all character set values to utf8mb4 and all collation values to utf8mb4_unicode_ci but still not working.

Answer 1

Your cluster parameter group should have the following options set:

• character_set_client: utf8mb4
• character_set_connection: utf8mb4
• character_set_database: utf8mb4
• character_set_server: utf8mb4
• collation_connection: utf8mb4_unicode_ci
• collation_server: utf8mb4_unicode_ci

Rebooting your instances after updating this might be required. When you connect to the cluster you want to set the correct collation for your connection, like this:

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

If you run SHOW VARIABLES LIKE "%collation%" after this you should see three variables that all have the correct collation (utf8mb4_unicode_ci).

You also need to convert your tables and columns to the correct charset and collation, this has been answered before on the DBA Stack Exchange: How to easily convert utf8 tables to utf8mb4 in MySQL 5.5