Fixing MySQL Error #1273: Unknown Collation 'utf8mb4_unicode_520_ci'

The Problem

You’re in the middle of migrating a WordPress site from a local environment like LocalWP or XAMPP to a live server. Everything seems fine until you hit the 'Import' button in phpMyAdmin. Suddenly, the process grinds to a halt with this frustrating message:

#1273 - Unknown collation: 'utf8mb4_unicode_520_ci'

This error stops the database creation in its tracks. Instead of a working website, you’re left with an empty database and the dreaded "Error establishing a database connection" screen.

Why This Happens

The culprit is a version gap between your source and destination servers. The utf8mb4_unicode_520_ci collation was introduced with MySQL 5.6 and MariaDB 10.1 to support the Unicode 5.2.0 standard. It's the modern standard for handling emojis and complex characters.

If your local environment uses MySQL 8.0 but your live host is still running a legacy version like MySQL 5.5 (which is over a decade old), the server won't recognize the "520_ci" instruction. It simply doesn't have that collation in its library.

Fix 1: Manual Search and Replace (Best for files under 100MB)

If you already have your .sql file, you don't need to re-export it. You can strip the incompatible collation using a high-quality text editor like VS Code, Sublime Text, or Notepad++.

The Steps:

• Open your .sql file in your editor.
• Press Ctrl + H (or Cmd + H on Mac) to trigger the Find and Replace tool.
• Search for: utf8mb4_unicode_520_ci
• Replace it with: utf8mb4_unicode_ci
• Hit Replace All and save the file.

Does it still fail? Some very old servers don't even support utf8mb4. If the error persists, try replacing utf8mb4 with utf8 and utf8mb4_unicode_ci with utf8_general_ci. This downgrades the database to a format compatible with nearly every server since 2005.

# Before:
CREATE TABLE `wp_posts` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci;

# After:
CREATE TABLE `wp_posts` (
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Fix 2: Exporting in Compatibility Mode

If you still have access to the original site, you can tell phpMyAdmin to export a file that plays nice with older servers.

• Open phpMyAdmin on your source server.
• Click the Export tab.
• Choose Custom - display all possible options.
• Find the Format-specific options section.
• Change the setting for "Database system or older MySQL server to maximize output compatibility with:" from NONE to MYSQL40.
• Scroll down and click Go.

This forces the export to use older, more universal syntax, effectively bypassing the #1273 error during the import phase.

Fix 3: Using the Command Line (Best for 500MB+ Databases)

Large SQL files will crash standard text editors. If you're dealing with a massive database, use the sed command to swap the strings directly in the terminal.

# Run this to replace the collation string instantly
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' database_dump.sql

Once the command finishes, import the file using WP-CLI for maximum speed:

wp db import database_dump.sql

Double-Checking the Results

After the import finishes, take 60 seconds to verify that your data survived the "downgrade." Look for these three things:

• Table Structure: Browse the wp_posts table in phpMyAdmin. If the rows load, the collation was accepted.
• Site Functionality: Click through your WordPress pages. Check that your images and menus appear correctly.
• Special Characters: Look for "broken" text like é instead of é. If you see these, you may have downgraded too far to utf8_general_ci when your content actually required utf8mb4.

How to Prevent This Next Time

The easiest way to avoid this is to keep your development and production environments in sync. If your host uses MySQL 5.5, don't use MySQL 8.0 for local development.

Alternatively, use a migration plugin like WP Migrate Lite or All-in-One WP Migration. These tools are smart enough to detect server versions and handle collation conversions automatically, so you never have to touch a line of SQL code.