Converting a table's character set or collation on a live MySQL table
The classic utf8 → utf8mb4 migration. On MySQL 8.0, ALTER TABLE … CONVERT TO CHARACTER SET rewrites every row and does not permit concurrent DML while it runs. On a busy table that is an outage.
What the manual says
| Operation | Instant | In place | Rebuilds table | Permits concurrent DML |
|---|---|---|---|---|
Converting a character set (CONVERT TO CHARACTER SET) | No | Yes | Yes* | No |
Changing the table character set / collation default only (CHARACTER SET = …) | No | Yes | Yes* (if the encoding differs) | Yes |
Source: MySQL 8.0 Reference Manual, Online DDL Operations → Table operations. * The table is rebuilt when the new encoding differs.
Two consequences: the whole table is rewritten (I/O, disk space for the copy, replica lag), and while CONVERT TO CHARACTER SET runs, writes wait. Waiting writes hold connections; the ALTER also needs an exclusive metadata lock at the start and end, which queues behind any long-running transaction on the table — and every new query queues behind the ALTER.
What to do instead
- Expand/contract: add new columns/tables with the target charset, backfill in batches, switch reads and writes, drop the old ones in a later migration.
- Online schema change tool (gh-ost, pt-online-schema-change): copies the table in the background and swaps it.
- Maintenance window with the statement asserted:
ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci, ALGORITHM=INPLACE, LOCK=NONE;— if MySQL cannot honourLOCK=NONEit fails immediately instead of blocking. - Check
lock_wait_timeoutso a queued ALTER fails fast rather than stacking up connections.
How Presift flags it
✖ MG002 ERROR migration.sql:1
ALTER TABLE users: CONVERT TO CHARACTER SET rebuilds the table and blocks concurrent DML
Why: Converting a table's character set rewrites every row (the manual lists it as in place, rebuilds table, permits concurrent DML: No); writes are blocked for the duration.
Do: Avoid on large/busy tables during traffic. Options: expand/contract (add new structure, migrate data in batches, switch, drop later); an online schema-change tool (gh-ost, pt-online-schema-change); or a maintenance window. Assert the expectation explicitly with ALGORITHM=... so MySQL fails fast if it cannot comply.
Ref: https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
▲ MG003 WARNING migration.sql:1
ALTER TABLE users: no ALGORITHM/LOCK clause on a potentially blocking changeEvaluate it on your own migrations — 30 days, whole product, no key to copy
Add the Action to a pull-request workflow and give the job id-token: write. On its first
run the client asks GitHub for the job's own identity token and exchanges it, in memory, for a
short-lived evaluation entitlement. There is no form, no account, no card and nothing to paste.
name: migration-safety
on: [pull_request]
permissions:
contents: read
id-token: write # lets Presift start your 30-day evaluation
jobs:
presift:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: nishu-sde/presift@v1
with:
paths: db/migrations
fail-on: errorOne 30-day evaluation per GitHub owner — an organisation or a personal account.
Linux x86_64 runners with Python 3.9+ (GitHub-hosted ubuntu-* runners qualify). Fork pull
requests cannot evaluate, because GitHub issues them no identity token. Outside GitHub Actions the
evaluation is not available; a paid organisation key is.