Fix MySQL "Incorrect string value" With utf8mb4

Problem statement

Saving a comment, product name, or username fails when it contains an emoji or certain Asian characters:

django.db.utils.DataError: (1366, "Incorrect string value: '\\xF0\\x9F\\x98\\x80' for column 'body' at row 1")

\xF0\x9F\x98\x80 is 😀 in UTF-8, a 4-byte character. MySQL's legacy utf8 charset (an alias for utf8mb3) stores at most 3 bytes per character, so it cannot hold emoji or many supplementary characters. The column, table, database, or connection is using utf8/utf8mb3 or latin1. The fix is utf8mb4 everywhere, including the Django connection.

1. Set the connection charset: "OPTIONS": {"charset": "utf8mb4"} in Django's DATABASES.
2. Convert the database default: ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;.
3. Convert each table: ALTER TABLE app_model CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;.
4. Back up first and test on a copy; conversions rewrite tables.
5. On MySQL 5.6 or old configs, watch for index key length errors on VARCHAR(255) unique columns.

Step-by-step solution

1. Find what is not utf8mb4

SELECT @@character_set_server, @@collation_server;
SELECT default_character_set_name FROM information_schema.SCHEMATA WHERE schema_name = 'myapp';

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = 'myapp' AND character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4';

The last query lists every text column that still needs converting.

2. Set the connection charset

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.mysql",
        # ...
        "OPTIONS": {
            "charset": "utf8mb4",
            "init_command": "SET sql_mode='STRICT_TRANS_TABLES'",
        },
    }
}

Without this, the client may send data as utf8mb3 and MySQL rejects or mangles 4-byte characters before they reach the column. Strict mode makes MySQL raise errors instead of silently truncating text to ?.

3. Convert database and tables

Back up first:

mysqldump --single-transaction --routines myapp > myapp-before-utf8mb4.sql

Then:

ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE blog_comment CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Generate the statements for every table:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES WHERE table_schema = 'myapp' AND table_type = 'BASE TABLE';

On MySQL 8, utf8mb4_0900_ai_ci is the server default and a good modern collation; MariaDB uses utf8mb4_unicode_ci or newer uca1400 collations. Pick one and use it consistently, because joining columns with different collations raises "Illegal mix of collations" errors.

4. Handle index length errors

On older setups (MySQL 5.6, or innodb_large_prefix off), converting a VARCHAR(255) with a unique index fails:

(1071, 'Specified key was too long; max key length is 767 bytes')

255 characters × 4 bytes = 1,020 bytes, above the 767-byte limit. Fixes: upgrade to MySQL 5.7+/8 with DYNAMIC row format (limit 3,072 bytes), or reduce the column to VARCHAR(191) with a Django migration before converting.

5. Big tables and downtime

CONVERT TO CHARACTER SET copies the table. On large tables this takes time and blocks writes on older versions. Run it in a maintenance window, or use an online schema change tool such as gh-ost or pt-online-schema-change. Test duration on a restored copy first.

Verification check: rerun the information_schema.COLUMNS query (it should return no rows), then in a Django shell save and reload a model with "test 😀" in a text field and compare the values.

Explanation

MySQL implemented UTF-8 in 2003 with a 3-byte maximum, which covered the Basic Multilingual Plane but not supplementary characters such as emoji, which became common years later. utf8mb4 was added in MySQL 5.5 as the correct implementation, but utf8 remained an alias for the 3-byte version for compatibility. Databases created on older servers or with copied configs keep the legacy charset table by table, and the error only surfaces when someone types a 4-byte character.

Edge cases / notes

• Full-text indexes need rebuilding after conversion.
• Stored data in latin1 that actually holds UTF-8 bytes (double-encoded "mojibake") needs a careful two-step conversion via BINARY; do not just CONVERT.
• Test databases. Set "TEST": {"CHARSET": "utf8mb4", "COLLATION": "utf8mb4_unicode_ci"} so tests match production.
• Managed MySQL may require changing the server default in a parameter group.

Next steps

• Set up the backend correctly with Configure Django With MySQL in Production.
• Plan long table rewrites with Django Schema Changes on Large Tables.

FAQ

Is MySQL utf8 the same as UTF-8?

No. MySQL's utf8 is utf8mb3, limited to 3 bytes. Real UTF-8 is utf8mb4.

Do I need to change Django settings too?

Yes. Set "charset": "utf8mb4" in OPTIONS, or the connection itself cannot carry 4-byte characters.

Which collation should I use?

utf8mb4_0900_ai_ci on MySQL 8, or utf8mb4_unicode_ci for broad compatibility. Use one consistently.

Will converting lose data?

Converting from utf8mb3 to utf8mb4 is lossless. Back up anyway, especially for latin1 sources.