Changing the primary key of a MySQL table

In InnoDB the primary key is the table (the clustered index), so changing it means rewriting every row. Which variant you write decides whether writes keep flowing.

What the manual says

Source: manual, Primary key operations. * ADD PRIMARY KEY cannot run in place if columns must be converted to NOT NULL; in place it requires strict SQL mode and fails if the column contains NULLs.

What to do

• Never DROP PRIMARY KEY in one migration and ADD PRIMARY KEY in the next: the first is COPY-only and blocks writes, and the table briefly has no primary key (replication and gh-ost/pt-osc need one).
• Replace it in one statement and assert it: ALTER TABLE events DROP PRIMARY KEY, ADD PRIMARY KEY (uuid), ALGORITHM=INPLACE, LOCK=NONE; — still a full in-place rebuild (I/O, replica lag), but writes continue.
• For very large tables, use an online schema-change tool or expand/contract into a new table.
• Define the primary key at table creation whenever you can.

How Presift flags it

▲ MG002 WARNING  migration.sql:1
    ALTER TABLE events: Replacing the primary key rebuilds the table
    Why: DROP PRIMARY KEY together with ADD PRIMARY KEY runs in place but rebuilds the table; concurrent DML is permitted.
    Do:  The whole table is rewritten in place (writes continue, with I/O and replica lag cost). Add ALGORITHM=INPLACE, LOCK=NONE so MySQL errors instead of silently falling back to COPY.

A standalone DROP PRIMARY KEY is reported as an error (copy-only, writes blocked); ADD PRIMARY KEY alone as a warning (in-place rebuild).

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: error