liveBook · Manning

Utf8mb4

As explained in PostgreSQL Mistakes and How to Avoid Them

utf8mb4 is MySQL's character encoding that supports Unicode characters encoded with up to four bytes.

utf8mb4 is MySQL’s Unicode encoding for characters requiring up to four bytes. It exists because MySQL’s older utf8 implementation supports only three-byte characters, excluding some international characters and emojis. section 10.3

A migration failure

A MySQL dump imported into PostgreSQL can fail with an invalid UTF8 byte sequence such as 0xe9 when tables still use latin1. Converting the dump with iconv addresses the character encoding mismatch, but embedded NUL characters can trigger a second PostgreSQL error. section 10.3

The operational lesson is to verify the source data rather than trusting a later server default:

  • Check table encodings explicitly; upgrading MySQL does not convert existing latin1 tables. section 10.3
  • Convert latin1 data to UTF-8 before importing it into PostgreSQL. section 10.3
  • Remove or otherwise handle NUL characters, which PostgreSQL rejects in string constants even with UTF8 encoding. section 10.3
psql service=staging -f prod_dump.sql

Inspect the failed import and the successive encoding and NUL-character diagnostics. section 10.3

How PostgreSQL Mistakes and How to Avoid Them covers it

How deep it goes
Explains the three-byte limitation, four-byte replacement, persistent latin1 tables, and related PostgreSQL import failures.
Examples
Uses a MySQL dump, the 0xe9 error, iconv conversion, and PostgreSQL’s rejected NUL character.
What it assumes
Assumes database migration familiarity while establishing the distinction between latin1, MySQL utf8, and utf8mb4.
What you can do after
Diagnose encoding-related imports with psql and repair dumps using iconv and NUL-character removal.
How it connects
Connects MySQL, UTF-8, latin1, Unicode, PostgreSQL, and character encoding.
Cover of PostgreSQL Mistakes and How to Avoid Them
liveBookmanning.com
Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Related concepts

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.