WordPress SQL import: resolve “Unknown collation” without altering data

Recognize a collation incompatibility during restoration

A WordPress database import stops with “Unknown collation,” often accompanied by number 1273 and a name such as utf8mb4_0900_ai_ci. The destination server does not recognize the name requested in an SQL statement. This rejection concerns the structure or an expression in the file; reducing its size does not make that collation available.

The goal is a complete restoration on a destination that understands the structure, without arbitrarily changing the content. The first approach preserves the file and uses a compatible destination. The second adapts a copy of its structure, only after checking differences in comparison behavior. Changing the name everywhere in the file is not a neutral choice.

This guide covers an authorized SQL restoration with access to the backup and the host’s database tool. An XML content import follows a different procedure. On a managed plan, check the actual capabilities: table access, creating a test database, and choosing the engine are three different permissions. In multisite, the backup may cover several subsites and shared tables; do not treat it as the database of an isolated site.

Preserve the original file and isolate the test destination

Keep the original SQL backup unchanged, along with its associated files if restoration depends on them. Work on a copy identified by a new name. Keep the full message and stopping point without publishing an excerpt containing personal data, credentials, or configuration values.

Prepare a separate, empty test database with an account restricted to it. Do not rerun the import on a production database containing newer data. Check whether the file includes CREATE DATABASE, USE, or qualified names such as autre_base.table: the name selected in the interface does not neutralize them. If these statements target a different scope, have an appropriate export prepared before execution. Keep the dump outside a public web directory; it contains the site’s data.

An interrupted first import may already have created tables or inserted rows. DDL, meaning structure statements, does not behave as a fully reversible operation in every case. Preserve the state useful for diagnosis, then start again with a clean destination prepared for the test. A Restart command does not guarantee a consistent restoration.

Distinguish the character set from comparison rules

A character set defines how characters are represented. A collation defines, among other things, how they are compared and sorted. Two columns may accept the same text yet compare it differently depending on their collation. Differences involving accents, case, or spaces can become significant for a search or a uniqueness constraint.

The name utf8mb4 is therefore not a replacement collation by itself. Nor should you turn every declaration into utf8 to remove the message: that changes a different aspect of the structure and may reduce the characters that can be represented, depending on the engine and its interpretation of that alias.

Compatibility depends on the engine and its exact version. MariaDB 11.4.5 added MySQL 0900 names as aliases for UCA 14.0 collations. Thus, utf8mb4_0900_ai_ci refers there to utf8mb4_uca1400_nopad_ai_ci, whereas MySQL uses UCA 9.0 rules for that name. An alias makes the file easier to accept without proving that all comparisons will remain identical. Check the name on the actual destination, then check values sensitive to sorting and uniqueness.

Query the destination’s capabilities without changing anything

Open phpMyAdmin from your host’s panel, then select the test database in the left column. The SQL tab lets you run the read-only queries below without changing data. Adapt only the collation name to match the error. The phpMyAdmin version shown on its home screen is the tool’s version; the VERSION() query obtains the SQL engine’s version.

The result identifies the engine and current database; a database shown as NULL is not a selected test database. The collation search should return a row with the expected name. An empty list means that this connection does not recognize the name. If you receive a syntax or permission error, keep it so the context can be checked; do not confuse it with an empty result.

1. Verify the server’s identity and open SQL in the test database.
2. Run both read-only queries and keep their results private.
3. Compare with the source information if it remains available.
4. Record the engine, its version, and the rejected name separately; do not settle for “MySQL compatible.”

SELECT VERSION() AS version_serveur,
       @@version_comment AS moteur,
       DATABASE() AS base_courante;
SHOW COLLATION WHERE Collation = 'utf8mb4_0900_ai_ci';

Locate the rejected declaration in a copy of the file

Open the SQL copy in a text editor suited to its size, without saving it at first. Search for the exact collation name. Examine each context: a table’s default option, a column definition, a database statement, or a specific expression. The name may also appear in a data string without being a structure statement.

Record the table name and the statement to which the tool attributes the error. A line number helps locate it, but a statement may span several lines. Keep the surrounding statement to understand what was requested. Do not disclose the full surrounding text if it contains an insertion of private data.

If the name is empty, misspelled, or replaced by a character set alone, the problem may come from the generator or an incorrect setting rather than an outdated engine. In that case, favor a new, corrected export from the source. If the name is valid at the source but absent at the destination, choose between a compatible destination and a validated structural adaptation.

Choose a destination that recognizes the original structure

The first approach is appropriate when you can prepare a compatible destination in the test hosting environment. Preferring the same engine with the required collations limits comparison differences. Another engine family may accept the name through an alias: in that case, repeat the sorting and uniqueness checks below, even without modifying the dump. Recognizing a collation does not establish full SQL compatibility.

In the database management panel, record the options actually available and have an isolated destination created or prepared according to the account’s permissions. If the version is fixed, ask the provider for a destination that recognizes the specified name. Do not undertake a global production server upgrade merely to perform the first import test.

WordPress.com currently reserves phpMyAdmin for Business and Commerce plans, with hosting activation if necessary. From the Hosting Dashboard: the relevant site, Settings, the Server section, Database, then Open phpMyAdmin. Each site has a preconfigured database; you cannot freely create a second database, change its connection, or access it externally. The “new empty database” procedure must therefore take place in an environment authorized for that purpose, without emptying the hosted site. Without SQL access, choose the migration procedure with the provider.

1. Provide the source engine, its version, and the collation declarations used.
2. Choose an authorized test destination; record its engine and check the name with SHOW COLLATION.
3. Keep the original file without replacing names.
4. Prepare its complete import into this destination after checking the scope of its statements.
5. If the choice is unsuitable, abandon this test database without changing the source or production.

Import the checked file into an isolated database

In phpMyAdmin or the authorized SQL tool, select the intended test database before choosing Import. Verify its name in the breadcrumb. Choose the checked file and the corresponding SQL format. The reading-encoding setting must match the file; changing it randomly does not make an unknown collation available.

Run a single import and keep the messages through completion. If the tool reports another error, identify what it concerns before changing the file again. A completed progress indicator or a few visible tables do not prove that the entire backup was executed. Avoid an option that ignores errors and silently continues.

1. Confirm the isolated database and the scope of the file to execute.
2. Open Import, select the SQL document, and check its options.
3. Execute once and keep the first failure message, if any.
4. If the import succeeds, check tables and data before connecting a WordPress copy to this database.
5. If the import stops, prepare a new clean destination for the next test without deleting an active database.

Adapt a structural copy only if the destination is fixed

The second approach is for someone able to read SQL statements and verify their effects. It is appropriate when the destination is fixed and a replacement collation has been chosen for its rules, not merely because its name exists. This decision may require the technical owner of a component imposing its own constraints.

Adapting the dump must affect only the relevant SQL declarations. Do not run a global replacement on content strings: the searched name may appear there as data, and changing a serialized value can make that value inconsistent. Keep a record of every changed statement and leave the original file intact. The example below shows only a table option; utf8mb4_unicode_ci remains an illustrative candidate to verify, with no universal equivalence to the original collation. These fragments are not standalone statements to execute.

Changing only a database’s default collation does not replace explicit collations in the dump. Likewise, the table option below does not replace a COLLATE clause on a column. Inventory these declarations separately; do not run a global conversion of existing tables to work around them. If the generator can produce a documented compatible export, prefer that verified option over repeated manual transformation.

1. List collations for the same character set, for example with SHOW COLLATION WHERE Charset = 'utf8mb4'; if the source uses utf8mb4.
2. Choose the replacement after comparing its rules and the constraints of the affected tables.
3. In a copy, change only the identified structure options, rereading the full context.
4. Import this copy into a new clean test database.
5. If comparison or uniqueness results differ unacceptably, return to the original file and the compatible-destination approach.

-- Option de table d’origine dans le dump :
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- Option illustrative après validation du remplacement :
DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Check indexes and comparison differences

A new collation may consider two values equal where the old one distinguished them, or vice versa. On a column with a unique index, this can cause insertion to fail for data that was acceptable at the source. A restoration that passes table creation may therefore encounter another legitimate rejection later.

One concrete case deserves checking: in MySQL, utf8mb4_0900_ai_ci is NO PAD, whereas utf8mb4_unicode_ci is PAD SPACE. Trailing spaces considered in the first case may no longer distinguish two strings in the second. Add representative pairs with accents, case differences, and spaces to your private verification set. Do not remove a unique index to get the import through: it may protect a business identifier.

If a conflict appears, record the affected values in a private environment and have the data owner decide how to handle them. Do not automatically delete rows that obstruct the conversion. A compatible destination remains preferable when no adaptation preserves the expected behavior. The goal is not just an import without a red message, but a database whose rules remain usable by the site.

On the copy, open the relevant table, then Structure in phpMyAdmin: record the column’s collation and the indexes using it. If necessary, the read-only query SHOW CREATE TABLE nom_table_confirme;, adapted to the exact name, exposes its full definition. It may reveal private names and default values. Then compare a search and a sort on the same source and restored records; an accepted creation query is not enough.

Do not use wp-config.php to convert the SQL file

The constants DB_CHARSET and DB_COLLATE belong to WordPress configuration. They do not erase explicit declarations in an SQL file executed through a database manager. Changing DB_COLLATE before rerunning the same dump therefore does not automatically transform its rejected creation statement.

Do not change an existing installation by removing these settings solely because a message mentions a collation. Keep the original values and their justification. An incorrect configuration may require a separate fix, particularly when WordPress or a component subsequently creates a table; that diagnosis is separate from reading an already-produced backup.

No new WordPress plugin is needed to identify the engine’s capabilities or import the checked SQL file. A backup tool may offer another documented procedure, but it does not magically make every collation available on the server. Avoid installing a converter without knowing the transformations it applies to structures and data. Read-only SQL queries and a file copy provide more direct control here.

Validate the restored database before connecting it to the site

Compare the table list with the list expected from the backup. Check component-specific tables, not just WordPress core tables. Check a few column and collation definitions in the SQL tool’s Structure tab. If an adaptation was made, they must match the recorded decisions.

Compare data volumes using appropriate checks; the row estimates displayed for some tables are not always exact counts. Also verify known records at the beginning and end of the scope. An SQL document may have stopped after several successful tables without the navigation interface making this obvious.

Only then connect a WordPress copy to the test database using its planned restoration procedure. Keep outbound messages disabled. Check login, a search, accented characters, and a reversible edit, followed by the affected components’ functions. A database-only export does not necessarily include media and plugin files; their absence is not new evidence of an incorrect collation. Separate these checks to maintain a reliable diagnosis.

Restart a test without mixing partial restorations

If the import failed, keep its first useful message and the file copy that produced it. Do not rerun a modified dump over partial tables hoping the remaining insertions will complete the whole. Depending on the SQL contents, you might encounter duplicates, overwrite some data, or retain a structure different from the intended one.

For a new test, use a clean isolated database prepared for that purpose through the panel or by the authorized administrator. Verify its name before any reset. The old production database and the source must remain available according to the rollback plan. Do not execute deletion calculated from an assumed prefix to clean up faster.

If a switch to the restored database is planned, keep the previous configuration and the time of the switch. Rolling back may mean restoring that connection, but writes received in the meantime must be accounted for. Simply putting an old wp-config.php back does not resolve a data discrepancy. Prepare a suitable window or synchronization for the site before the real operation.

Prepare future exports for the intended destination

Record the source engine and version, the collations used, the validated destination, and the tool that created the export. When a component adds tables or changes its SQL rules, include them in the next restoration test. The WordPress tables you happen to remember do not necessarily describe the whole site.

Keep the original file and, if an adaptation was validated, its transformation record. Specify the columns and constraints checked, along with remaining limitations. A recipe that replaces one name with another without context is not a reproducible migration plan. Capabilities evolve: check the actual destination during the next restoration.

To obtain precise help, provide the rejected name, engine and version, redacted structure statement, and SHOW COLLATION result. State whether the original file works on another test destination. This evidence helps choose compatibility or a deliberate adaptation without making the error disappear at the cost of silently changing content.

Compare two collation rules without creating a table

If the MySQL test server recognizes both names below, this read-only query demonstrates a concrete difference involving trailing spaces. It neither creates nor changes a table. In phpMyAdmin, open SQL on the test destination and run only this check after SHOW COLLATION.

On MySQL with these documented collations, the NO PAD case distinguishes a trailing space, and the PAD SPACE case may make it equivalent. A result of 0 means “different”; 1 means “equal.” If a collation is missing, the failed check confirms only that it is unavailable: do not install one by text substitution in the dump. On MariaDB or another version, record the actual result and the collation underlying any aliases.

This exercise explains why an import can get past the initial error and then encounter a uniqueness constraint. It does not replace comparison of your columns and their representative data. Beginners should keep both results with the engine information and ask the maintainer to choose the destination; no global conversion plugin is necessary.

SELECT
  _utf8mb4'a' COLLATE utf8mb4_0900_ai_ci =
  _utf8mb4'a ' COLLATE utf8mb4_0900_ai_ci AS egal_0900,
  _utf8mb4'a' COLLATE utf8mb4_unicode_ci =
  _utf8mb4'a ' COLLATE utf8mb4_unicode_ci AS egal_unicode;