/aurora-dsql

MySQL to DSQL Migration: DDL Operations

Migration patterns for specific MySQL DDL operations to DSQL-compatible equivalents.

MUST read type-mapping.md first for data type mappings and the CRITICAL Destructive Operations Warning. MUST read ddl-migrations/overview.md for the general Table Recreation Pattern and user verification requirements.

Table Recreation Pattern Overview

MUST follow this sequence with user verification at each step:

1. Plan & Confirm - MUST present migration plan and obtain user approval to proceed
2. Validate - Check data compatibility with new structure; MUST report findings to user
3. Create - Create new table with desired structure; MUST verify with user before execution
4. Migrate - Copy data (batched for tables > 3,000 rows); MUST report progress to user
5. Verify - Confirm row counts match; MUST present comparison to user
6. Swap - CRITICAL: MUST obtain explicit user confirmation before DROP TABLE
7. Re-index - Recreate indexes using ASYNC; MUST confirm completion with user

Transaction Rules

• MUST batch migrations exceeding 3,000 row mutations
• PREFER batches of 500-1,000 rows for optimal throughput
• MUST respect 10 MiB data size per transaction
• MUST respect 5-minute transaction duration

Common Verify & Swap Pattern

All migrations end with this pattern (referenced in examples below).

CRITICAL: MUST obtain explicit user confirmation before DROP TABLE step.

-- MUST verify counts match
SELECT COUNT(*) FROM target_table;
SELECT COUNT(*) FROM target_table_new;

-- CHECKPOINT: MUST present count comparison to user and obtain confirmation
-- Agent MUST display: "Original table has X rows, new table has Y rows.
-- Proceeding will DROP the original table. This action is IRREVERSIBLE.
-- Do you want to proceed? (yes/no)"
-- MUST NOT proceed without explicit "yes" confirmation

-- MUST swap tables (DESTRUCTIVE - requires user confirmation above).
-- Each DDL below MUST run in its own transaction (DSQL: one DDL per
-- transaction):
DROP TABLE target_table;
ALTER TABLE target_table_new RENAME TO target_table;

-- MUST recreate indexes (each in its own transaction):
CREATE INDEX ASYNC idx_target_tenant ON target_table(tenant_id);

Detailed Migration Patterns

Load the relevant file for the specific MySQL DDL operation you need to migrate:

• ddl-column-changes.md — ALTER COLUMN type, DROP COLUMN
• ddl-auto-increment.md — AUTO_INCREMENT to UUID/IDENTITY/SEQUENCE
• ddl-type-alternatives.md — ENUM, SET, ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY
• ddl-constraints.md — SET/DROP NOT NULL, SET/DROP DEFAULT
• ddl-structural.md — ADD/DROP CONSTRAINT, MODIFY PRIMARY KEY
• ddl-batching.md — Batched migration pattern, error handling and recovery