Four Symptoms, One Underlying Cause
Encoding bugs feel arbitrary because the symptoms look unrelated. In practice almost all of them reduce to a single mistake: bytes written with one encoding were read with a different one. Once you can name which two encodings were involved, the fix is usually obvious.
Here are the four presentations you will actually encounter.
Mojibake
A name that should read "Müller" appears as "Müller". Two characters where there should be one, the first typically an accented capital A or a similar high-range Latin character.
This is UTF-8 bytes read as a single-byte encoding, usually Windows-1252 or ISO-8859-1. The character requires two bytes in UTF-8; the single-byte reader interprets each byte as its own character. The original data is intact and recoverable, because no information was lost, only misinterpreted.
Replacement Characters
The same name appears as "M?ller" or with a diamond-shaped question mark. One character where there should be one, but the wrong one.
This is the opposite direction: single-byte encoded bytes read as UTF-8. The byte sequence is not valid UTF-8, so the decoder substitutes the Unicode replacement character. Unlike mojibake, this is destructive. The original byte value is gone and cannot be reconstructed.
An Invisible Character at the Start
Your first column header is not matching. Comparing it against the expected string fails. Printing it looks correct. Its length is one greater than it should be.
This is a byte order mark, three bytes at the beginning of a UTF-8 file that some tools write and many do not expect. It is invisible in most editors and breaks exact string comparison on the first field of the first row. Notoriously, spreadsheet applications on Windows write it by default.
Emoji and Rare Characters Failing to Save
Most text works, but emoji, certain Chinese characters, or mathematical symbols cause errors or get truncated at that point.
This is the classic MySQL trap: a column declared with the utf8 character set, which stores at most three bytes per character and therefore cannot represent anything outside the Basic Multilingual Plane. The name is misleading; the character set that actually implements UTF-8 is utf8mb4.
The Four Layers Where It Goes Wrong
Debugging effectively means knowing where to look. Bytes pass through four layers between the source system and your database, and each one can reinterpret them.
| Layer | Typical Failure | How To Check |
|---|---|---|
| The file itself | Exported as a regional codepage, or UTF-8 with a BOM. | Inspect the first bytes in a hex viewer. Look for EF BB BF. |
| The reader | Assumed an encoding rather than detecting or being told. | Read the same file with each candidate encoding and compare. |
| The connection | Client and server disagree on the wire character set. | Query the session character set variables directly. |
| The column | Declared with a character set that cannot hold the data. | Inspect the column definition, not just the database default. |
The connection layer is the one people forget, and it produces the most confusing behaviour: correct bytes in the file, a correctly declared column, and corruption in between. A connection declared as latin1 will transcode your UTF-8 on the way in, and the column stores exactly what it was handed.
Diagnosing in Order
Work outward from the data. Guessing at configuration before you know what the bytes are wastes time.
Step 1: Look at the Bytes
Not the rendered characters, the actual bytes. Open the file in a hex viewer or query the byte length of a suspect value in the database and compare it against the character length. For pure ASCII these are equal; any difference tells you multi-byte characters are present, and the ratio hints at which encoding.
A value where byte length is exactly double the character length for Latin text is a strong mojibake signal.
Step 2: Determine Whether It Is Recoverable
This determines your entire strategy, so establish it early.
- Mojibake is recoverable. The bytes are correct and merely misinterpreted. Re-encoding fixes it in place.
- Replacement characters are not recoverable. The information is destroyed. You must reload from the source.
Spending an afternoon writing a repair script for replacement characters is wasted effort. Check first.
Step 3: Verify the Connection Before the Column
Check the session character set on the connection your loader actually uses, not the one your admin client uses. These are frequently different, which is why data loaded by a script is corrupt while the same value typed into a GUI client is fine.
Step 4: Check the Column, Not the Database Default
A database can default to utf8mb4 while individual columns created earlier remain utf8 or latin1. Table and column definitions override database defaults and are not retroactively updated when the default changes. Always inspect at column granularity.
The Fixes, By Layer
Fixing the File
Re-export with UTF-8 explicitly selected, without a BOM if the consumer does not expect one. If re-export is impossible, transcode the file with an explicit source and target encoding rather than relying on detection.
For spreadsheet sources, be aware that the standard "Save as CSV" behaviour on Windows may use a regional codepage. Choosing "CSV UTF-8" avoids the codepage problem but introduces a BOM, so you trade one issue for another and need to handle whichever one you chose.
Fixing the Reader
Never let an importer guess. Specify the encoding explicitly. If you must handle unknown files, detect the BOM first, then attempt strict UTF-8 decoding, and only fall back to a single-byte encoding if strict decoding fails. Silent lossy fallback is how replacement characters get written.
Fixing the Connection
Set the connection character set explicitly in your connection string or immediately after connecting. Do not rely on server defaults, which vary between environments and are a classic reason something works locally and corrupts in production.
Fixing the Column
On MySQL, use utf8mb4 with an appropriate collation for every text column that stores human-entered data. On PostgreSQL, ensure the database was created with UTF-8 encoding, since this cannot be changed afterward without a dump and reload. On SQL Server, use the N prefix on string literals and NVARCHAR rather than VARCHAR, or a UTF-8 collation on recent versions.
Note that generated INSERT scripts must match. A script emitting VARCHAR columns and unprefixed literals will not store non-Latin text correctly on SQL Server regardless of how the connection is configured, which is one reason generating dialect-appropriate DDL matters rather than emitting one generic form.
Prevention That Actually Holds
- Declare UTF-8 at every boundary. File export, file read, connection, column, and application response. Every layer where an assumption is possible should have an explicit statement instead.
- Fail loudly on invalid input. Configure decoders to raise errors rather than substitute replacement characters. A failed import you can retry beats a successful import you cannot repair.
- Keep a canary row. Include a test record containing an accented Latin character, a CJK character, an emoji, and a right-to-left character. Load it with every migration and read it back. If the canary survives, the pipeline is sound; if it does not, you know before real data is affected.
- Assert on byte length in validation. Add a check for the replacement character and for unexpected byte-to-character ratios in your post-load validation script. This turns encoding damage from something discovered by a customer into something discovered by a query.
- Inspect data in a client-side viewer before loading. A browser-based table editor renders the actual decoded characters, so mojibake and replacement characters are visible immediately rather than after they have been written to a table.
Frequently Asked Questions
Why does MySQL utf8 not mean UTF-8?
It was implemented when three bytes per character seemed sufficient, before characters outside the Basic Multilingual Plane were in common use. It stores a strict subset of UTF-8. The complete implementation is utf8mb4, and it is what you should use for any column holding human-entered text.
Should I strip the byte order mark or keep it?
Strip it for machine consumption. It serves no purpose in UTF-8, since UTF-8 has no byte order to mark, and it breaks exact comparison on the first field. Keep it only when a specific downstream tool requires it, which in practice means certain spreadsheet applications.
Can I repair mojibake already stored in my database?
Usually yes, by reinterpreting the stored bytes with the correct encoding. Work on a copy, and be careful about rows that were already correct, since applying the transformation twice corrupts them. Replacement characters, by contrast, cannot be repaired at all and require reloading from the source.
Conclusion: Replace Assumptions With Declarations
Encoding bugs are not mysterious once you stop treating text as text and start treating it as bytes plus an interpretation. Every corruption is a place where two layers disagreed about the interpretation.
The durable fix is not a clever repair script. It is declaring the encoding explicitly at every boundary, configuring decoders to fail rather than substitute, and keeping a canary row that proves the pipeline works before real data depends on it.