mysql-pitfalls
Use when writing or reviewing MySQL 8.x (InnoDB) schema, queries or migrations: JSON columns and the -> and ->> operators, indexing JSON, utf8mb4 index key length limits, default case-insensitive coll
SKILL.mdskills/mysql-pitfalls/SKILL.md
GitHubmysql-pitfalls
Step 6 of the pipeline (WORKFLOW.md), for the MySQL 8.x behaviours that differ from what a developer used to
PostgreSQL or an ORM's defaults would assume: a JSON operator that returns quoted text, an index key that is too
long only on utf8mb4, a partitioned table that refuses a foreign key, a lock timeout of one year, and an ALTER
that copies the whole table. The premise: MySQL's defaults and limits are documented and specific, so a rule
that names the number is checkable and a rule that says "be careful" is not.
When
- A JSON column is queried, compared or indexed, or a long string column is indexed.
- A table is partitioned, or retention is implemented by deleting old rows.
- A read must see a write just made through a replica, or a query or lock needs a timeout.
- An
ALTER TABLEruns against a large table, or a shadow-table tool such as gh-ost is considered.
Steps
Read only the section the task meets. They are independent.
| § | Covers | Read it when | File |
|---|---|---|---|
| 1 | JSON, collation and index key length: -> vs ->>, indexing JSON, key limits, default collation |
a JSON column, a text index or a string comparison is written | 01-json-collation-keys.md |
| 2 | Partitioning, replicas and timeouts: partition keys, retention, lag, read-your-writes, timeout variables, upsert locks | a table is partitioned, replicas serve reads, or a timeout or lock error appears | 02-partitioning-replicas-timeouts.md |
| 3 | DDL that rebuilds and gh-ost prerequisites: online DDL algorithms, VARCHAR boundary, generated columns, foreign keys, shadow-table limits | an ALTER TABLE touches a large table or a schema change tool is chosen |
03-rebuilding-ddl-and-gh-ost.md |
Output / checkpoint
Each rule was exercised on the target version: a JSON query was checked with EXPLAIN against its index (§1.1),
a migration was run with an explicit ALGORITHM clause so that an unsupported combination fails instead of
copying (§3.1), and a retention job was run against a scratch table partitioned the same way (§2.1). A rule
checked by reading documentation only is not verified.
Guardrails
- Never expect
->to return unquoted text: use->>or an explicit cast to a native type (§1.1). - Never create a foreign key on, or to, a partitioned InnoDB table (§2.1).
- Never leave
lock_wait_timeoutat its default in a migration session (§2.3). - Never run an
ALTER TABLEon a large table without an explicitALGORITHMandLOCKclause (§3.1). - Versions: each rule names the MySQL version it needs; the manual read is MySQL 8.4. Nothing was run while writing this block.
- General migration discipline belongs to
devops-conventions; ORM migration review is inprisma-ops-and-test-hygiene§1.4 andjpa-hibernate-pitfalls.
Origin
Rewritten from the MySQL 8.4 Reference Manual (facts only, no text copied), the gh-ost documentation and the
strong_migrations README (MIT), read 2026-10-08. 🟡: never run by us; open points are in
references/origin.md. Overlaps with skills/sql-conventions §7 and skills/devops-conventions (2026-10-19); cross-referenced, not merged.