📋

Description

This error indicates that the default collation specified in a database, table, or column definition is not valid or recognized by the MySQL server. It typically occurs when an unsupported collation name is provided where `utf8mb4_0900_ai_ci` or `utf8mb4_general_ci` is expected for the `utf8mb4` character set.

💬

Error Message

Invalid default collation %s: utf8mb4_0900_ai_ci or utf8mb4_general_ci expected

🔍

Known Causes

3 known causes

⚠️

Unsupported Collation

The collation name provided in a `CREATE DATABASE`, `CREATE TABLE`, or `ALTER TABLE` statement is not a valid or recognized collation for the specific MySQL server version.

⚠️

Typo in Collation Name

A simple spelling mistake or incorrect capitalization in the specified collation name prevents MySQL from recognizing it as valid.

⚠️

MySQL Version Mismatch

Attempting to use a collation that is only supported in newer MySQL server versions on an older, incompatible server instance.

🛠️

Solutions

3 solutions available

1. Set a Valid Default Character Set and Collation in `my.cnf` medium

Configure MySQL server to use a supported default character set and collation.

1

Locate your MySQL configuration file. This is typically named `my.cnf` or `my.ini` and can be found in directories like `/etc/mysql/`, `/etc/`, or within your MySQL installation directory.

2

Edit the configuration file using a text editor. You might need root or administrator privileges.

3

Add or modify the `character-set-server` and `collation-server` settings under the `[mysqld]` section. Ensure you are using a collation that is compatible with your MySQL version and the expected ones (e.g., `utf8mb4_0900_ai_ci` or `utf8mb4_general_ci`). If you're unsure, `utf8mb4` with `utf8mb4_unicode_ci` is a widely supported and good general-purpose choice.

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

4

Save the changes to the configuration file.

5

Restart the MySQL server for the changes to take effect.

sudo systemctl restart mysql  # For systemd-based systems (e.g., Ubuntu 15.04+, CentOS 7+)
sudo service mysql restart      # For older init.d systems

2. Alter Existing Databases and Tables to Use a Valid Collation medium

Manually change the collation of affected databases and tables.

1

Connect to your MySQL server using a client (e.g., `mysql` command-line tool, MySQL Workbench).

mysql -u your_username -p

2

Identify the databases and tables that are causing the error. You can often find this information in the error logs or by inspecting the table definitions.

3

For each affected database, alter its default collation. Replace `your_database_name` and `valid_collation` with the actual names.

ALTER DATABASE your_database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

4

For each affected table within those databases, alter its collation. Replace `your_table_name` and `valid_collation`.

ALTER TABLE your_table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

5

You might need to re-create or alter indexes if they were created with the invalid collation. This is less common but possible.

3. Update MySQL Server Version advanced

Upgrade to a MySQL version that supports the desired collation.

1

Check your current MySQL server version. The error message implies you might be using a version that doesn't fully support the specified collation (e.g., `utf8mb4_0900_ai_ci` is typically associated with MySQL 8.0+).

SELECT VERSION();

2

Consult the MySQL documentation for the collation support in different versions. If your version is significantly older, upgrading might be the most robust solution.

3

Plan and execute a MySQL server upgrade. This is a significant operation and should be performed with caution, including backups.

4

After upgrading, re-apply Solution 1 (Set a Valid Default Character Set and Collation in `my.cnf`) to ensure the new version is configured correctly.

🔗

Related Errors

5 related errors

MySQL Error 3721: Invalid Default Collation