Fixing MySQL ERROR 1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'

The Shortcut: A Quick Search-and-Replace

The most effective fix is to swap the incompatible collation strings in your .sql file for a version your older server actually recognizes.

• Open your SQL dump file using a code editor or a command-line tool.
• Search for all instances of utf8mb4_0900_ai_ci and replace them with utf8mb4_unicode_ci.
• Look for utf8mb4_0900_as_ci and replace those with utf8mb4_unicode_ci as well.
• Save the file and restart your import process.

Why Does This Error Happen?

With the release of version 8.0, MySQL updated its default character set to utf8mb4 and its default collation to utf8mb4_0900_ai_ci. The "0900" refers to the Unicode Collation Algorithm (UCA) 9.0.0. The "ai" indicates that the sorting is accent-insensitive.

Legacy versions like MySQL 5.7 were built years before these standards existed. They simply don't have the library files to understand the "0900" instructions. When your export file tells an old server to create a table using these new rules, the server gives up and throws Error 1273.

Three Ways to Fix It

Method 1: Using the Command Line (Best for Huge Files)

Trying to open a 2GB SQL dump in a standard text editor will likely freeze your computer. Instead, use sed. It processes the file line-by-line without loading the whole thing into memory.

# Replace the 0900 collation with the compatible unicode variant
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' backup.sql

# Also catch the accent-sensitive variant if it exists
sed -i 's/utf8mb4_0900_as_ci/utf8mb4_unicode_ci/g' backup.sql

Note: If you are on macOS, the syntax is slightly different: sed -i '' 's/.../.../g' backup.sql.

Method 2: Using VS Code or Notepad++

If your file is small—say, under 50MB—a visual editor is often easier.

• Open the .sql file in VS Code or Notepad++.
• Press Ctrl + H (or Cmd + Option + F on Mac) to open the Replace tool.
• Find: utf8mb4_0900_ai_ci
• Replace with: utf8mb4_unicode_ci
• Click Replace All and save the file.

Method 3: Proactive Exporting

If you can still access the source database, you can try to export it with better compatibility settings. In phpMyAdmin, go to the "Export" tab and choose "Custom." Under the "Format-specific options," look for the dropdown labeled "Database system or older MySQL server to maximize output compatibility with." Selecting MYSQL40 is a common trick, though manual string replacement remains the most reliable method for 5.7 migrations.

Checking Your Work

After modifying the file, run your import command again:

mysql -u username -p database_name < backup.sql

Once it finishes, log into your MySQL console to verify the tables. Run SHOW TABLE STATUS; and check the Collation column. It should now list utf8mb4_unicode_ci. Finally, browse your site to ensure that emojis and special characters like é or ñ are rendering correctly.

A Note on Older Versions

If you are migrating to a very old server (pre-MySQL 5.5.3), even utf8mb4 won't work. In those rare cases, you must replace utf8mb4 with utf8 and utf8mb4_unicode_ci with utf8_unicode_ci. For almost all modern hosting environments, however, utf8mb4_unicode_ci is the safest and most compatible choice.