📋

Description

MySQL Error 1300 indicates that the database encountered a character string it cannot process due to an invalid character or incorrect encoding. This typically occurs when data being inserted, updated, or queried contains characters that are incompatible with the database, table, or column's defined character set.

💬

Error Message

Invalid %s character string: '%s'

🔍

Known Causes

4 known causes

⚠️

Character Set Mismatch

The character set of the data being inserted or processed does not align with the character set configured for the database, table, or specific column.

⚠️

Incorrect Data Encoding

Data contains characters that are not valid within its declared encoding, such as attempting to store UTF-8 characters in a Latin-1 encoded field.

⚠️

Client-Server Encoding Discrepancy

The character set used by the client application differs from the character set expected by the MySQL server, leading to misinterpretation of characters.

⚠️

Legacy Character Set Limitations

Attempting to store modern characters (e.g., emojis, specific international characters) in an older, limited character set like `latin1`.

🛠️

Solutions

3 solutions available

1. Verify and Correct Character Set in Table and Column Definitions medium

Ensure your table and column definitions use a compatible character set that can store the problematic string.

1

Identify the offending table and column. The error message usually provides clues.

2

Check the current character set of the table and column.

SHOW CREATE TABLE your_table_name;

3

If the character set is too restrictive (e.g., latin1) and the string contains characters outside its range (e.g., emojis, non-Latin alphabets), alter the table and column to use a more permissive character set like utf8mb4.

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE your_table_name MODIFY your_column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4

If you are inserting data, ensure the client connection character set also matches or is compatible with the database's character set.

SET NAMES 'utf8mb4';

2. Adjust Client Connection Character Set easy

Set the client's character set to match or be compatible with the database's expected encoding.

1

Before executing queries that involve the problematic string, set the client connection character set. This is especially important for tools like MySQL Workbench, command-line clients, or application connectors.

SET NAMES 'utf8mb4';

2

For command-line clients, you can often set this when connecting.

mysql -u your_user -p --default-character-set=utf8mb4 your_database_name

3

In application code, ensure your database connector is configured to use the correct character set (e.g., 'utf8mb4'). This might involve connection string parameters or specific API calls.

// Example in Python with mysql.connector
config = {
    'user': 'your_user',
    'password': 'your_password',
    'host': 'your_host',
    'database': 'your_database',
    'charset': 'utf8mb4'
}
cnx = mysql.connector.connect(**config)

3. Sanitize or Transcode Input Data medium

Cleanse or convert the input string to a format compatible with the target column's character set.

1

If you cannot alter the database schema or client settings, you may need to process the incoming data before insertion.

2

For strings containing invalid characters for the current encoding, you can attempt to remove or replace them. This is a data loss operation, so use with caution.

UPDATE your_table_name SET your_column_name = REPLACE(your_column_name, '\\xNN', '') WHERE your_column_name LIKE '%\\xNN%'; -- Replace \\xNN with the hex code of the invalid character

3

Alternatively, in your application code, before sending data to MySQL, ensure it's properly encoded to the target character set. Libraries for character set conversion can be used.

// Example in PHP
$valid_string = iconv('UTF-8', 'ISO-8859-1//IGNORE', $invalid_string); // Convert to ISO-8859-1, ignoring invalid chars

🔗

Related Errors

5 related errors

MySQL Error 1300: Invalid Character String Encoding