October 13, 20263 min read

Review a MySQL Schema Change Before It Runs

Generate a tracking-code migration for MySQL, review the native SQL, and verify old orders, application updates, and a fresh replay.

Simon HartSolutions Engineer

Ptah helps you plan, review, and apply database migrations. Try in your browser

Two queued orders exist in MySQL. The application is about to start storing carrier tracking codes, but those old orders do not have codes yet. The first change is therefore a nullable column.

We ran the complete review and deployment path with Ptah 0.13.0 and MySQL 8.4.11. It generated a migration from the desired schema, applied the reviewed file, preserved the old orders, and reproduced the final structure on a second database. The example directory contains the SQL and actual results.

Describe the change on top of existing orders

Version 1 creates an InnoDB orders table with an ID and a status defaulting to queued. We applied it and inserted two rows. The desired schema adds tracking_code varchar(40) NULL:

CREATE TABLE orders (id bigint PRIMARY KEY, status varchar(20) NOT NULL DEFAULT 'queued', tracking_code varchar(40) NULL) ENGINE=InnoDB;

With DB_URL set to the disposable MySQL database, run:

ptah migrations generate --db-url "$DB_URL" --schema-file schema.sql --migrations-dir migrations --name tracking

The generated up file contains this statement, shown without its comments:

ALTER TABLE `orders` ADD COLUMN `tracking_code` varchar(40);

Review the actual dialect-specific SQL. This file adds a nullable column; it does not populate tracking codes, add uniqueness, or decide which carrier owns a code. Those are separate requirements. The interactive Assist example explores a more detailed carrier-and-code requirement on PostgreSQL.

The generated comments also carry timeout directives. Their presence is not a guarantee about the operation’s duration. Review MySQL’s locking and online-DDL requirements for the table before deploying to production.

Apply the reviewed artifact

Seal the files after review, then verify that checksum during deployment:

ptah migrations hash --dir migrations
ptah migrations up --db-url "$DB_URL" --migrations-dir migrations --verify-sum
ptah schema compare --db-url "$DB_URL" --schema-file schema.sql --exit-code

Both deployment and comparison exited 0. The comparison reported:

No schema differences detected.

We then used the MySQL client to read both orders, assign a code to the first, and read them again:

SELECT id, status, tracking_code FROM orders ORDER BY id;
UPDATE orders SET tracking_code='PKG-001',status='sent' WHERE id=1;
SELECT id,status,tracking_code FROM orders ORDER BY id;

The captured client output is:

id  status  tracking_code
1  queued  NULL
2  queued  NULL
id  status  tracking_code
1  sent  PKG-001
2  queued  NULL

Both original orders remained queued with NULL codes until the update. Afterward the first was sent with PKG-001, while the second was unchanged.

Keep the MySQL transaction boundary visible

MySQL’s DDL can cause implicit commits. Wrapping a migration in a transaction does not give a multi-statement schema change the same rollback behavior as PostgreSQL. The MySQL transaction documentation explains that boundary.

If a later statement fails, earlier DDL may remain committed. Review the recovery path for each statement in a multi-statement migration.

Verify the next environment can reproduce it

The second disposable database started empty. We applied the original migration plus the tracking migration, then compared the result:

ptah migrations up --db-url "$REPLAY_DB_URL" --migrations-dir migrations --verify-sum
ptah schema compare --db-url "$REPLAY_DB_URL" --schema-file schema.sql --exit-code

The replay and comparison both exited 0. This verifies the final schema from the same migration bytes. It does not copy the order rows from the first database or prove application compatibility under production traffic.

The useful review loop stays recognizable across engines: define the target, inspect the generated SQL, test the data requirement, and verify the deployed structure. The engine still decides locking and DDL behavior. The MySQL guide documents the supported surface.

Put Ptah to work

Example files

← All articles