MySQL rejects text with ERROR 1300: Invalid utf8mb4 character string
ERROR 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
The 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
Statements 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
- 1
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
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=utf8mb43Verify
Reconnect and send the statement that failed.
SELECT @@character_set_client;Confirmed by dev.mysql.com
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:
- TaxiDexMy own multi-tenant taxi dispatch SaaS: API, dispatcher portal, driver and passenger apps, and the servers underneath.
- QistLockA NestJS and React SaaS driving an Android Device Owner agent that locks financed phones when payments lapse.
- ShopGoAn e-commerce aggregator with a Python scraping engine behind its catalog, past 10K Google Play downloads.
Related fixes
Databases
MySQL rejects text with 'Incorrect string value' (emoji and other characters)
ERROR 1366 (HY000): Incorrect string value
MySQL raises this when a value has bytes the column's character set cannot store.
3 causes, each with a fixDatabases
Installing the Airflow MySQL provider fails on pkg-config or mysqlclient
Building the mysqlclient wheel fails during the install
The Airflow MySQL provider depends on mysqlclient, which pip compiles against the MySQL C client library.
2 causes, each with a fixDatabases
A query on Aurora MySQL dies with 'table /rdsdbdata/tmp/#sql... is full'
ERROR 1114 (HY000): The table '/rdsdbdata/tmp/#sqlfd_1586_2' is full
In Aurora MySQL 3, internal temporary tables share one pool, and when that pool and the memory-mapped overflow file are used up, a reader instance ends the query with error 1114.
3 causes, each with a fixDatabases
Azure Database for MySQL refuses a connection that does not use SSL
ERROR 3159 (HY000): Connections using insecure transport are prohibited while --require_secure_transport=ON.
Azure Database for MySQL Flexible Server requires encrypted connections by default (require_secure_transport = ON, TLS 1.2).
4 causes, each with a fixDatabases
Compiling a C or C++ program that includes mysql.h fails
undefined reference to `mysql_init'
The compiler only finds mysql.h when the MySQL client development files are installed and their folder is on the include path.
3 causes, each with a fixDatabases
DBeaver fails to resolve Maven dependencies when downloading a database driver
A driver library does not download automatically
DBeaver downloads driver jars from a Maven repository.
3 causes, each with a fix