How to fix ERROR 1253: COLLATION not valid for CHARACTER SET in MySQL

MySQLINTERMEDIATEMEDIUMUpdated Dec 25, 2025· 212 views

This error occurs when you try to use a collation that is incompatible with the specified character set. MySQL requires that each collation belongs to exactly one character set. Fix it by ensuring your character set and collation are compatible and consistent across your database.

Quick fix

Fixes ERROR 1253: COLLATION not valid for CHARACTER SET

SHOW VARIABLES LIKE '%char%';
SHOW VARIABLES LIKE '%collation%';
  1. Check current server character set and collation settings
  2. Verify your table and column character sets
  3. Convert problematic columns or strings to compatible collation
  4. Fix stored procedures with mismatched collations
mysql — error
SHOW VARIABLES LIKE '%char%';
SHOW VARIABLES LIKE '%collation%';
ERROR 1253: COLLATION not valid for CHARACTER SET

What this error means

ERROR 1253 (ER_COLLATION_CHARSET_MISMATCH) is thrown when MySQL detects that you are trying to apply a collation to a character set that does not support it. Each character set in MySQL has a specific set of valid collations—you cannot mix them. For example, the collation utf8_unicode_ci belongs to the utf8 character set, not utf8mb4. Similarly, collations like latin2_bin cannot be used with latin1 character sets. This mismatch typically happens during queries, stored procedure definitions, imports, or schema migrations where the character set and collation settings are inconsistent.

Typical symptoms

  • Query execution fails with "ERROR 1253" when using COLLATE clause
  • Importing or restoring SQL dump files fails at specific tables
  • Stored procedures or functions fail to compile or execute
  • Column or table creation fails with collation specification
  • Cross-character set comparisons produce the error

Common causes

  1. Using a collation that does not belong to the specified character set (e.g., utf8_general_ci with utf8mb4)
  2. Importing a database dump that uses different character sets and collations than your MySQL server default
  3. Changing MySQL versions where default character sets changed (e.g., upgrading from MySQL 5.7 to 8.0)
  4. Inconsistent collation settings across database, tables, columns, and stored procedures
  5. Applying COLLATE in queries or stored procedures to incompatible character sets (e.g., COLLATE on numeric or different character set expressions)

How to fix "ERROR 1253: COLLATION not valid for CHARACTER SET"

1Check current server character set and collation settings

Run these commands to see what your MySQL server is configured to use:

sql
SHOW VARIABLES LIKE '%char%';
SHOW VARIABLES LIKE '%collation%';

Look for variables like character_set_server, collation_server, character_set_database, and collation_database. Record these values.

2Verify your table and column character sets

Check the character set and collation of the specific table causing the error:

sql
SHOW CREATE TABLE your_table_name;

Or for more detailed column information:

sql
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'your_table_name';

Note any mismatches between the declared collation and its character set.

3Convert problematic columns or strings to compatible collation

If the issue is in a query, use the CONVERT() function or CAST() to ensure compatibility:

sql
-- Using CONVERT to change both character set and collation
SELECT CONVERT(your_column USING utf8mb4) COLLATE utf8mb4_unicode_ci FROM your_table;

-- Or cast to the correct character set
SELECT CAST(your_column AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_unicode_ci FROM your_table;

Make sure the collation you specify (e.g., utf8mb4_unicode_ci) belongs to the character set you're converting to.

4Fix stored procedures with mismatched collations

If the error occurs in a stored procedure, declare all parameters and local variables with consistent collations:

sql
DELIMITER //

CREATE PROCEDURE your_procedure(
  IN param1 VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
)
BEGINS
  DECLARE local_var VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  
  -- Procedure body here
END//

DELIMITER ;

Ensure all RETURN statements and string literals also use compatible character sets and collations.

5Fix import/migration errors by updating the dump file

If importing a SQL dump is causing the error, edit the dump file and find incompatible collation declarations. Common fixes:

sql
-- Replace incompatible collations with compatible ones
-- Find: COLLATE utf8_general_ci (with utf8mb4)
-- Replace: COLLATE utf8mb4_unicode_ci

-- Or normalize to utf8mb4_unicode_ci across the entire dump
-- Find: CHARACTER SET utf8 COLLATE utf8_unicode_ci
-- Replace: CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Use your editor's Find and Replace to fix these systematically before importing.

6Alter existing tables to use compatible collations

To fix tables already in your database, alter them to use a consistent character set and collation:

sql
-- Change the entire table
ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Or change individual columns
ALTER TABLE your_table_name
MODIFY COLUMN your_column VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note: CONVERT TO changes all string columns in the table. Be cautious with large tables as this can take time.

7Update your MySQL configuration for consistency

To prevent this error going forward, set consistent defaults in your MySQL configuration file (my.cnf on Linux/Mac, my.ini on Windows):

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

After updating the configuration, restart MySQL:

bash
# Linux/Mac
sudo systemctl restart mysql

# Or using mysqld directly
mysqld_safe

Any new databases and tables created after this will use the correct defaults.

Advanced notes

MySQL version differences can affect default character sets. MySQL 5.7 uses utf8 (limited to 3 bytes) by default, while MySQL 8.0 uses utf8mb4 (4 bytes, supporting full Unicode including emoji). When migrating between versions, always verify collation compatibility. The utf8mb4_unicode_ci collation is recommended for most applications as it properly supports the full Unicode character set. For raw ASCII or latin1-only data, utf8mb4_general_ci offers slightly better performance with minimal compatibility concerns. If you encounter "Unknown collation" errors, ensure both the collation name and character set exist in your MySQL version—use SHOW CHARACTER SET and SHOW COLLATION to list available options.

Frequently asked questions

What does "ERROR 1253: COLLATION not valid for CHARACTER SET" mean?
ERROR 1253 (ER_COLLATION_CHARSET_MISMATCH) is thrown when MySQL detects that you are trying to apply a collation to a character set that does not support it. Each character set in MySQL has a specific set of valid collations—you cannot mix them. For example, the collation utf8_unicode_ci belongs to the utf8 character set, not utf8mb4.
How do I fix "ERROR 1253: COLLATION not valid for CHARACTER SET"?
This error occurs when you try to use a collation that is incompatible with the specified character set. MySQL requires that each collation belongs to exactly one character set. Fix it by ensuring your character set and collation are compatible and consistent across your database.
What causes "ERROR 1253: COLLATION not valid for CHARACTER SET"?
Using a collation that does not belong to the specified character set (e.g., utf8_general_ci with utf8mb4) Importing a database dump that uses different character sets and collations than your MySQL server default Changing MySQL versions where default character sets changed (e.g., upgrading from MySQL 5.7 to 8.0)
Is the "ERROR 1253: COLLATION not valid for CHARACTER SET" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading