Today I Learned: Storing Emoji to Mysql with Golang

A simple and silly story, about our tiny bug today.

Today, I with my team in Kurio, having a bit of funny moment but also silly thing. It’s happen when we trying to store emojis to our storage in Mysql through our new and simple CRUD API services.

We’re making an internal service that used by our content-team, just a simple CRUD. The stacks is just: Angular + Golang + Mysql.

The services already deployed to production and already used by our content-team. Everything is normal, because it’s already tested from staging until it’s released. Until, when one of our content-team started to store an item to this services, and the payload have a few of emojis. That’s when we realized this silly bug.

The payload is more look like this :

{"type": "text","text": "😈🤠 Lorem Ipsum Dolor sit Amet 😱","color": "#FFFFFF"}

And returning error about incorrect value to store.

"Number": 1366"Message": "Incorrect string value: '\\xF0\\x9F\\x98\\x88 \\xF0...' for column 'text' at row 1"

Luckily, this services is for internal use to our content-team, and the user load is not huge, so it’s still tolerable 😈. I can’t imagine what will happen if we released this to our real user.

Solving The Issue

To solve this issue, we do a few things related to this issue.

• Change the Database’s charset encoding and collation
• Change the Connection Driver’s charset encoding and collation from our application(Golang) to Mysql Server

Change Database Encoding Charset and Collation

our default charset before changed

We use Mysql 5.7 and using utf-8 as our charset. Which is not recommended after we fixing this issue.

So the solution is, we change our charset and collation to utf8mb4, because utf-8 in Mysql is not fully support all the utf-8 encoding generally. If we want a fully support all the utf-8 generally, we must use utf8mb4 .

To change the whole database’s charset and collation:

ALTER DATABASE    database_name    CHARACTER SET = utf8mb4    COLLATE = utf8mb4_unicode_ci;

To change the whole a specific table’s charset and collation

ALTER TABLE    table_name    CONVERT TO CHARACTER SET utf8mb4    COLLATE utf8mb4_unicode_ci;

So after making this, we think we have solved our problem. But, after we test it again, we still don’t fixed it. It still returning the same error.

Change Connection Charset in Mysql Driver

After reading more article and some q/a in stackoverflow and other forums. It’s not only the database, we also need to change our connection’s driver charset and collation. Because this service that we developed, is built from Golang (1.10), and also we use package github.com/go-sql-driver/mysql as our driver, we just need to add some changes to our dsn.

Before:

dsn := `root:root@tcp(127.0.0.1 :3306)/DB_NAME?parseTime=1&loc=Asia%2FJakarta`

dbConn, _:= sql.Open(`mysql`, dsn)

After:

dsn :=`root:root@tcp(127.0.0.1 :3306)/DB_NAME?parseTime=1&loc=Asia%2FJakarta**&charset=utf8mb4&collation=utf8mb4_unicode_ci`**

dbConn, _:= sql.Open(`mysql`, dsn)

Well, after doing this, now our service is running well, and can accept any emoji characters.

Conclusions

This bug is not too big, to be honest, it is a silly and funny bug. But it looks like everyone had ever fallen to this trap 😥. But today I learned something, that we should be careful when creating a database, regardless whatever its type (RDBMS, NoSQL)

References:

1. Answer by Selvamani P in stackoverflow.com https://stackoverflow.com/a/39465494

2. 
   

   Answer by Mathias Bynens in dba.stackexchange.comhttps://dba.stackexchange.com/a/21684

If you found this article useful, or interesting to read, please kindly share to your network circle, or you can just give claps to help other to reach this. If you have any question or something, just put a response below.