MySQL "Unknown Collation" Error After Migration: Causes & Fix

You restore a database dump on a cPanel account or a fresh VPS, and instead of your site coming back up, phpMyAdmin or the MySQL CLI throws a wall of errors mentioning a collation nobody asked for — something like utf8mb4_0900_ai_ci. The import either aborts halfway or "succeeds" while quietly skipping table after table. Here's exactly why this happens and how to fix it without touching your data.

Symptom: What You'll Actually See

The error usually shows up in one of these forms, depending on how you're importing:

• phpMyAdmin import: #1273 - Unknown collation: 'utf8mb4_0900_ai_ci', repeated once per table.
• MySQL CLI / SSH import: ERROR 1273 (HY000) at line 42: Unknown collation: 'utf8mb4_0900_ai_ci', and the import stops dead at that line.
• WordPress migration plugins (All-in-One WP Migration, Duplicator, UpdraftPlus): the restore finishes but tables are missing or the site throws "table doesn't exist" errors afterward — the plugin silently dropped anything it couldn't create.
• WP-CLI: wp db import exits with the same 1273 error and a non-zero exit code.

Cause: Your Source and Destination Speak Different MySQL

This isn't corruption and it isn't a permissions issue — it's a version mismatch. utf8mb4_0900_ai_ci is the default collation MySQL 8.0 has used since 2018. It's tied to a newer Unicode collation algorithm (UCA 9.0.0) that MariaDB doesn't implement the same way, so any MariaDB version below 10.10 — which covers the vast majority of cPanel/WHM servers, since cPanel has shipped MariaDB rather than MySQL for years — simply has no idea what that collation name means.

You'll hit this constantly when:

• You export a WordPress or app database from a local dev environment (Docker's mysql:8 image, MAMP, XAMPP with MySQL 8) and import it into cPanel hosting.
• You migrate off a host or VPS running MySQL 8 onto a cPanel server running MariaDB.
• A developer's laptop uses MySQL 8 by default while the production server was provisioned years ago on MariaDB 10.3 or 10.4.

Run mysql --version or check phpMyAdmin → Server → Variables → version on the destination. If it says MariaDB and the version is below 10.10, that's your answer.

Fix 1: Clean the Dump File Before Importing (Best for a One-Time Migration)

If you haven't imported yet, or the import partially failed, the cleanest fix is to rewrite the .sql file so it never mentions the unsupported collation. MariaDB's utf8mb4_general_ci is the closest safe equivalent for almost every real-world use case (it isn't byte-identical in sort order, but it's fine for the vast majority of sites — see the note on sorting below).

On Linux or macOS, run this against the dump before importing:

sed -i \
  -e 's/utf8mb4_0900_ai_ci/utf8mb4_general_ci/g' \
  -e 's/utf8mb4_0900_as_cs/utf8mb4_general_ci/g' \
  -e 's/ CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci/ CHARACTER SET utf8mb4/g' \
  database_dump.sql

On Windows, do the same replacement in Notepad++ (Find & Replace → Replace All, with "Search Mode: Extended") or through WSL. Then import the cleaned file:

mysql -u cpaneluser_dbuser -p cpaneluser_dbname < database_dump.sql

Or through phpMyAdmin: Import → Choose File → Go, uploading the cleaned database_dump.sql instead of the original.

Fix 2: Already Imported and Missing Tables? Check What Landed

If a migration plugin ran and "finished" without telling you it skipped anything, compare what you expected against what's actually there:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'cpaneluser_dbname';

Cross-check the count against your source database. If tables are missing, don't re-run the plugin and hope — go back to Fix 1, export a fresh dump from the source, clean it, and import it manually. Migration plugins rarely surface the real MySQL error; they just fail quietly.

Fix 3: Table or Database Already Exists but Has the Wrong Collation

Sometimes the import succeeds (because MariaDB 10.6+ can occasionally coerce the name), but text sorting and comparisons behave oddly afterward — case-sensitive searches, broken ORDER BY, WooCommerce or WordPress search returning nothing. Convert the whole database in place:

ALTER DATABASE cpaneluser_dbname
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

Then convert each table individually (this rewrites the collation of every text column, so back up first):

ALTER TABLE wp_posts
  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

For a WordPress site with dozens of tables, WP-CLI does this in one pass instead of table by table:

wp db query "SELECT CONCAT('ALTER TABLE ', table_name,
  ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
  FROM information_schema.tables WHERE table_schema = DATABASE();" | \
  tail -n +2 > /tmp/convert.sql
wp db query < /tmp/convert.sql

Collation Compatibility at a Glance

Prevention: Stop This at Export Time

• Check the destination version first. Before migrating, look up your cPanel server's MariaDB/MySQL version in WHM → Server Information, or ask your host. If it's MariaDB below 10.10, export with a compatible collation from the start.
• Export with an explicit collation. From a MySQL 8 source: mysqldump --default-character-set=utf8mb4 -u user -p dbname > dump.sql, then run the sed cleanup above as a habit — it costs nothing and prevents the failure entirely.
• Set the collation in your app before you ever create tables — in WordPress, wp-config.php's DB_COLLATE constant, or your framework's database config — so new installs never pick up MySQL 8 defaults in the first place.
• Test the import on a staging database if you're migrating a large production site. A failed import against a live database left half-created objects is a worse afternoon than a failed import against a throwaway staging DB.

Once the import completes cleanly, spot-check a page that does text search or sorting (a WooCommerce product search, a WordPress archive page) to confirm the collation swap didn't change how results are ordered in a way your users would notice.