📋

Description

This error indicates that MySQL attempted to convert a string from one character set or collation to another but failed. This typically occurs during data insertion, updates, or when performing operations that involve strings with incompatible character encodings.

💬

Error Message

Cannot convert string '%s' from %s to %s

🔍

Known Causes

4 known causes

⚠️

Incompatible Character Sets

Data is being inserted or updated into a column, table, or database that has a different character set or collation than the incoming string, leading to an unconvertible character.

⚠️

Client-Server Encoding Conflict

The client application's character set (e.g., character_set_client) differs from the server's expected character set, causing incorrect interpretation and conversion issues.

⚠️

Non-Representable Characters

The string contains characters that simply cannot be represented in the target character set, even with proper conversion settings, such as trying to store a complex Unicode character in a 'latin1' column.

⚠️

Connection Collation Discrepancy

The 'character_set_connection' or 'collation_connection' settings for the current session do not match the character sets of the data being processed, leading to conversion failures.

🛠️

Solutions

3 solutions available

1. Adjust Table/Column Character Set and Collation medium

Ensure the character set and collation of the table or specific column match the encoding of the data being inserted or queried.

1

Identify the character encoding of the string causing the error and the current character set/collation of the target table or column.

2

If the source string encoding is incompatible with the table/column, alter the table or column to use a compatible character set and collation. For example, if you're inserting UTF-8 data and the table uses Latin1, change to utf8mb4.

ALTER TABLE your_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- Or for a specific column:
ALTER TABLE your_table MODIFY your_column VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

3

Re-run the operation that caused the error.

2. Explicitly Cast or Convert String Encoding easy

Force the conversion of the string to the expected character set before it reaches MySQL.

1

Locate the query or application code that is attempting to insert or manipulate the problematic string.

2

Before sending the string to MySQL, convert it to the character set that your MySQL server or target column expects. If the string is in a known encoding (e.g., Latin1) and you need to insert it into a UTF-8 column, use a conversion function in your application language or a stored procedure.

// Example in Python with SQLAlchemy:
from sqlalchemy import cast, String
from sqlalchemy.dialects.mysql import CHARSET, COLLATE

# Assuming 'problematic_string' is your string variable and 'utf8mb4' is the target
converted_string = cast(problematic_string, String(charset=CHARSET.UTF8MB4, collation=COLLATE.UTF8MB4_UNICODE_CI))
# Then use 'converted_string' in your insert/update statement

3

Alternatively, you can try to force conversion within SQL if you're certain of the source encoding and target encoding.

INSERT INTO your_table (your_column) VALUES (CONVERT(_utf8mb4 FROM latin1 'your_string_here'));

4

Execute the modified query.

3. Check MySQL Server Configuration medium

Verify that the MySQL server's default character set and collation are appropriate for the data being handled.

1

Connect to your MySQL server using a client like the `mysql` command-line tool or MySQL Workbench.

2

Check the current server character set and collation settings.

SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

3

If these defaults are not suitable (e.g., they are set to `latin1` and you are consistently dealing with `utf8mb4` data), modify the MySQL configuration file (`my.cnf` or `my.ini`).

[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

4

Restart the MySQL server for the changes to take effect.

# On Linux systems:
sudo systemctl restart mysql
# Or:
sudo service mysql restart

5

Re-run the problematic operation.

🔗

Related Errors

5 related errors

MySQL Error 3854: String Character Conversion Failed