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.

Determinable

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

Dosia

I have set all the cluster parameter group options already. And in the instance parameter group under init_connect I have set the value previously to SET NAMES utf8mb4 but after reading your post updated that to SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci. rebooted again and still not working. I am certain the tables in the database have the correct charset and collation. Not really sure what else I can try. As for the SHOW VARIABLES LIKE "%collation%", is that a command prompt statement for mysql? I tried SHOW VARIABLES LIKE 'collation' in mysql workbench and there was an empty reply – Determinable

okay. finally it's working. SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci had to be entered in the console as the init_connect parameter was not having any effect. Not sure if that's because init_connect defined in instance and not cluster but in any case thanks for your help. – Determinable

@Determinable great to hear! – Dosia

For me there were 2 parameter groups named "default.aurora5.6" - only the one with type "DB cluster parameter group" has these options – Rambutan

after I made the changes collation_databas is still latin1_swedish_ci. what can I do? – Preterition

Answer 2

Changing the default character set of a table doesn't alter any of the existing columns. You can run SHOW CREATE TABLE MyTable and see the difference.

For example, we can create a table with an old-fashioned charset:

mysql> create table MyTable (string1 varchar(100) ) default character set = latin1;

mysql> show create table MyTable\G
*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `string1` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Next, we change the table's default, but we see that does not change the column. The existing column automatically gets an option showing us what it actually uses for a charset, since it's now different from the table default:

mysql> alter table MyTable default charset = utf8mb4;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table MyTable\G
*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `string1` varchar(100) CHARACTER SET latin1 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

The default is used for columns added to the table subsequently:

mysql> alter table MyTable add column string2 varchar(100);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table MyTable\G
*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `string1` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `string2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

To convert the existing column, use CONVERT TO CHARACTER SET. This rewrites the table, converting the existing data of each column if needed:

mysql> alter table MyTable convert to character set utf8mb4;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table MyTable\G
*************************** 1. row ***************************
       Table: MyTable
Create Table: CREATE TABLE `MyTable` (
  `string1` varchar(100) DEFAULT NULL,
  `string2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now both columns say nothing about their respective charsets, because they match the table's default.

Children

that is useful and good information. however, in my case it did not solve the problem. changing the encoding of the table using the alter table statement above does not allow me to enter emoji still. I am getting ???? for emoji entries. Oddly there are a few emoji which do appear as emoji but most of them are replaced with question marks. – Determinable

You may also need to use SET NAMES utf8mb4; in your client session, and also make sure your application supports utf8mb4, so the strings remain encoded that way from input all the way to the database, and also do the same when fetching them. – Children

Based on what you said I've updated the parameter group for the cluster containing the RDS and set all character set and collation values but still not working. I believe that is the same as "SET NAMES utf8mb4". I think it may be a Aurora issue. I've edited the question to reflect what I have done. – Determinable

Answer 3

I wrote a detailed blog post (a few years back) explaining how to support emoji and Aurora MySQL in Ruby on Rails here:

https://josephecombs.com/2018/05/06/how-to-support-emojis-with-rails-elasticbeanstalk-and-amazon-aurora

I know external links are frowned upon here, but this could help others in a similar situation. The guide is too long to paste as an answer.

Restorative