Arabic text turns into ???? in MySQL: the utf8mb4 fix

The symptom

A form saves مرحبا and the database shows ?????. Or the page shows مرحبا. Emoji in names break inserts with Incorrect string value.

The cause

Somewhere between the browser and the disk, a step is not using full UTF-8. In MySQL, the old utf8 charset is really utf8mb3: it stores up to three bytes per character, so emoji and some symbols fail. If any one layer uses latin1 or utf8mb3, text gets mangled or replaced with ?.

There are four layers, and all four must agree.

The fix

1. The database

ALTER DATABASE app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

2. Every table (existing columns do not change on their own)

ALTER TABLE messages CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create new tables with it explicitly:

CREATE TABLE posts (...) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

3. The connection. In Node.js with mysql2, set the charset on the pool:

import mysql from 'mysql2/promise';
const pool = mysql.createPool({ host, user, password, database, charset: 'utf8mb4' });

4. Backups and imports. Dumps must be read and written with the same charset:

mysqldump --default-character-set=utf8mb4 app > app.sql
mysql --default-character-set=utf8mb4 app < app.sql

Check it

SHOW VARIABLES LIKE 'character_set_%';
SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'app';

Then insert مرحبا 👋 and read it back. If both survive, every layer is right.

Already-broken data

Rows saved as ? are gone; the characters were replaced before they were stored. Rows that look like مرحبا were double-encoded and can often be repaired by converting through latin1 and back. Test on a copy first.

FAQ

utf8mb4_unicode_ci or utf8mb4_0900_ai_ci?

On MySQL 8, utf8mb4_0900_ai_ci is the modern default and sorts well. utf8mb4_unicode_ci works on more versions. Pick one and use it everywhere so joins and comparisons don't complain.

Does this affect Arabic search?

Collation decides how text compares and sorts. Both collations above handle Arabic letters; for advanced Arabic search (ignoring diacritics or letter variants) you normalize the text before saving.

---

Hit a bug like this in your own project? The editor on ahmed-adil.com can pass it to Ahmed.