Fix MySQL Charset and Collation Mismatches

A charset or collation mismatch in MySQL shows up as Illegal mix of collations errors, question marks where accents should be, or a join that fails between two tables that look identical. The fix is to find which layer is wrong — server, database, table, column or connection — and correct that layer only. This guide walks through the diagnosis and the repair, in the order that avoids damaging your data.

Before you start

You need:

• A Linux VPS running MySQL 8.0/8.4 or MariaDB 10.x/11.x. Check with mysql --version.
• SSH access to the server. The examples use deploy@203.0.113.10.
• A MySQL account with ALTER privileges on the affected database, or root.
• A current backup. Character set conversions rewrite table data, and a wrong conversion is hard to undo.

Take the backup first:

ssh deploy@203.0.113.10
mysqldump --default-character-set=utf8mb4 -u root -p \
  --single-transaction --routines --triggers \
  appdb > /root/appdb-backup.sql

Find where the mismatch is

Start at the top and work down. Server defaults first:

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

The values that matter are character_set_server, collation_server, character_set_database and collation_database. On MySQL 8.0 and later the defaults are usually utf8mb4 and utf8mb4_0900_ai_ci. MariaDB typically uses utf8mb4_general_ci or a uca1400 collation. Check your own server's documentation for the exact default in your version.

Next, look at the schema objects:

SHOW CREATE DATABASE appdb;
SHOW CREATE TABLE appdb.orders;

Then list every column that is not utf8mb4:

SELECT table_schema, table_name, column_name, character_set_name, collation_name
FROM information_schema.columns
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema','sys')
  AND character_set_name IS NOT NULL
  AND collation_name NOT LIKE 'utf8mb4%'
ORDER BY table_schema, table_name;

Any row this returns is a candidate for the error you are seeing. A single latin1 or utf8mb3 column in a join is enough to trigger Illegal mix of collations (error 1267).

Fix the server defaults

Server defaults only affect objects created after the change. They do not rewrite existing tables.

Find your config file — it is usually /etc/mysql/mysql.conf.d/mysqld.cnf on Ubuntu and Debian, or /etc/my.cnf.d/server.cnf on AlmaLinux and Rocky. See where my.cnf lives if you are unsure.

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

[client]
default-character-set = utf8mb4

Avoid skip-character-set-client-handshake unless you have a specific reason. It forces the server charset onto every client regardless of what the client asks for, which hides connection bugs instead of fixing them.

Fix database and table defaults

Change the database default without touching stored data:

ALTER DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

To rewrite a table and all its text columns:

ALTER TABLE appdb.orders
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CONVERT TO assumes the existing bytes are encoded in the column's current charset. If the data was written by a client using the wrong charset, the bytes are already wrong and this command will not repair them — see the mojibake entry in troubleshooting.

Fix a single column

When only one column is out of line, change just that column:

ALTER TABLE appdb.orders
  MODIFY customer_note VARCHAR(255)
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Keep the column type and length identical to what SHOW CREATE TABLE reports, otherwise you may silently truncate data. If the column is part of an index, the index is rebuilt as part of the change.

Fix the connection charset

A correct schema still produces question marks if the client connects with the wrong charset. Check the current session:

SHOW SESSION VARIABLES LIKE 'character_set%';

character_set_client, character_set_connection and character_set_results should all be utf8mb4. Set them for the current session:

SET NAMES utf8mb4;

Then fix the client permanently:

• PHP PDO: add ;charset=utf8mb4 to the DSN, for example mysql:host=203.0.113.10;dbname=appdb;charset=utf8mb4.
• JDBC: use useUnicode=true&characterEncoding=UTF-8 in the connection URL.
• Command line: pass --default-character-set=utf8mb4 to mysql and mysqldump.
• Python: pass charset='utf8mb4' to the connector.

Fix "Illegal mix of collations" in queries

Error 1267 means two operands in a comparison use different collations. The permanent fix is to align the columns. As a temporary patch you can force a collation in the query:

SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_code = c.code COLLATE utf8mb4_0900_ai_ci;

If the collations differ only in suffix (utf8mb4_general_ci versus utf8mb4_0900_ai_ci), converting the older column to the server default resolves it cleanly.

Check it works

Confirm the schema, the connection and the data:

SHOW CREATE TABLE appdb.orders\G
SHOW SESSION VARIABLES LIKE 'character_set%';
SELECT id, customer_note FROM appdb.orders WHERE id = 1;

Then insert a test row with accented and multi-byte characters:

INSERT INTO appdb.orders (customer_note) VALUES ('Café — naïve 🚀');
SELECT customer_note, HEX(customer_note) FROM appdb.orders ORDER BY id DESC LIMIT 1;

The text should come back exactly as you typed it, with no ? and no é sequences. If it does, the round trip through the connection and the column is correct.

Troubleshooting

• Question marks (?) in stored data — the client sent bytes the connection charset could not represent, and MySQL replaced them at insert time. The original characters are gone; re-import the affected rows from a backup taken with --default-character-set=utf8mb4.
• Mojibake such as é or ’ — double encoding. The text was written as UTF-8 bytes but interpreted as latin1, then stored as UTF-8. Test the repair on a copy: ALTER TABLE t MODIFY col VARBINARY(255); then ALTER TABLE t MODIFY col VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;. The binary step preserves the raw bytes so the second step re-interprets them correctly.
• Incorrect string value during ALTER — the column holds bytes that are not valid in its declared charset. Convert through VARBINARY first, as above, and inspect a few rows before and after.
• Error 1267 returns after you fixed the columns — a literal or a temporary table in the query carries a different collation. Check information_schema.columns again and look for utf8mb3 columns created by an older application migration.
• The application still writes the wrong charset — the connection string was not updated, or a second config file overrides it. Confirm with SHOW SESSION VARIABLES from inside the application, not from your SSH session.

Next steps

• Restore a MySQL database from a mysqldump file when you need to re-import clean data.
• Install and secure MariaDB or MySQL to get the server defaults right from the start.
• Connect MySQL Workbench, HeidiSQL or DBeaver over SSH and set the charset in your GUI client too.
• Create a database and user for a web app with utf8mb4 set correctly on day one.

If the conversion is part of a wider cleanup, a Linux VPS gives you root access to edit config files and restart the service whenever you need.