Skip to content

PLANETSCALE 2023-03-24 Tier 3

Read original ↗

PlanetScale — How to Upgrade from MySQL 5.7 to 8.0

JD Lien's 2023-03-24 PlanetScale pedagogical post is a practitioner's checklist for the MySQL 5.7 → 8.0 major-version upgrade, motivated by MySQL 5.7's October-2023 end-of-life. The article's value on the wiki is as the canonical taxonomy of breaking-change classes across a major MySQL version bump — the five orthogonal compatibility axes a DBA has to audit before flipping the version. The closing section pitches PlanetScale's online database import as a zero-downtime alternative to the in-place RDS upgrade.

Summary

The post frames the MySQL 5.7 → 8.0 upgrade as five orthogonal breaking-change classes plus two operational ones:

  1. Character set + collation defaults flip — server default goes from latin1 / utf8 to utf8mb4 / utf8mb4_0900_ai_ci. Legacy tables keep their declared charset; the recommendation is to migrate to utf8mb4 anyway for Unicode + emoji coverage. (See concepts/utf8mb4-vs-utf8.)
  2. Deprecated data types — YEAR(2), ENUM, TINYTEXT / MEDIUMTEXT / LONGTEXT, and the NATIONAL / CHARACTER SET / COLLATE inline clauses: still available but no longer recommended.
  3. Authentication plugin change — default auth plugin is now caching_sha2_password instead of mysql_native_password. Old clients that speak only the older plugin stop authenticating after the server is upgraded until the client library or the user account is updated.
  4. SQL mode defaults are stricter — new default is ONLY_FULL_GROUP_BY,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION. Queries that were silently tolerated on 5.7 (ambiguous GROUP BY, division by zero) now error on 8.0.
  5. Reserved-word expansion — MySQL 8 reserves ~40 new keywords (ACTIVE, ADMIN, ATTRIBUTE, COMPONENT, GROUPS, LEAD, RANK, RANDOM, ROLE, SKIP, SYSTEM, URL, VISIBLE, etc.). Any schema using these as unquoted identifiers (table / column / index names) fails to parse on 8.0.

Plus two closely-related operational shifts:

  1. C-style && / || / ! operators are deprecated (in favour of AND / OR / NOT).
  2. Server error codes changed for some errors — applications that match on error codes to trigger retry / fallback need to re-audit.

The closing sections pitch PlanetScale's database-import tooling as the zero-downtime path: rather than perform an in-place upgrade (which requires downtime even under RDS Blue/Green), import the 5.7 database to PlanetScale, which "will automatically do the no-downtime upgrade to 8.0 for you" under the hood via Vitess VReplication logical replication.

Key takeaways

  1. A major-version upgrade has five orthogonal breaking-change classes, not one. The checklist- style framing in the post is actually load-bearing: charset, data types, auth, SQL mode, and reserved words are each independent, each has a distinct audit procedure, and each has its own remediation. Skipping any one class risks a post-upgrade outage on the uncovered axis. (Source: sources/2026-04-21-planetscale-how-to-upgrade-from-mysql-57-to-80.)
  2. The utf8 → utf8mb4 default flip is the schema-level change with the biggest long-tail cost. Legacy schemas keep their declared CHARSET=utf8; migrating them requires ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4, which naively rewrites the whole table. See concepts/utf8mb4-vs-utf8 and the Vitess 21 programmatic-text-conversion Online DDL for the scale-aware execution path.
  3. The auth-plugin change breaks old clients, not old data. "The most significant change is that the default authentication plugin is now caching_sha2_password instead of mysql_native_password." Client-library compatibility is the gating concern — the mitigation is either upgrade every client library to one that speaks caching_sha2_password, or ALTER USER ... IDENTIFIED WITH mysql_native_password to pin old accounts back to the old plugin. See concepts/mysql-caching-sha2-password-auth.
  4. Stricter SQL mode silently flips query-shape contracts. The new default includes ONLY_FULL_GROUP_BY — any SELECT a, b FROM t GROUP BY a (where b is neither aggregated nor functionally dependent on a) returns rows on 5.7 and errors on 8.0. A 5.7 codebase can have thousands of these queries that silently "worked" by returning one arbitrary b per group. Auditing is non-trivial. See concepts/mysql-sql-mode.
  5. Reserved-word expansion is the schema breakage with the cleanest detection path. The new keywords have a finite published list; a information_schema.columns scan against the list identifies every table / column that must be renamed or back-tick-quoted before the upgrade. Compare this with SQL-mode auditing (needs full query-corpus static analysis) — reserved words are a ~1-query audit. (See concepts/mysql-reserved-words-upgrade-break.)
  6. RDS Blue/Green is not zero-downtime. Lien quotes AWS: "database engine upgrades require downtime" — even the Blue/Green path still has a cutover window. The only zero-downtime path documented is logical replication with a client-redirect cutover — which is what PlanetScale's import tooling is underneath (concepts/online-database-import).
  7. Deprecated-data-type migration is mostly advisory on 8.0 — YEAR(2), ENUM, TINYTEXT / MEDIUMTEXT / LONGTEXT still work on 8.0. The recommendation to move them (to YEAR(4), lookup tables with FKs, VARCHAR(N) / TEXT) is a future-proofing discipline, not a hard upgrade blocker.

Systems extracted

  • systems/mysql — subject of the upgrade; 5.7 phase-out drives the post's urgency.
  • systems/innodb — the storage engine whose default ROW_FORMAT changed to DYNAMIC on 8.0, affecting utf8mb4 index-size ceilings for legacy schemas.
  • systems/planetscale — pitched as the zero-downtime upgrade destination.
  • systems/vitess — underlying substrate for PlanetScale's import tooling (not named in the post; inferred from PlanetScale's architecture).
  • systems/aws-rds — named as the reference point for "engine upgrades require downtime", even under Blue/Green deployments.

Concepts extracted

  • concepts/utf8mb4-vs-utf8 — already canonical; this post reinforces the MySQL-8-default-flip framing.
  • concepts/character-set — already canonical; reinforced with the ALTER DATABASE ... CHARACTER SET utf8mb4 + ALTER TABLE ... CONVERT TO CHARACTER SET two-step migration recipe.
  • concepts/collation — already canonical; utf8mb4_0900_ai_ci framed as the MySQL 8 default with the suffix taxonomy (0900 = UCA 9.0, ai = accent-insensitive, ci = case-insensitive).
  • NEW: concepts/mysql-caching-sha2-password-auth — MySQL 8's new default authentication plugin and the cross-client-library compatibility fallout.
  • NEW: concepts/mysql-sql-mode — the server variable that controls SQL dialect strictness; its MySQL 8 default is strict enough that 5.7 queries often error.
  • NEW: concepts/mysql-reserved-words-upgrade-break — the class of upgrade-breakage where new reserved words collide with existing unquoted identifiers.
  • concepts/online-database-import — already canonical; reinforced with the "zero-downtime MySQL major-version upgrade" use-case.
  • concepts/backward-compatibility — already canonical; this post canonicalises the major-version-upgrade variant (vendor ships an intentionally-breaking release; each breaking axis is compensable but the migrator bears the cost).

Patterns extracted

No new reusable patterns from this post. The upgrade-workflow primitives (audit → remediate → upgrade → verify) are generic release-engineering discipline; the post's value is in enumerating the MySQL-specific audit axes (which are concepts, not patterns). The zero-downtime-upgrade-via-import path reuses concepts/online-database-import / systems/vitess-movetables already canonicalised from the 2026-02-16 petabyte-scale migration post.

Operational numbers

  • MySQL 5.7 end-of-life: October 2023 — named in the post as the forcing function. As of the 2026-04-23 ingest date, 2.5+ years past EoL; the post's urgency framing has compounded.
  • New reserved words in MySQL 8.0: ~40 (enumerated in the post; full list at dev.mysql.com keywords).

Caveats

  • Pedagogy voice, no production retrospective. No real-customer upgrade telemetry, no measured per-breaking-axis incidence rates, no post-upgrade-outage case studies. Every mitigation is stated as generic advice.
  • The deprecated-data-type list is opinion, not enforced. ENUM in particular is still heavily used in production MySQL schemas; the "use a lookup table with foreign keys" recommendation is a schema-design opinion, not a compatibility requirement.
  • No charset-migration cost discussion. The post gives the ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 recipe but doesn't mention that naive execution rewrites the entire table — a multi-hour-to-multi-day operation at scale. The canonical scale-aware execution (Vitess 21 programmatic text conversion) is not referenced.
  • No auth-plugin client-library compatibility matrix. Which client libraries shipped caching_sha2_password support in which year versions — left as "update your clients".
  • The PlanetScale closing pitch omits the migration audit burden. Importing to PlanetScale skips the 5.7 → 8.0 server upgrade, but the importing schema still has to audit reserved-word collisions + charset mismatches before the import succeeds — the "MySQL compatibility" reference link in the post acknowledges this in passing.
  • SQL-mode audit methodology absent. The post names ONLY_FULL_GROUP_BY as the most-common breakage but doesn't sketch how to enumerate the offending queries — this is actually the hardest part of the upgrade in practice.
  • Error-code changes not enumerated. The post points to the MySQL error-code reference page without listing the specific 5.7 → 8.0 diffs.
  • Re-surface date vs original publication. Published 2023-03-24, re-fetched via RSS on 2026-04-21 (nearly 3 years after publication). The "EoL slated for October 2023" phrasing is pre-EoL wording preserved from the original.

Source

Last updated · 378 distilled / 1,213 read