How to Solve MySQL Error 1273: Unknown Collation 'utf8mb4_0900_ai_ci'

Last update: 06-09-2024

While working on a Laravel project or importing an SQL dump file, you might encounter the error:

SQLSTATE[HY000]: General error: 1273 Unknown collation: 'utf8mb4_0900_ai_ci'

This error occurs because the utf8mb4_0900_ai_ci collation is not supported by your version of MySQL or MariaDB. This collation was introduced in MySQL 8.0, and older versions, including certain versions of MariaDB, do not recognize it.

Understanding the Issue

The utf8mb4_0900_ai_ci collation offers enhanced support for sorting and comparing Unicode characters. However, if you're using a version of MySQL earlier than 8.0 or a version of MariaDB, such as 10.11.7, this collation is unavailable. As a result, attempting to use it in your database configuration or SQL scripts will lead to the "Unknown collation" error.

When Might This Error Occur?

• During Development: If your Laravel project or another application specifies the utf8mb4_0900_ai_ci collation in its configuration or migration files.
• When Importing an SQL Dump: If the SQL dump file was created from a MySQL 8.0 database and contains tables or columns with the utf8mb4_0900_ai_ci collation.

Solving the Error

To resolve this issue, you can use an alternative collation that is supported by both older MySQL versions and MariaDB. A commonly supported collation is utf8mb4_unicode_ci. Here’s how you can make the necessary changes:

1. Update Your Database Configuration

If you're working with Laravel, update the config/database.php file to use utf8mb4_unicode_ci:

'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'forge'),
    'username' => env('DB_USERNAME', 'forge'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

2. Update Your Migration Files

If your migration files specify the utf8mb4_0900_ai_ci collation, update them to use utf8mb4_unicode_ci:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name')->collation('utf8mb4_unicode_ci');
    $table->timestamps();
});

3. Modify the SQL Dump File

If you encounter this error while importing an SQL dump file, open the file in a text editor and replace all instances of utf8mb4_0900_ai_ci with utf8mb4_unicode_ci. You can use a find and replace function to make this process quicker.

Conclusion

By replacing utf8mb4_0900_ai_ci with utf8mb4_unicode_ci in your code or SQL files, you can avoid the "Unknown collation" error and ensure compatibility with your database version.

If you have any questions or run into additional issues, feel free to leave a comment below or reach out.