Fixing Unknown Collation utf8mb4_0900_ai_ci During Import

An SQL dump that stops with Unknown collation: 'utf8mb4_0900_ai_ci' is not necessarily damaged. The destination may not recognize the comparison rule specified by the source. phpMyAdmin displays the database error; raising the upload limit does not fix it.

A character set defines which characters can be represented. A collation defines comparison and sorting behavior. Replacing a collation name is therefore more than a spelling change: it can affect searches, username equality, and unique indexes.

Ask both servers what they support

Run these queries separately on the source and destination. Record the database server version, not just the hosting panel or client version. MySQL and MariaDB do not share one version-numbering scheme.

SELECT VERSION(), @@version_comment;
SHOW COLLATION WHERE Collation = 'utf8mb4_0900_ai_ci';
SHOW COLLATION WHERE Collation = 'utf8mb4_unicode_ci';

If the first collation query returns no rows on the destination, that name is unsupported there. A matching row does not prove that every other feature in the dump is compatible. MariaDB support and aliases vary between versions, so avoid blanket claims that it can never recognize a particular name.

Search the dump for the exact failing name. It may appear on individual columns, database definitions, or expressions as well as table defaults. Changing the destination database default does not override a COLLATE clause explicitly written in CREATE TABLE.

Why SET NAMES does not fix this error

SET NAMES utf8mb4 and the client's --default-character-set=utf8mb4 concern connection encoding. They do not add an unknown collation to the server. Resaving the file in a different encoding is also unrelated and can introduce real character corruption.

Resolve schema compatibility first. If imported text later appears garbled, investigate that separately with the PHP/MySQL encoding workflow. Changing several layers together makes it difficult to determine what fixed or damaged the data.

Compatible destination or adapted schema?

If the application depends on the source comparison behavior, a maintained destination that supports it may require fewer changes. That is not a reason to upgrade production immediately: test application drivers, queries, and backup tools beforehand.

If the destination cannot change, select a supported alternative deliberately. utf8mb4_unicode_ci is a common candidate, but it is not identical to utf8mb4_0900_ai_ci. Test language-specific characters, including Turkish I/ı/İ/i where relevant, with representative data. Sharing the utf8mb4 prefix does not make all equality and whitespace rules equivalent.

Trailing spaces can cause a unique-key conflict

Run this example on a MySQL server that supports both collations. In MySQL 8.4, the first expression returns 0 and the second returns 1:

SELECT
  (_utf8mb4'a' COLLATE utf8mb4_0900_ai_ci =
   _utf8mb4'a ' COLLATE utf8mb4_0900_ai_ci) AS newer_rule,
  (_utf8mb4'a' COLLATE utf8mb4_unicode_ci =
   _utf8mb4'a ' COLLATE utf8mb4_unicode_ci) AS older_rule;

Two values distinct on the source can become equal on the destination, preventing a unique index from being populated. Inspect usernames, slugs, email addresses, and product codes rather than only checking that text looks correct on screen.

The following assumes a separate staging table with a utf8mb4 label column and no unique constraint on candidate values. It detects groups that the proposed collation considers equal; it does not merge them:

SELECT label COLLATE utf8mb4_unicode_ci AS candidate, COUNT(*) AS total
FROM import_check
GROUP BY label COLLATE utf8mb4_unicode_ci
HAVING COUNT(*) > 1;

Change only the intended schema definitions

1. Keep the original dump and work on a copy.
2. Separate schema and data exports when practical; list affected tables and columns.
3. Edit the relevant DDL definitions. A blind file-wide replacement can alter stored article text too.
4. Import into an empty restricted test area without removing unique indexes to suppress errors.
5. Compare ordering, equality, uniqueness, and application search behavior.

Record the old and new collation and affected columns. If 1062 appears, examine the conflicting records instead of silently skipping them.

References: MySQL collation effects and MariaDB supported collations.