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.
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.
Run these commands to see what your MySQL server is configured to use:
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.
Check the character set and collation of the specific table causing the error:
SHOW CREATE TABLE your_table_name;Or for more detailed column information:
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.
If the issue is in a query, use the CONVERT() function or CAST() to ensure compatibility:
-- 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.
If the error occurs in a stored procedure, declare all parameters and local variables with consistent collations:
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.
If importing a SQL dump is causing the error, edit the dump file and find incompatible collation declarations. Common fixes:
-- 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_ciUse your editor's Find and Replace to fix these systematically before importing.
To fix tables already in your database, alter them to use a consistent character set and collation:
-- 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.
To prevent this error going forward, set consistent defaults in your MySQL configuration file (my.cnf on Linux/Mac, my.ini on Windows):
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ciAfter updating the configuration, restart MySQL:
# Linux/Mac
sudo systemctl restart mysql
# Or using mysqld directly
mysqld_safeAny new databases and tables created after this will use the correct defaults.
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.
ERROR 1064: You have an error in your SQL syntax
How to fix "ERROR 1064: You have an error in your SQL syntax" in MySQL
ERROR 1054: Unknown column in field list
Unknown column in field list
ER_WINDOW_RANGE_FRAME_NUMERIC_TYPE (3589): RANGE frame requires numeric ORDER BY expression
RANGE frame requires numeric ORDER BY expression in MySQL window functions
CR_ALREADY_CONNECTED (2058): Handle already connected
How to fix "CR_ALREADY_CONNECTED (2058): Handle already connected" in MySQL
ER_WINDOW_DUPLICATE_NAME (3591): Duplicate window name
How to fix ER_WINDOW_DUPLICATE_NAME (3591) in MySQL window functions