Buglyst
Start Debugging

LEARN · DEBUGGING GUIDE

MySQL utf8mb4 Encoding Error When Storing Emoji

Emoji storage in MySQL fails silently or with 'Incorrect string value' errors when the table is not utf8mb4. This guide covers every layer—connection, column, table, and server—that must be aligned.

IntermediateDatabase7 min read

What this usually means

MySQL's legacy utf8 character set (utf8mb3) only supports up to 3-byte UTF-8 characters. Emoji and many CJK characters are 4 bytes. To store them, you must use utf8mb4 at every layer: the database default, the table character set, the column character set, the client connection, and the application-side charset. The error 'Incorrect string value' is MySQL's way of saying 'I'm configured for utf8mb3 but you're sending a 4-byte character'.

( 01 )Fast diagnosis

The first ten minutes — establish facts before touching code.

  • 1Check the table character set: SHOW CREATE TABLE your_table; Look for CHARSET=utf8 (not utf8mb4).
  • 2Check the column character set: SHOW FULL COLUMNS FROM your_table; Look for collation like utf8_general_ci (not utf8mb4_general_ci).
  • 3Check the database default: SELECT @@character_set_database, @@collation_database;
  • 4Check the connection charset: SHOW VARIABLES LIKE 'character_set_%'; Look for character_set_client, character_set_connection, character_set_results.
  • 5Test with a direct SQL INSERT from a utf8mb4-aware client (e.g., mysql command line with --default-character-set=utf8mb4).
( 02 )Where to look

The specific files, logs, configs, and dashboards that usually own this bug.

  • search/etc/mysql/my.cnf or /etc/my.cnf: look for [client] and [mysqld] sections with default-character-set or character-set-server.
  • searchApplication configuration: e.g., Rails config/database.yml 'encoding: utf8mb4', Django DATABASES 'OPTIONS': {'charset': 'utf8mb4'}.
  • searchConnection string: e.g., JDBC URL with ?useUnicode=true&characterEncoding=utf8mb4.
  • searchMySQL error log: typically /var/log/mysql/error.log — may show collation mismatch warnings.
  • searchApplication logs: look for 'character_set' or 'charset' related errors.
  • searchDatabase schema migration files: check if charset is explicitly set to utf8 or omitted.
  • searchPerformance schema or information_schema: SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA='your_db' AND TABLE_COLLATION NOT LIKE '%utf8mb4%';
( 03 )Common root causes

Practical causes, not theory. These are the things you will actually find.

  • warningTable created with CHARSET=utf8 instead of CHARSET=utf8mb4 and COLLATE=utf8mb4_unicode_ci.
  • warningMySQL server default character set is utf8 (utf8mb3) in my.cnf under [mysqld] section.
  • warningClient connection charset not set to utf8mb4: PHP PDO, Python MySQL driver, or JDBC driver defaults to utf8.
  • warningDatabase upgrade where existing tables remain on utf8mb3 while new tables are created with utf8mb4.
  • warningColumn indexes on VARCHAR columns that exceed the maximum key length when switching to utf8mb4 (767 bytes vs 3072 bytes).
  • warningApplication code uses MySQL connection with 'SET NAMES utf8' instead of 'SET NAMES utf8mb4'.
( 04 )Fix patterns

Concrete fix directions. Pick the one that matches your root cause.

  • buildAlter table charset and collation: ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • buildAlter column charset if needed: ALTER TABLE your_table MODIFY your_column VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  • buildChange server defaults in my.cnf: [mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_unicode_ci; [client] default-character-set=utf8mb4; restart MySQL.
  • buildUpdate application connection parameters: set charset to utf8mb4 in connection string or after connection.
  • buildFor JDBC: add ?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_general_ci (note: Java uses 'UTF-8' for utf8mb4).
  • buildFor Python (mysqlclient): charset='utf8mb4' in connection dict.
  • buildCheck index lengths: if an index on a VARCHAR(255) column becomes too long, reduce column length or use ROW_FORMAT=DYNAMIC (requires proper file format).
( 05 )How to verify

A fix you cannot prove is a guess. Close the loop.

  • verifiedInsert a known emoji (e.g., '😀' U+1F600) and SELECT it back; it should display as the emoji, not '?'.
  • verifiedCheck SHOW CREATE TABLE your_table again; confirm CHARSET=utf8mb4.
  • verifiedRun SELECT HEX(column) FROM your_table WHERE id = <new_id>; for emoji '😀' you should see F09F9880 (4 bytes).
  • verifiedRun a query that uses emoji in a WHERE clause (e.g., WHERE message = '😀') and verify it matches.
  • verifiedCheck application logs for no more character set errors.
  • verifiedRun ALTER TABLE ... VALIDATE check? Not needed; just test with a representative set of emojis.
( 06 )Mistakes to avoid

Things that make this bug worse or harder to find.

  • warningOnly changing the column charset without converting the table; the table default may still be utf8mb3.
  • warningUsing SET NAMES utf8 after converting tables — must use SET NAMES utf8mb4.
  • warningNot adjusting index limits: a VARCHAR(255) index on a utf8mb4 column requires 1020 bytes (255*4), which may exceed innodb_large_prefix limits (767 bytes default). Ensure innodb_large_prefix=ON or use ROW_FORMAT=DYNAMIC.
  • warningForgetting to restart MySQL after changing my.cnf.
  • warningAssuming all emoji are 4 bytes — some are 3 bytes (e.g., some CJK symbols), but the important ones are 4 bytes. Convert everything to utf8mb4.
  • warningNot testing with a real emoji that is 4 bytes; test with '😀' (U+1F600) or '🌍' (U+1F30D).
( 07 )War story

Emoji in user bios broke the entire user update endpoint

Backend engineer on a social platformMySQL 5.7, Django 2.2, Python 3.6, AWS RDS

Timeline

  1. 09:15User reports that updating their profile bio with an emoji fails with 500 error.
  2. 09:20I check Django error logs; see OperationalError: (1366, "Incorrect string value: '\xF0\x9F\x98\x80' for column 'bio' at row 1").
  3. 09:25I examine the MySQL table: SHOW CREATE TABLE users_profile; shows CHARSET=utf8.
  4. 09:30I check server defaults: SELECT @@character_set_database; returns utf8.
  5. 09:35I alter the table: ALTER TABLE users_profile CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  6. 09:40Test the endpoint again; still fails with same error.
  7. 09:45I realize the database connection charset is wrong. I add 'OPTIONS': {'charset': 'utf8mb4'} to Django DATABASES config and restart the app.
  8. 09:50Test again; emoji saves and retrieves correctly.
  9. 10:00I also alter the bio column explicitly to ensure no remnants: ALTER TABLE users_profile MODIFY bio TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  10. 10:15I run a migration to set default charset on future tables: in Django settings, set 'OPTIONS': {'charset': 'utf8mb4', 'init_command': 'SET NAMES utf8mb4'}.

On a Tuesday morning, a user reported that updating their profile bio with an emoji caused a 500 error. The error log pointed to an 'Incorrect string value' for the bio column. I immediately suspected a charset issue because emoji are 4-byte UTF-8 characters and MySQL's utf8 charset (utf8mb3) only supports up to 3 bytes.

I checked the table structure and saw CHARSET=utf8. I ran ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4. That should have fixed it, but the error persisted. I then realized the Django database connection was still using utf8. I had to update the Django settings to explicitly set charset='utf8mb4' and add 'SET NAMES utf8mb4' to the connection init.

After that change, the emoji saved flawlessly. I also took the opportunity to alter the column explicitly and set a default charset for future migrations. The root cause was twofold: the table charset was utf8, and the client connection charset was utf8. Both had to be changed to utf8mb4. I also verified by inserting a few emojis and checking the HEX value to confirm it was stored as 4 bytes.

Root cause

The MySQL table was created with CHARSET=utf8, which cannot store 4-byte characters. Additionally, the Django application's database connection was using 'utf8' charset, preventing the emoji from being sent correctly even after the table was altered.

The fix

Altered the table and all relevant columns to utf8mb4, and updated Django's database configuration to use charset='utf8mb4' with 'SET NAMES utf8mb4'.

The lesson

Encoding must be consistent at every layer: server, database, table, column, and client connection. Changing only one layer will not fix the issue. Always verify with a real 4-byte emoji test.

( 08 )Why utf8mb4 Is Required for Emoji

MySQL's utf8 character set is actually utf8mb3; it stores only up to 3 bytes per character. Emoji like 😀 (U+1F600) are encoded as 4 bytes (F0 9F 98 80). If you try to store a 4-byte character in a utf8mb3 column, MySQL raises an error or truncates data.

utf8mb4 is a superset that supports up to 4 bytes per character, covering all Unicode characters including emoji, supplementary characters, and CJK extension B. The collation utf8mb4_unicode_ci (or _general_ci) is commonly used.

( 09 )Checking and Changing Every Layer

Start with the database default: SELECT @@character_set_database, @@collation_database;. If it's utf8, you can change it globally in my.cnf under [mysqld] with character-set-server=utf8mb4 and collation-server=utf8mb4_unicode_ci, then restart MySQL.

Next, convert existing tables: ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;. This changes the table default and all columns (except those explicitly defined). For columns with explicit charset, use ALTER TABLE ... MODIFY ... CHARACTER SET utf8mb4;.

Client connection charset must match. After connecting, run SET NAMES utf8mb4; or configure your client library. For example, in PHP PDO: $pdo = new PDO('mysql:host=...;charset=utf8mb4', ...);. In Python MySQLdb: conn = MySQLdb.connect(charset='utf8mb4'). In Java JDBC: jdbc:mysql://host/db?useUnicode=true&characterEncoding=UTF-8&connectionCollation=utf8mb4_general_ci.

( 10 )Index Length Limitations and Solutions

When converting to utf8mb4, each character takes up to 4 bytes. A VARCHAR(255) column will require up to 1020 bytes for indexing (255*4). In InnoDB, the maximum index key length is 767 bytes by default (or 3072 bytes with innodb_large_prefix=ON and ROW_FORMAT=DYNAMIC or COMPRESSED).

If you have an index on a VARCHAR(255) column, the ALTER TABLE may fail with 'Specified key was too long; max key length is 767 bytes'. To fix, either reduce the column length to 191 (191*4=764 bytes), enable innodb_large_prefix (set innodb_large_prefix=ON in my.cnf, requires file-per-table and DYNAMIC row format), or change the index to use a prefix (e.g., INDEX(column(191))).

( 11 )Handling Replication and Migration

If you have replication, ensure the slave also uses utf8mb4. Converting a table on the master will replicate the ALTER TABLE to the slave, but if the slave's server charset is still utf8, the slave may have issues. It's safer to set server defaults on both master and slave before altering tables.

For zero-downtime migrations, you can convert tables using pt-online-schema-change (Percona Toolkit) with --alter 'CONVERT TO CHARACTER SET utf8mb4' to avoid locking the table.

( 12 )Verifying the Fix Programmatically

Write a simple script that inserts a known emoji (e.g., '😀') into each affected column and retrieves it. Assert that the retrieved value equals the inserted value. Check the HEX of the stored value to confirm it's F09F9880.

Monitor application logs for any new charset-related errors after deployment. Also check slow query logs for any queries that might be affected by index changes.

Frequently asked questions

Why do I get 'Incorrect string value' only for some emoji but not others?

Some emoji are 3 bytes (e.g., basic Latin characters, some symbols) and fit in utf8mb3. The error occurs only for 4-byte characters (emoji like 😀, 🌍, supplementary characters). If you only test with 3-byte characters, you might think everything works.

Can I convert only specific columns instead of the whole table?

Yes, you can alter individual columns: ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4. However, it's recommended to convert the whole table to avoid mixed charsets within the same table, which can cause confusing behavior.

What is the difference between utf8mb4_unicode_ci and utf8mb4_general_ci?

utf8mb4_unicode_ci is more accurate for general Unicode collation (supports more languages) but slightly slower. utf8mb4_general_ci is simpler and faster but less accurate. For most applications, utf8mb4_unicode_ci is recommended. The difference is negligible for emoji storage.

My ALTER TABLE fails with 'Specified key was too long' — what should I do?

This happens because utf8mb4 uses more bytes per character, exceeding the index key length limit. Solutions: (1) Reduce indexed column length to 191 characters or less. (2) Enable innodb_large_prefix=ON and set row_format=DYNAMIC. (3) Use a prefix index: INDEX(column_name(191)).

Do I need to restart MySQL after changing my.cnf?

Yes, changes to my.cnf require a MySQL server restart to take effect. For runtime changes without restart, you can use SET GLOBAL character_set_server=utf8mb4; but this only affects new connections. For persistent changes, edit my.cnf and restart.