AWS Builder Center

Builder Center

The emoji that broke our signup form, and what utf8mb4 means

A classmate signed up for our class project with an emoji in their display name and got a 500 error. The MySQL table was using the old 3-byte utf8, and fixing it taught me more about character sets than I planned.

For a web development class, our group built a small events app on RDS for MySQL. Two days after we shared the link with the class, someone signed up with a display name that ended in a party popper emoji. The signup form returned a 500, and our logs showed a MySQL error: Incorrect string value.
My first theory was that the emoji was somehow our frontend's fault. It wasn't.
What was actually going on. In MySQL, the character set called utf8 is really utf8mb3, an older encoding that stores at most 3 bytes per character. Most emoji need 4 bytes. So MySQL refused to store the name. The character set that handles all of Unicode, emoji included, is utf8mb4. MySQL 8.0 uses utf8mb4 by default, so how did we end up with the old one?
We had copied our first migration from a tutorial, and it explicitly created every table with DEFAULT CHARSET=utf8. The tutorial was old. The database was fine. Our tables were not.
The fix. For each table with user-entered text, we converted it:
```sql
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
```
A couple of things to watch for while doing this:
  • Index length. utf8mb4 uses up to 4 bytes per character, so an index on a long VARCHAR column can hit the index size limit. One of our columns was VARCHAR(1000) with a unique index for no real reason, which is too long for an index once every character can take 4 bytes. We shortened it.
  • The connection charset. The table can be utf8mb4 and you can still get mangled text if the client connection uses a different charset. We set charset=utf8mb4 in the connection string explicitly.
  • Collation changes comparisons. Different collations can compare accented or differently-cased strings differently, which can affect unique constraints. We ran our tests again after converting, and one duplicate-username check behaved slightly differently than before.
We also added a test that signs up a user with an emoji name, so this specific bug can never come back quietly.
The classmate with the party popper was very gracious about being our first real QA engineer. Their name displays correctly now, emoji and all.
# mysql
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article