Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Node.js, PHP and APIs

PDO and MySQL store or show broken UTF-8 text

ErrorAccented letters appear as question marks or strange symbols

Short answer

MySQL converts text between the client and the table using the connection character set. If PDO does not set it, accented letters turn into question marks or mojibake. Add charset=utf8mb4 to the PDO DSN, do not write UTF-8 with a dash in MySQL, and check the session variables.

  • Applies to PHP 8.3, MySQL 8.4
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Ask the database what the connection really uses. If character_set_client or character_set_results is not utf8mb4, the connection is the problem.

    $pdo->query("SELECT @@character_set_client, @@character_set_connection, @@character_set_results")
        ->fetch(PDO::FETCH_ASSOC);
  2. 2Match what you see, then jump to its fix

    1. 1The first-step query shows a charset like latin1The DSN does not name a charsetFix
    2. 2Code runs SET NAMES after connecting, but quoted values still breakSET NAMES is used for a job it does not do fullyFix
    3. 3An error or a silent fallback when the charset is setThe charset name is spelled UTF-8 with a dashFix
    4. 4Existing rows look wrong even after the DSN is fixedThe connection charset does not match the data or the columnFix

What does the error look like?

  • Accented letters appear as question marks or strange symbols
  • Text read back from the database differs from what was saved

Cause 1 of 4

The DSN does not name a charset

1Check

The connection character set decides how text bytes are read and returned. Without it, the server's default is used. The PHP manual sets the charset for PDO in the DSN.

SELECT @@character_set_client, @@character_set_results;

2Fix

Add the charset to the DSN. Use utf8mb4 so four-byte characters work.

$pdo = new PDO(
    "mysql:host=localhost;dbname=app;charset=utf8mb4",
    $user,
    $pass
);

3Verify

The first-step query now shows utf8mb4 in all three values.

$pdo->query("SELECT @@character_set_client")->fetchColumn();

Confirmed by php.net

Did this fix it?

Cause 2 of 4

SET NAMES is used for a job it does not do fully

1Check

The PHP manual warns that SET NAMES and SET CHARACTER SET do not change the character set used by the escaping functions of the driver. PDO::quote() follows the connection setting.

grep -rn "SET NAMES" src/

2Fix

Remove the SET NAMES call and set the charset in the DSN, as in the previous cause.

"mysql:host=localhost;dbname=app;charset=utf8mb4"

3Verify

Quoted and bound values keep their accents.

echo $pdo->quote("café");

Confirmed by php.net

Did this fix it?

Cause 3 of 4

The charset name is spelled UTF-8 with a dash

1Check

MySQL character set names have no dashes. The manual says UTF-8 is not valid and fails to change the character set, while utf8 and utf8mb4 are valid names.

grep -rn "UTF-8" src/ config/

2Fix

Write utf8mb4 in the DSN and in any SET NAMES statement. The word UTF-8 with a dash belongs only in HTTP headers and HTML.

charset=utf8mb4

3Verify

The session variables report utf8mb4.

SELECT @@character_set_client;

Confirmed by php.net

Did this fix it?

Cause 4 of 4

The connection charset does not match the data or the column

1Check

The server converts between the client's character set and the column's, and the conversion can lose characters that are not in both sets. If the stored bytes were written under a wrong connection setting, they are already wrong in the table.

SELECT @@character_set_client, @@character_set_connection, @@character_set_results;

2Fix

Set the connection to the charset your application really sends, on every connection. Re-import or repair rows that were saved wrongly, after taking a copy of the table.

SET NAMES 'utf8mb4';

3Verify

New rows saved after the fix read back identically.

SELECT title FROM posts ORDER BY id DESC LIMIT 1;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Put charset=utf8mb4 into the one place that builds the DSN.
  • Use the same character set for the connection as your application sends.
  • Check the three connection variables after any change to the connection code.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Projects of mine built on the same technologies:

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.