Migrating MyISAM applications to InnoDB

ALTER TABLE … ENGINE=InnoDB is necessary and not sufficient.

Pre-flight

SELECT table_schema, table_name
FROM information_schema.tables
WHERE engine='MyISAM';

Then:

1. Inventory fulltext indexes — plan InnoDB/MariaDB fulltext or external search
2. Find code that assumed MyISAM table locks for “serialisation”
3. Budget disk — InnoDB row formats are larger
4. Require explicit primary keys (why)
5. Measure hot tables; plan online conversion order

Convert

ALTER TABLE orders ENGINE=InnoDB, ROW_FORMAT=DYNAMIC;

Large tables: pt-online-schema-change (or equivalent) with replication lag guards. Convert replicas first when topology allows rehearsal.

Application changes

• Wrap multi-statement work in transactions
• Handle deadlocks and lock waits (retry guide)
• Re-test COUNT(*)-heavy dashboards under MVCC expectations
• Replace accidental reliance on table-level locking

Rollback

Keep tested restores until soak ends. Prefer row-based replication through the cutover. Do not call the migration done until a crash/restart drill on a converted copy succeeds.