How to Handle Database Character Set Mismatches During Site Migrations

Database character set mismatches are one of the quietest ways a WordPress migration goes wrong. The site moves from one host to another. The front end loads. The admin dashboard works. Then somebody opens an old post and sees “ where a curly quote should be, or é instead of é. The migration didn’t fail. The bytes moved. What changed was how those bytes got read. This article walks through the exact mechanics of that failure, how to catch it before it becomes a support ticket, and how to fix it without rebuilding the site.

For small-to-mid publishing teams, this matters because the editorial archive is the asset. A character set mismatch doesn’t just break display. Run the wrong conversion and it can corrupt stored data. The fix isn’t a plugin setting. It’s a sequence of checks against wp-config.php, the MySQL connection, the table collations, and the dump file itself.

What a Character Set Mismatch Actually Is

WordPress stores text in MySQL tables. MySQL gives each table a character set and a collation. The character set defines which bytes map to which characters. The collation defines how those characters sort and compare. WordPress has used utf8mb4 as its default since version 4.2, but plenty of older sites still run utf8, and plenty of hosts still create databases with utf8 or even latin1 as the default.

The mismatch shows up when one layer says utf8mb4 and another layer says latin1. The bytes don’t change. The label changes. MySQL then interprets the same byte sequence under a different encoding, and the output becomes mojibake: “, é, —, and similar garbage.

There are three common places where the label gets lost or changed:

• The mysqldump export file, which may not include SET NAMES utf8mb4 or may include a conflicting SET NAMES latin1.
• The wp-config.php file, where DB_CHARSET may be set to utf8 while the tables are utf8mb4, or the other way around.
• The target database server, where the default character set for new tables may differ from the source server.

Detecting the Mismatch Before It Spreads

Don’t start a migration by importing the dump and hoping. Check the source first. Run this query against the source database:

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name'
AND TABLE_NAME LIKE '%_posts';

If the TABLE_COLLATION for wp_posts is utf8mb4_unicode_ci or utf8mb4_unicode_520_ci, the source is modern. If it’s utf8_general_ci or latin1_swedish_ci, the source is legacy. That single value tells you what to expect in the dump.

Next, check the dump file itself. Open the first 50 lines of the .sql file in a plain text editor. Look for lines like:

/*!40101 SET NAMES utf8mb4 */;

or

/*!40101 SET NAMES latin1 */;

If the dump was created with mysqldump without the --default-character-set=utf8mb4 flag, the SET NAMES line may be missing or wrong. That’s the first failure point.

Finally, check wp-config.php on the target site. Look for these two lines:

define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

If DB_CHARSET is utf8 but the tables are utf8mb4, WordPress will tell MySQL to use utf8 for the connection. That alone can produce mojibake on display even when the stored data is fine.

The Exact Fix Sequence

There’s a correct order of operations. Skipping a step or doing them out of order can make the corruption permanent. Follow this sequence.

1. Export with an Explicit Character Set

Never rely on the host’s default mysqldump settings. Always pass the character set explicitly:

mysqldump --default-character-set=utf8mb4 \
  --single-transaction \
  --quick \
  --no-tablespaces \
  -u username -p database_name > site_backup.sql

The --default-character-set=utf8mb4 flag forces the dump to label the bytes correctly. The --single-transaction flag prevents table locks on InnoDB tables during the export. The --no-tablespaces flag avoids a common permission error on shared hosts.

If the source tables are latin1 but actually contain utf8 bytes — a common legacy situation — don’t use --default-character-set=latin1. That will double-encode the data. Instead, export with --default-character-set=latin1 only if you’re certain the stored bytes are genuinely latin1. For most WordPress sites, the stored bytes are utf8 even when the table label says latin1. In that case, export with utf8mb4 and fix the table labels after import.

2. Inspect the Dump Header

After the export, open the .sql file and confirm the SET NAMES line matches utf8mb4. If it doesn’t, don’t import. Re-run the export with the correct flag. Importing a mislabeled dump is how you turn a display problem into a storage problem.

3. Create the Target Database with the Right Defaults

On the target server, create the database with an explicit character set and collation:

CREATE DATABASE new_site_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

This ensures that any table created during the import without an explicit character set inherits utf8mb4. If the target host doesn’t allow CREATE DATABASE through a control panel, use the panel’s database creation form and select utf8mb4 if available. If the panel only offers utf8, create the database anyway and fix the tables after import.

4. Import with the Same Explicit Character Set

Import the dump using the same character set flag:

mysql --default-character-set=utf8mb4 \
  -u username -p new_site_db < site_backup.sql

This tells the MySQL client to interpret the dump file as utf8mb4. If the dump header says utf8mb4 and the client says utf8mb4, the bytes land in the target tables unchanged.

5. Verify Table Collations After Import

After the import completes, run the same information_schema query against the target database. Compare the TABLE_COLLATION values with the source. If the source was utf8mb4_unicode_ci and the target is utf8_general_ci, the import didn't preserve the collation. That's a mismatch, but it's usually cosmetic. The fix is a single ALTER TABLE statement per table, or a loop over all tables.

If the target tables are latin1 while the source was utf8mb4, the import failed to apply the character set. Don't run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 yet. First check whether the data is already mojibake. If the front end shows clean text, the bytes are fine and only the label is wrong. In that case, use ALTER TABLE ... DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci without CONVERT TO. That changes the label without touching the bytes.

6. Fix the Connection Character Set in wp-config.php

Set DB_CHARSET to utf8mb4 and leave DB_COLLATE empty:

define('DB_CHARSET', 'utf8mb4');
define('DB_COLLATE', '');

An empty DB_COLLATE lets MySQL use the table's own collation for comparisons. Setting a specific collation here can cause unexpected sort order changes on migrated sites.

7. Test with Known Problem Characters

Create a test post or edit an existing one. Type or paste these characters into the post body and title:

• Curly quotes: “ ” ‘ ’
• Em dash: —
• Accented letters: é ü ñ
• Non-Latin script: 日本語
• Emoji: 😀

Save the post, reload it, and check the front end. If any of these render as ?, “, or empty boxes, the connection or table character set is still wrong. Don't proceed with content edits until this test passes.

When the Data Is Already Corrupted

If the migration was already completed and the stored data now shows mojibake, the fix is different. You're no longer preventing corruption. You're reversing it.

The most common case is a latin1 table that contains utf8 bytes. The bytes are correct, but MySQL interprets them as latin1. The fix is to change the table's character set to utf8mb4 without converting the bytes. In MySQL, that's:

ALTER TABLE wp_posts
  MODIFY post_content LONGTEXT
  CHARACTER SET utf8mb4;

This tells MySQL to reinterpret the existing bytes as utf8mb4. It doesn't re-encode them. If the bytes were already valid utf8, the text becomes readable immediately.

The dangerous case is when someone already ran CONVERT TO CHARACTER SET utf8mb4 on a latin1 table that contained utf8 bytes. That double-encodes the data. The original bytes are gone. The only reliable fix is to restore from a pre-conversion backup and redo the migration correctly. If no backup exists, the data is permanently damaged. This is why the order of operations matters.

Why This Happens on Shared Hosts

Shared hosting control panels often create databases with latin1 or utf8 as the default, regardless of what the application expects. The panel's migration tool may also export with a hardcoded character set. When you combine a panel-created database with a panel-generated dump, the labels can disagree at three different layers: the dump header, the client connection, and the table definition.

This isn't a WordPress bug. WordPress sets the connection character set based on DB_CHARSET in wp-config.php. The problem is that the surrounding infrastructure doesn't always honor that setting. The fix is to stop trusting the infrastructure and start checking the actual bytes and labels at each layer.

Preventing the Next Migration Failure

Add a pre-migration checklist to your team's runbook. The checklist should include:

• Run the information_schema query on the source and record the collation for every table.
• Export with --default-character-set=utf8mb4 and verify the SET NAMES line in the dump.
• Create the target database with utf8mb4 defaults.
• Import with the same explicit character set.
• Compare source and target table collations after import.
• Test with curly quotes, em dashes, accented letters, and emoji before publishing any new content.

If your team uses a migration plugin, the same checks still apply. Plugins can hide the export and import steps, but they can't fix a mislabeled dump. After any plugin-based migration, run the collation query and the character test. If either fails, export and import manually using the sequence above.

What This Means for Editorial Workflows

For a publishing team, a character set mismatch isn't just a technical annoyance. It can silently corrupt archived content. An editor opens a post from 2016, sees mojibake, and assumes the content was always broken. The archive loses trust. The fix isn't to re-type the content. The fix is to restore the correct byte interpretation.

This is also why migration testing should include a content audit, not just a front-end smoke test. Open the oldest post, the longest post, and a post with non-Latin characters. Check the post title, the excerpt, and the content. If any of them show mojibake, stop the migration and fix the character set before proceeding.

If you're dealing with a site that shows no content at all after migration, the problem may be different. See What to Fix First When a New WordPress Site Says Nothing Found for the separate failure mode of empty archives and missing rewrite rules.

FAQ

Why do I see “ instead of curly quotes after a migration?

That's the classic signature of utf8 bytes being interpreted as latin1. The curly quote is stored as three bytes in utf8. When MySQL reads those bytes as latin1, each byte becomes a separate character, producing “. The fix is to change the table or connection character set to utf8mb4 without converting the bytes.

Should I use utf8 or utf8mb4 for WordPress?

Use utf8mb4. MySQL's utf8 is a three-byte subset that can't store emoji or some rare characters. WordPress has defaulted to utf8mb4 since version 4.2. If your tables are still utf8, migrate them to utf8mb4 as part of the next site migration.

Can I fix a character set mismatch with a plugin?

No. The mismatch lives in the database layer, not the application layer. A plugin can change how WordPress queries the database, but it can't change how MySQL interprets the stored bytes. The fix requires SQL statements against the tables or a correct re-import of the dump.

What is the difference between changing the default character set and converting the data?

Changing the default character set updates the table's label for future inserts. It doesn't touch existing bytes. Converting the data re-encodes every existing byte sequence from one character set to another. If the existing bytes are already utf8 but labeled latin1, converting will double-encode them and permanently corrupt the text. Change the label first. Convert only when you're certain the stored bytes are genuinely in the old character set.

Next Step for This Site

This article is part of a series on migration failure modes. The next article in the series covers serialized data corruption in wp_options during search-and-replace operations — a related failure that often appears alongside character set mismatches when teams use naive SQL find-and-replace on serialized arrays. If you have a migration story where the character set was fine but widgets and theme options broke, that's the article to read.