ZapBounce

ZapBounce and MySQL

MySQL holds contact data in a lot of older applications, and it has one specific trap that produces verification results nobody can explain.

The legacy utf8 charset is three bytes and cannot store the full Unicode range. An address containing a four-byte character is truncated or rejected on insert, and what you verify is not what the user typed.

utf8 is not UTF-8, and it truncates

MySQL's utf8 charset stores at most three bytes per character. Real UTF-8 needs four for some characters, and utf8mb4 is the one that handles them.

An internationalised address on a utf8 column can be stored truncated or rejected, and the verification then runs against a string that is not the address anybody has.

That produces invalid verdicts for addresses that work perfectly, and the cause is three schema levels away from where anyone is looking.

How the data moves

  1. Check the column charset before anything else

    utf8mb4 is correct. utf8 will cause exactly this problem and nothing downstream can compensate.

  2. Store verdicts in their own table

    Keyed on the address, with a checked_at timestamp, joined rather than added as a column.

  3. Batch through a scheduled job

    Select candidates, submit a batch, upsert the results on the webhook.

  4. Compare stored addresses against their source

    Where a truncation is suspected, compare a sample against the original form submissions.

Setting it up

  1. Confirm the email column uses utf8mb4 rather than utf8, and convert it if not.
  2. Create a verdicts table keyed on the address with a checked_at column.
  3. Index the candidate query on checked_at so stale-row selection stays fast.
  4. Write a worker that batches candidates and upserts results.
  5. Create a view joining contacts to verdicts with a staleness condition.
  6. Sample addresses against their source to confirm nothing was truncated on insert.

MySQL: common questions

Why do some addresses verify as invalid when they work?

Check the column charset. MySQL's utf8 is three bytes and truncates characters that need four, so the stored string is not the address.

utf8 or utf8mb4?

utf8mb4, always. MySQL's utf8 is a historical mistake that is still the default in older schemas.

Column or separate table for the verdict?

Separate table with a timestamp. A column has no natural place for the check date.

Reference

Check a MySQL export today

100 free checks a month, no card. Unknown results and duplicates are never billed.

Get an API keySee pricing