Somewhere in your memberlist there is a user whose location is Düsseldorf. Their signature contains “smart quotesâ€. Their username, if they registered in 2009 and never changed it, may be Björn. The database column is latin1. The bytes are not.
This is mojibake: UTF-8 bytes that were decoded as latin1 (or cp1252) and then re-encoded as latin1. The result is valid latin1, which is why nothing complained. It is also wrong, which is why the memberlist looks like a bad fax.
The standard advice is to run ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 and move on. That advice assumes the data is actually latin1. If it is double-encoded UTF-8, the conversion will faithfully preserve the mojibake and, in some cases, make it harder to reverse. The repair has to happen before the migration, not after.
What double encoding actually is
Take the string Björn. In UTF-8 the bytes are 42 6A C3 B6 72 6E. If a connection declares latin1, MySQL interprets each byte as a latin1 character: C3 becomes Ã, B6 becomes ¶. The stored value is now the six-character string Björn. Store that back into a latin1 column and you have committed the original sin.
The tell is the pattern. à followed by a punctuation character, †before quotes, é where é should be. These are not random corruption. They are the UTF-8 byte sequence rendered through a latin1 lens, and they are reversible if you know the original encoding.
Before you touch anything: back up and freeze writes
Repair is a destructive operation on production data. Take a logical dump and a physical snapshot. Put the board in maintenance mode so no new posts, profile edits, or private messages land mid-repair. For a 10k+ user board this is not optional; a repair that races against user writes will produce a database that is partly fixed and partly not, and you will not be able to tell which rows are which without a full audit.
Record the current schema before changing it. SHOW CREATE TABLE phpbb_users for every table you intend to touch. You will want the original column definitions when something goes sideways.
Find the damage before you fix it
Do not convert first and inspect later. Query for the mojibake signatures directly. A useful starting point is a scan for the common lead bytes:
SELECT user_id, username, user_location
FROM phpbb_users
WHERE username LIKE '%Ã%'
OR user_location LIKE '%Ã%'
OR user_sig LIKE '%â€%'
LIMIT 50;That is a sample, not a census. For a real count, run the same predicates with COUNT(*) and group by table. You want to know the blast radius before you write a single UPDATE.
Two categories will show up. The first is genuine double-encoded UTF-8, which is reversible. The second is data that was already latin1 and contains legitimate accented characters, which is not mojibake and must not be “repaired.” A user in Málaga with a correctly stored á is not a bug. A user in Málaga is. The distinction matters because the repair function will corrupt the first if applied blindly.
The repair, in principle
The reversible transformation is: reinterpret the stored latin1 bytes as UTF-8, then re-encode as UTF-8. One MySQL pattern for this is to convert the column to BINARY, then to utf8mb4, which forces a byte-preserving reinterpretation rather than a character-set translation:
ALTER TABLE phpbb_users
MODIFY username VARBINARY(255);
ALTER TABLE phpbb_users
MODIFY username VARCHAR(255) CHARACTER SET utf8mb4;
This works when the bytes are genuinely UTF-8 that was mislabeled. It does not work when the bytes are latin1 that was correctly labeled, because those bytes are not valid UTF-8 and the conversion will either fail or substitute replacement characters. Test on a restored copy first. Always.
The alternative is a row-by-row repair in application code: read the value, run it through a conversion that treats the stored string as latin1 bytes and decodes them as UTF-8, write it back. This is slower but lets you inspect each row and skip the ones that are already correct. For a memberlist of a few thousand rows, either approach is fine. For a phpbb_posts_text table with millions of rows, the schema-level approach is the only one that finishes before the next ice age.
Connection charset is the whole ballgame
Most mojibake is not created by the database. It is created by the connection. A PHP script that opens a latin1 connection, receives UTF-8 bytes from a form, and writes them to a latin1 column will produce exactly this corruption on every insert. Fixing the data without fixing the connection guarantees the problem returns.
Set the connection charset explicitly at the driver level. In PHP’s mysqli, that is set_charset('utf8mb4') or the equivalent DSN parameter. In PDO, it is the charset option in the DSN. In the MySQL client, it is SET NAMES utf8mb4. Do not rely on the server default; it varies by distribution and by version, and it is the single most common cause of this class of bug.
Verify the connection before you trust it:
SHOW VARIABLES LIKE 'character_set_client';
SHOW VARIABLES LIKE 'character_set_connection';
SHOW VARIABLES LIKE 'character_set_results';All three should report utf8mb4. If any of them says latin1, stop and fix that before touching data.
phpBB-specific considerations
phpBB’s schema and its conversion tooling have their own conventions. The board’s config file and the ACP both carry charset settings, and the database layer applies them at connection time. A migration that changes the database but not the board configuration will produce a board that reads correctly in one code path and incorrectly in another.
The practical sequence for a phpBB board is: back up, put the board offline, repair the data in the database, update the board’s charset configuration, verify the connection charset, then bring the board back online and spot-check the memberlist, a sample of posts, and the private message tables. The memberlist is the fastest visual check because usernames and locations are short and easy to scan. It is not sufficient on its own; signatures and post bodies are where the long-tail damage lives.
What not to do
Do not run a global REPLACE() across the database to swap ö for ö. It will work for the common cases and silently mangle the rare ones, including any post that legitimately discusses mojibake, any code block containing those byte sequences, and any username that happens to contain the pattern by coincidence. The repair is a re-encoding, not a find-and-replace.
Do not convert to utf8mb4 first and repair afterward. Once the column is utf8mb4, the double-encoded bytes are still double-encoded, and you have lost the clean signal that the column was latin1. The repair becomes harder, not easier.
Do not assume that a successful ALTER TABLE means the data is correct. The conversion will succeed on mojibake because mojibake is valid latin1. Success of the DDL statement tells you nothing about the semantic correctness of the contents.
Verification
After the repair, query for the mojibake signatures again. The counts should drop to zero, or to a small residue that you can inspect by hand. Then check the reverse: query for the correct characters and confirm they render as expected. A user named Björn should appear as Björn in the memberlist, in their posts, and in any exported data.
Export a sample of rows to a UTF-8 file and open it in a tool that does not guess encodings. If the file is valid UTF-8 and the characters are correct, the repair held. If the tool reports invalid byte sequences, something is still wrong.
FAQ
Can I tell mojibake from legitimate latin1 without inspecting every row? Not reliably. The signatures overlap. A string containing à is suspicious but not conclusive. The only safe approach is to test the repair on a copy and compare before and after.
Will this break search? Possibly. Search indexes built on the old byte sequences will not match the repaired strings. Rebuild the search index after the repair, not before.
How long will this take on a 10k+ user board? The repair itself is fast if done at the schema level. The downtime is dominated by the backup, the verification, and the search reindex. Budget for the whole sequence, not just the ALTER.
What if I find mojibake after the migration? You can still repair it, but you have lost the clean latin1 signal. You will need to identify affected rows by pattern and repair them individually, which is slower and more error-prone. This is why the repair belongs before the migration.
Does utf8mb4 cost more storage? Yes, for columns that hold non-ASCII data, because utf8mb4 reserves up to four bytes per character. For a forum with mostly ASCII content the difference is modest. For a forum with heavy non-Latin text it is larger. Measure on your own data rather than trusting a rule of thumb.
