Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

MySQL rejects text with ERROR 1300: Invalid utf8mb4 character string

ErrorERROR 1300 (HY000): Invalid utf8mb4 character string: 'Paysand'

Short answer

Error 1300 appears when the bytes MySQL receives are not valid in the character set it was told to expect. Two usual sources: a file loaded with the wrong CHARACTER SET (a file saved in another encoding), or a client whose connection character set does not match what it really sends.

  • Applies to MySQL 8.0, MySQL 8.4, mysql client 8.x
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    See what the session and the database currently assume, then compare with how the source file was saved.

    SHOW VARIABLES LIKE 'character_set%';
    SHOW WARNINGS;
  2. 2Match what you see, then jump to its fix

    1. 1The error appears while loading a CSV or text file, and the text near the bad byte is accentedLOAD DATA reads the file in the wrong character setFix
    2. 2Statements typed in the mysql client or sent from an app, not a file, trigger the errorThe client connection character set does not match what it sendsFix

What does the error look like?

  • ERROR 1300 (HY000): Invalid utf8mb4 character string: 'Paysand'
  • Invalid utf8mb4 character string

Cause 1 of 2

LOAD DATA reads the file in the wrong character set

1Check

LOAD DATA reads the whole file in character_set_database unless you say otherwise. SET NAMES and character_set_client do not change how the file is read.

SELECT @@character_set_database;

2Fix

State the real encoding of the file in the statement. Use utf8mb4 only if the file is UTF-8; if it was saved in another encoding, name that character set instead (latin1 is one example). Files in ucs2, utf16, utf16le or utf32 cannot be loaded at all: convert them to UTF-8 first.

LOAD DATA LOCAL INFILE '/tmp/places.csv'
  INTO TABLE places
  CHARACTER SET utf8mb4
  FIELDS TERMINATED BY ',' ENCLOSED BY '"'
  IGNORE 1 LINES;

3Verify

Run the load again and confirm that the row count matches the file.

SELECT COUNT(*) FROM places;

Confirmed by dev.mysql.com

Did this fix it?

Cause 2 of 2

The client connection character set does not match what it sends

1Check

The server treats incoming statements as being in character_set_client. If the client really sends another encoding, the bytes are misread.

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

2Fix

Make the client declare its true encoding, for the session or in the option file. The mysql client can also pick it up from the operating system settings.

SET NAMES utf8mb4;
-- or in my.cnf:
-- [mysql]
-- default-character-set=utf8mb4

3Verify

Reconnect and send the statement that failed.

SELECT @@character_set_client;

Confirmed by dev.mysql.com

Did this fix it?

How do I stop it coming back?

  • Save exports as UTF-8 and put CHARACTER SET in every LOAD DATA statement, so the file is never read with an accidental default.
  • Set default-character-set in the client option file instead of relying on detection.
  • Keep the table, database and connection on utf8mb4.

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.