Error
Error Code:
1253
MariaDB Error 1253: Collation-Character Set Mismatch
Description
This error indicates that the specified collation is not compatible with the character set it's being paired with. It typically occurs when creating or altering database objects (tables, columns) or setting session/global character set variables.
Error Message
COLLATION '%s' is not valid for CHARACTER SET '%s'
Known Causes
4 known causesIncorrect Schema Definition
Attempting to create or alter a table or column with a collation that does not belong to the declared character set.
Invalid Configuration Setting
Setting session or global character set and collation variables (e.g., character_set_server, collation_database) to incompatible values.
Typo or Non-existent Name
A misspelling or reference to a character set or collation name that is not recognized or supported by the MariaDB server.
Environment Mismatch
Importing a database schema or data from an environment where character sets or collations might differ or be deprecated in the current MariaDB version.
Solutions
3 solutions available1. Correcting Table or Column Collation and Character Set medium
Alter the table or specific column to use a compatible collation for the desired character set.
1
Identify the character set and collation that are causing the mismatch. The error message usually provides this information. For example, if you see `COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'`, then `'utf8mb4_general_ci'` is the problematic collation and `'latin1'` is the character set.
2
Determine the correct collation for your desired character set. You can list available collations for a character set using the following SQL command:
SHOW COLLATION WHERE Charset = '<desired_character_set>';3
Once you have identified a compatible collation (e.g., `utf8mb4_unicode_ci` for `utf8mb4`), alter the table or the specific column to use it. If the mismatch is at the table level, alter the entire table. If it's on a specific column, alter that column.
ALTER TABLE <your_table_name> CONVERT TO CHARACTER SET <desired_character_set> COLLATE <compatible_collation>;
-- Or, to alter a specific column:
ALTER TABLE <your_table_name> MODIFY <your_column_name> <column_type> CHARACTER SET <desired_character_set> COLLATE <compatible_collation>;4
If you are unsure about the `<column_type>` for a specific column, you can first check its current definition:
SHOW CREATE TABLE <your_table_name>;2. Setting Default Character Set and Collation for New Tables medium
Configure MariaDB to use compatible defaults for newly created tables to prevent future mismatches.
1
Edit the MariaDB configuration file. The location varies by operating system. Common locations include `/etc/my.cnf`, `/etc/mysql/my.cnf`, or files within `/etc/mysql/conf.d/`.
2
Locate the `[mysqld]` section of the configuration file. If it doesn't exist, create it.
3
Add or modify the following lines to set the desired default character set and a compatible collation. For example, to set `utf8mb4` as the default with a common collation:
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci4
Save the configuration file.
5
Restart the MariaDB service for the changes to take effect.
sudo systemctl restart mariadb3. Applying Correct Settings During Table Creation easy
Specify the character set and collation explicitly when creating new tables.
1
When writing `CREATE TABLE` statements, always include the `CHARACTER SET` and `COLLATE` clauses to ensure compatibility.
CREATE TABLE <your_table_name> (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;2
If you are creating tables through an application or ORM, ensure that the connection parameters or configuration for the database driver specify the desired character set and collation.