Mojibuster

WordPress and database exports: é and ’ instead of é and ’

After a migration, update or database export your posts suddenly show é instead of é and ’ instead of ’? The data is almost always still there – it is just read with the wrong encoding. Mojibuster repairs the export; then you fix the cause in the database.

Works with text and these files:

  • CSV
  • TXT
  • JSON
  • XML
  • SRT
  • VTT
  • TSV
  • MD
  • HTML
  • SQL
  • LOG

For example Excel exports, database dumps, subtitles or website code.

Repair

  1. Drop a file or paste text
  2. It gets repaired automatically
  3. Copy or download
or simply drag a file here

What the problem looks like

A line from an SQL dump after moving servers

Before

INSERT INTO wp_posts (post_title) VALUES ('Café menu – crème brûlée');

After

INSERT INTO wp_posts (post_title) VALUES ('Café menu – crème brûlée');

Where it comes from

WordPress stores text as UTF-8. Many older installations, however, created their tables as latin1 or connected with latin1. As long as writing and reading make the same mistake, nobody notices. After moving to a new server, exporting with phpMyAdmin or changing the connection charset, every accented letter suddenly turns into two characters.

  • DB_CHARSET in wp-config.php is latin1 or doesn't match the tables.
  • The dump was exported as latin1 and imported as utf8mb4 – or the other way round.
  • A plugin or import script converted already broken text once more. That creates double-encoded text like é.

Repair the export

  1. Back up the database before you change anything.
  2. Export the affected tables as an .sql file, for example with phpMyAdmin or mysqldump --default-character-set=utf8mb4.
  3. Drop the file into the box above. Mojibuster repairs only the broken spots and shows you every change.
  4. Download the repaired file and import it with utf8mb4 into a fresh test database.
  5. Check the site. If everything looks right, import it into the live database.

The dump is never uploaded – which matters, because it usually contains user accounts, e-mail addresses and comments. More on that for businesses & data protection officers.

Careful with serialized data

WordPress stores some settings in wp_options and wp_postmeta as serialized PHP values such as s:5:"Café";. The number is the length in bytes. When é becomes é again, it no longer matches and WordPress discards the value. So repair content tables like wp_posts, wp_comments and wp_terms first. For options, wp search-replace from WP-CLI is the better tool because it updates the lengths.

Fix the cause in MySQL

  • Switch tables and connection to utf8mb4 and set define('DB_CHARSET', 'utf8mb4'); in wp-config.php.
  • If a latin1 column holds UTF-8 bytes, don't convert it directly – that would encode it a second time. The usual route goes through a binary type: first MODIFY … LONGBLOB, then MODIFY … LONGTEXT CHARACTER SET utf8mb4.
  • Test every change on a copy of the database first.

Frequently asked questions

Can I repair a whole WordPress database at once?

Yes, even large SQL dumps. Mind serialized data in wp_options and wp_postmeta, though – WP-CLI handles that better.

Will correct characters in the dump be changed?

No. Mojibuster only repairs sequences that are clearly broken. Correct text stays as it is.

What's the difference between utf8 and utf8mb4?

In MySQL, utf8 only stores characters of up to three bytes, so emojis get lost. utf8mb4 is real UTF-8 and the right choice for WordPress.

Does this work for systems other than WordPress?

Yes. Joomla, Drupal, Magento or your own application on MySQL or MariaDB have the same problem and the same fix.

More guides