How to fix ERROR 1273: Unknown collation in MySQL

First, verify which MySQL version you're running:

mysql --version

Also check the version of the server you're importing to:

SELECT VERSION();

If the source version (where the dump was created) is newer than the target version, you'll need to replace the unsupported collations.

Open your SQL dump file and search for collation declarations. Common unsupported collations are:

- utf8mb4_0900_ai_ci (MySQL 8.0.1+)
- utf8mb4_unicode_520_ci (MySQL 5.6+, not in 5.5)

You can search in your text editor using find (Ctrl+F or Cmd+F):

SEARCH FOR: utf8mb4_0900_ai_ci
SEARCH FOR: utf8mb4_unicode_520_ci

Use the sed command to replace all instances of the unsupported collation:

# Replace utf8mb4_0900_ai_ci with utf8mb4_unicode_ci
sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

# Or replace utf8mb4_unicode_520_ci with utf8mb4_unicode_ci
sed -i 's/utf8mb4_unicode_520_ci/utf8mb4_unicode_ci/g' dump.sql

Note: On macOS, use sed -i '' instead of sed -i:

sed -i '' 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' dump.sql

If you're on Windows or prefer a graphical editor:

1. Open your SQL dump file in a text editor (Notepad++, VS Code, etc.)
2. Use Find and Replace (Ctrl+H or Cmd+H)
3. Find: utf8mb4_0900_ai_ci
4. Replace with: utf8mb4_unicode_ci
5. Click "Replace All"
6. Save the file

Alternatively, if replacing with utf8mb4_unicode_ci causes issues, try utf8mb4_general_ci for maximum compatibility.

Now import your modified dump file:

mysql -u username -p database_name < dump.sql

Or via MySQL client:

SOURCE /path/to/dump.sql;

If the import succeeds, the error is resolved.

After successful import, verify the collation was applied correctly:

-- Check database collation
SHOW CREATE DATABASE database_name;

-- Check table collation
SHOW CREATE TABLE table_name;

-- Check column collation
SHOW FULL COLUMNS FROM table_name;

All should show utf8mb4_unicode_ci or your replacement collation.