MyISAM Legacy Characteristics, Table Locks, FULLTEXT History, and Migration Concerns

Learning outcomes

ServiceHub inherits a decade-old MariaDB database containing MyISAM tables. The original runbook says “MyISAM is faster for reads” and warns never to convert the search tables. That statement is not a migration plan. MyISAM predates modern InnoDB defaults and provides a very different correctness envelope: no SQL transactions, no foreign-key enforcement, table-level locking and crash repair based on engine-specific files/tools.

1. Inventory legacy tables before changing them

SELECT TABLE_SCHEMA,TABLE_NAME,ENGINE,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTHFROM information_schema.TABLESWHERE ENGINE='MyISAM'ORDER BY TABLE_SCHEMA,TABLE_NAME;SELECT ENGINE,TRANSACTIONS,XA,SAVEPOINTSFROM information_schema.ENGINESWHERE ENGINE IN ('MyISAM','Aria','InnoDB');

A migration starts with inventory because MyISAM may be present for very different reasons: legacy application assumptions, FULLTEXT history, copied static tables, old tools, or simply a default inherited years ago. Treat every reason as a hypothesis to test, not a permanent requirement.

2. Build a legacy table and prove rollback does not protect it

DROP DATABASE IF EXISTS servicehub_engines_lab;CREATE DATABASE servicehub_engines_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_engines_lab;CREATE TABLE technicians (  technician_id BIGINT NOT NULL PRIMARY KEY,  display_name VARCHAR(80) NOT NULL,  active BOOLEAN NOT NULL DEFAULT TRUE) ENGINE=InnoDB;INSERT INTO technicians VALUES (1,'Ava Chen',1),(2,'Mina Patel',1),(3,'Noah Smith',1);CREATE TABLE legacy_articles (  article_id BIGINT NOT NULL PRIMARY KEY,  title VARCHAR(200) NOT NULL,  body TEXT NOT NULL,  FULLTEXT KEY ft_article (title,body)) ENGINE=MyISAM;INSERT INTO legacy_articles VALUES (1,'Router reset','Reset edge router after saving configuration.'), (2,'Link diagnostics','Collect packet loss and latency before escalation.');SHOW CREATE TABLE legacy_articles\G

START TRANSACTION;UPDATE legacy_articles SET title='CORRUPTED TITLE' WHERE article_id=1;ROLLBACK;SHOW WARNINGS;SELECT article_id,title FROM legacy_articles WHERE article_id=1;

The row remains changed because MyISAM is non-transactional. If a transaction also changes InnoDB tables, ROLLBACK can revert the InnoDB part while leaving the MyISAM part committed, producing warning 1196 and a split business state. Lesson 5 makes that mixed-engine boundary explicit.

3. Table-level locking changes concurrency shape

MyISAM protects conflicting operations with table locks rather than InnoDB row locks/MVCC. That can be efficient for simple read-mostly patterns, but a writer can serialize access to the whole table. Use two sessions to make the scope visible.

Session A:LOCK TABLES legacy_articles WRITE;UPDATE legacy_articles SET title='Router reset procedure' WHERE article_id=1;-- hold the lockSession B:SELECT article_id,title FROM legacy_articles WHERE article_id=2;-- this waits even though it targets another rowSession A:UNLOCK TABLES;Session B:-- the read completes

The point is not that every MyISAM read always blocks every write. MyISAM has concurrent-insert behavior and lock scheduling details. The durable design fact is that its concurrency control is table-oriented, so contention patterns differ fundamentally from InnoDB.

4. FULLTEXT history is not a reason to freeze the engine

SELECT article_id,title,       MATCH(title,body) AGAINST ('router diagnostics' IN NATURAL LANGUAGE MODE) AS scoreFROM legacy_articlesWHERE MATCH(title,body) AGAINST ('router diagnostics' IN NATURAL LANGUAGE MODE)ORDER BY score DESC;

MyISAM historically provided FULLTEXT before InnoDB did, which explains many old schemas. Modern MariaDB supports FULLTEXT in other engines including InnoDB, so the presence of a FULLTEXT index is now a migration test case—not proof that MyISAM must remain. Compare tokenization, stopwords, collation, ranking and application result expectations on the exact source and target versions before cutover.

5. Files and repair are operational responsibilities

Traditional MyISAM tables use data and index files such as .MYD and .MYI alongside metadata. MariaDB provides tools including mariadb-check, myisamchk and myisampack. File-copy simplicity is useful only when you also respect clean shutdown/table flushing, permissions, open-file state and version/platform compatibility.

CHECK TABLE legacy_articles;SHOW TABLE STATUS LIKE 'legacy_articles'\G

6. Migration to InnoDB is a correctness project, not an ALTER slogan

CREATE TABLE legacy_articles_copy LIKE legacy_articles;INSERT INTO legacy_articles_copy SELECT * FROM legacy_articles;ALTER TABLE legacy_articles_copy ENGINE=InnoDB;SELECT COUNT(*) FROM legacy_articles;SELECT COUNT(*) FROM legacy_articles_copy;SHOW CREATE TABLE legacy_articles_copy\GCHECK TABLE legacy_articles_copy;

A production migration should be rehearsed on a restored copy with realistic data size. ALTER TABLE ... ENGINE=InnoDB rebuilds the table and can require substantial disk, I/O and metadata-lock planning. Never discover those requirements during the production maintenance window.

7. Deliberately wrong: “legacy benchmark says MyISAM is faster”

A benchmark from another hardware generation, data size or workload proves almost nothing about today’s ServiceHub. Even if a synthetic read test favors MyISAM, the engine can still be the wrong production choice if transactions, row-level concurrency, crash recovery, foreign keys, backups or Galera matter. The repair is a requirements matrix plus a representative benchmark that includes correctness and operational criteria—not throughput alone.

8. Checkpoint and cleanup

Keep the lab database for Lesson 3. The next lesson examines an even sharper persistence boundary: MEMORY tables survive as definitions after restart, but their rows do not.

8. Integrity and backup differences become migration acceptance criteria

MyISAM’s lack of foreign-key enforcement is not only a feature difference; it changes what bad data may already exist. Before converting a legacy parent/child schema to InnoDB and adding foreign keys, run orphan checks explicitly. A new constraint can fail because old rows violate a rule that the application merely intended to enforce. The repair sequence is inventory → detect violations → decide how to repair/archive them → convert → add constraints → verify application writes under the new rules.

Backup consistency must also be re-evaluated. A logical dump that reads several MyISAM tables while writers continue can observe different points in time unless the backup workflow coordinates locks appropriately. InnoDB’s transactional snapshot mechanisms solve a different problem and cannot be retroactively applied to MyISAM rows. The migration plan should therefore include at least one restore rehearsal from the legacy backup method and one from the target InnoDB method, with row counts, checksums/business totals, FULLTEXT behavior and application smoke tests.

Finally, benchmark the converted table after realistic warmup. InnoDB may consume storage and cache differently, while writes now pay for transactional logging and MVCC. Those costs are not defects—they purchase guarantees the old engine did not provide. The decision should compare total service behavior, not isolated SELECT throughput.

9. Foreign-key intent must become validated data before enforcement

Legacy MyISAM schemas often contain columns named like foreign keys even though the engine never enforced referential integrity. Before adding an InnoDB FOREIGN KEY, compare child keys against the parent table, quantify orphans, and decide whether each orphan is bad data, archived history, or a legitimate optional relationship. Only after cleanup should the constraint be added and tested with representative inserts/deletes. This turns an implicit application convention into an explicit database contract.

Authoritative references

• MariaDB Documentation — MyISAM Overview
• MariaDB Documentation — HIGH_PRIORITY and LOW_PRIORITY
• MariaDB Documentation — Choosing the Right Storage Engine