[Bug]: ADD COLUMN on content is a 7-minute table rebuild; the deploy timeout kills it mid-migration and wedges the environment

[dqnykamp]

Summary

content carries two FULLTEXT indexes, which prevent InnoDB from using ALGORITHM=INSTANT or INPLACE for ADD COLUMN. Every added column is therefore a full ALGORITHM=COPY table rebuild — measured at 7 min 3 s on dev3 (383 MB data, 55,403 rows), during which the table is read-only, blocking all writes.

The backend deploy allows 5 minutes. So the migration is killed mid-rebuild. MySQL has no transactional DDL, so the schema change commits but Prisma never records completion, leaving _prisma_migrations with an unfinished row. From then on every deploy fails with P3009 — including a rollback to main — until a human runs prisma migrate resolve by hand.

This is not specific to the column that triggered it. content is the most-modified table in the schema, so every future ADD COLUMN on it will hit this.

Steps to reproduce

1. Add a migration containing ALTER TABLE content ADD COLUMN … ; and deploy the backend.
2. entrypoint.sh runs prisma:deploy before npm run start, so nothing listens on :3000 and ALB health checks cannot pass while the rebuild runs.
3. At 5 minutes update-cluster-with-rollback reports "Deployment did not stabilize" and rolls back; ECS kills the task mid-rebuild.
4. Trigger any further deploy — the same image, another branch, or a revert to main.

Expected behavior

A schema migration should either complete within the deploy budget or fail cleanly, leaving the environment deployable and the previous image serving.

Actual behavior

All later deploys die in the entrypoint with P3009. Recovery requires ECS-exec into a running task and a manual prisma migrate resolve --applied. dev3 was undeployable from 02:30 UTC until cleared by hand.

Doenet version or commit

f3372db72 (PR #3026)

Browser / OS / Node version

dev3 · MySQL 8.0.46 on ECS Fargate · Node 24.15

Relevant logs

# API task stdout — last line before the 5-minute kill
Applying migration `20260814120000_problem_set_description_doc`

# the retry, and every deploy after it
Error: P3009
migrate found failed migrations in the target database, new migrations will not be applied.
The `20260814120000_problem_set_description_doc` migration started at 2026-08-15 02:30:26.559 UTC failed

# _prisma_migrations after the kill — DDL committed, bookkeeping lost
started_at: 2026-08-15T02:30:26.559Z   finished_at: null
applied_steps_count: 0                 logs: null
-- while SHOW COLUMNS FROM content LIKE 'isDescription' returns the column

# reproduced directly on dev3 with a throwaway column
| 212 | doenet | Query | 151 | copy to tmp table | ALTER TABLE content ADD COLUMN _mdl_test ... |
Query OK, 55403 rows affected (7 min 3.419 sec)

# MySQL naming the cause
mysql> ALTER TABLE content ADD COLUMN _x BOOLEAN NOT NULL DEFAULT false, ALGORITHM=INSTANT;
ERROR 1846 (0A000): ALGORITHM=INSTANT is not supported. Reason: InnoDB presently
supports one FULLTEXT index creation at a time. Try ALGORITHM=COPY/INPLACE.

Additional context

Ruled out along the way: there was no lock contention — performance_schema.metadata_locks showed only the ALTER's own granted locks with nothing pending, and no long-lived transactions existed. The MySQL service was steady throughout. The migration was doing real work the entire time.

Three contributing factors, roughly in order of leverage:

1. content cannot take instant DDL, because of content_name_idx and content_source_idx. Moving full-text search off the table — a separate search table, or an external index — would make ADD COLUMN on content a millisecond operation permanently. Biggest payoff, and the biggest decision.
2. Migrations run in the app entrypoint, so they are gated by the health-check clock and a long migration is indistinguishable from an unhealthy container. Running them once, before the service update, would decouple the two.
3. timeout-minutes defaults to 5 (not overridden in reusable-deploy-backend.yml) while healthCheckGracePeriodSeconds is also 300 — the deploy gives up exactly when ECS stops being lenient. Mitigation only; 7 minutes already exceeds it.

Note for prod: its content is presumably larger than dev3's, so the rebuild runs longer than 7 minutes, with writes blocked throughout and rollback unavailable if it wedges. Until (1) or (2) lands, any migration touching content needs applying out-of-band, in a window where a write freeze is acceptable.

Filed from the investigation in #3026, whose migration surfaced this.