A simple and silly story, about our tiny bug today. Today, I with my team in , 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. Kurio 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 as our charset. Which is not recommended after we fixing this issue. utf-8 So the solution is, we change our charset and collation to , because in Mysql is not fully support all the encoding generally. If we want a fully support all the generally, we must use . utf8mb4 utf-8 utf-8 utf-8 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 as our driver, we just need to add some changes to our dsn. github.com/go-sql-driver/mysql 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: Answer by Selvamani P in stackoverflow.com https://stackoverflow.com/a/39465494 Answer by Mathias Bynens in dba.stackexchange.com https://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.
