Upgrading Legacy MySQL: From MyISAM to Modern MySQL 8.4

Executive Summary: Why Upgrade Legacy MySQL

Legacy MySQL databases running on MyISAM storage engine with implied foreign key relationships pose substantial risks to modern businesses. These systems lack data integrity guarantees, transaction support, and modern security features.

Key Migration Benefits

• Data Integrity: ACID compliance and proper foreign key constraints prevent data corruption
• Concurrent Access: Row-level locking instead of table-level locking
• Crash Recovery: Automatic crash recovery without manual table repairs
• Security: Transparent Data Encryption and role-based access control
• Modern SQL: Window functions, CTEs, JSON support not available in MySQL 4-5

Understanding the Legacy Database Problem

MyISAM Limitations

MyISAM was the default storage engine in MySQL 4 and 5.0, but has critical limitations:

• Table-Level Locking: Any write operation blocks the entire table
• No Transaction Support: No rollback capability for failed operations
• No Foreign Key Constraints: Referential integrity must be maintained by application code
• Corruption Risk: Tables frequently corrupt during crashes, requiring manual repair
• No Encryption: Data stored in plaintext on disk

Implied vs Explicit Foreign Keys

Legacy systems often use naming conventions to imply relationships rather than database constraints:

-- Legacy: implied relationship through column naming
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,  -- No actual constraint
    INDEX idx_customer (customer_id)
) ENGINE=MyISAM;

-- Modern: explicit foreign key constraint
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB;

Real-World Data Corruption Scenarios and MySQL 8 Solutions

When you understand how data corruption happens in legacy systems, you'll see why MySQL 8's modern features are so important. These scenarios illustrate common failure patterns in production systems and how modern MySQL prevents them.

Scenario 1: Partial Updates and the Double-Charge Problem

The Problem: Partial Updates Without Transactions

In a MyISAM-based e-commerce system, a customer purchase needs multiple table updates. When the server crashes mid-operation, customers get charged but orders aren't created:

-- Legacy MyISAM: no transaction support
-- Step 1: deduct from customer balance (SUCCEEDS)
UPDATE customer_accounts
SET balance = balance - 500.00
WHERE customer_id = 1234;

-- Step 2: create order record (SERVER CRASHES HERE)
INSERT INTO orders (customer_id, amount, status)
VALUES (1234, 500.00, 'pending');

-- Step 3: update inventory (NEVER EXECUTES)
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 5678;

-- Result: customer charged $500, no order created, inventory not updated
-- Customer service nightmare: "Where's my order? You took my money!"

The Solution: ACID Transactions in InnoDB

MySQL 8 with InnoDB ensures all operations succeed or all fail together:

-- Modern MySQL 8: full transaction support
START TRANSACTION;

-- All operations are atomic
UPDATE customer_accounts
SET balance = balance - 500.00
WHERE customer_id = 1234;

INSERT INTO orders (customer_id, amount, status)
VALUES (1234, 500.00, 'pending');

UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 5678;

-- If ANY step fails, ALL are rolled back
COMMIT;

-- With automatic rollback on errors
DELIMITER $
CREATE PROCEDURE safe_purchase(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Purchase failed - no charges made';
    END;

    START TRANSACTION;

    -- All succeed or all fail
    UPDATE customer_accounts
    SET balance = balance - p_amount
    WHERE customer_id = p_customer_id;

    INSERT INTO orders (customer_id, amount, status)
    VALUES (p_customer_id, p_amount, 'pending');

    UPDATE inventory
    SET quantity = quantity - 1
    WHERE product_id = p_product_id;

    COMMIT;
END$
DELIMITER ;

Scenario 2: Orphaned Orders When Foreign Keys Are Missing

The Problem: Data Integrity Without Constraints

Without foreign keys, deleting customers leaves orphaned orders. This causes reporting errors and legal compliance issues:

-- Legacy MyISAM: no foreign key support
-- Admin deletes inactive customer
DELETE FROM customers WHERE customer_id = 5000;

-- Orders still reference deleted customer
SELECT COUNT(*) FROM orders WHERE customer_id = 5000;
-- Returns a non-zero count - exact figure depends on order volume

-- Financial report crashes or shows incorrect totals
SELECT c.company_name, SUM(o.amount) as total_revenue
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id  -- NULL results!
GROUP BY c.customer_id;

-- GDPR compliance request fails
-- "Delete all my data" - but orders remain, violating privacy laws

The Solution: Foreign Key Constraints

MySQL 8 prevents orphaned records through enforced relationships:

-- Modern MySQL 8: foreign keys prevent orphans
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE RESTRICT;  -- Prevents deletion if orders exist

-- Attempting to delete customer with orders
DELETE FROM customers WHERE customer_id = 5000;
-- ERROR 1451: Cannot delete or update a parent row: foreign key constraint fails

-- For GDPR compliance: cascade delete when appropriate
ALTER TABLE customer_personal_data
ADD CONSTRAINT fk_personal_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE;  -- Personal data deleted with customer

-- For historical records: set NULL for archived data
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_archived
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE SET NULL;  -- Preserves order history without customer

Scenario 3: Invalid Data Without Check Constraints

The Problem: Business Rules Not Enforced

Application bugs or direct database access can insert invalid data that breaks business logic:

-- Legacy MySQL: no check constraints
-- Bug in application sets negative prices
UPDATE products SET price = -99.99 WHERE product_id = 100;
-- SUCCESS - database accepts negative price!

-- Promotional code sets discount over 100%
INSERT INTO promotions (code, discount_percent)
VALUES ('MEGA_SALE', 150);
-- SUCCESS - 150% discount means we pay customers!

-- Date logic error books appointment in the past
INSERT INTO appointments (customer_id, appointment_date)
VALUES (123, '2020-01-01');
-- SUCCESS - appointment scheduled 5 years ago!

-- Financial losses accumulate before detection
-- Customer gets paid nearly $100 to take the product!

The Solution: Check Constraints (MySQL 8.0.16+)

Database-level validation prevents invalid data no matter where it comes from:

-- Modern MySQL 8: check constraints enforce business rules
ALTER TABLE products
ADD CONSTRAINT chk_positive_price
CHECK (price >= 0),
ADD CONSTRAINT chk_price_range
CHECK (price <= 999999.99);

ALTER TABLE promotions
ADD CONSTRAINT chk_valid_discount
CHECK (discount_percent BETWEEN 0 AND 100);

ALTER TABLE appointments
ADD CONSTRAINT chk_future_appointment
CHECK (appointment_date >= CURDATE());

-- Invalid operations now fail immediately
UPDATE products SET price = -99.99 WHERE product_id = 100;
-- ERROR 3819: Check constraint 'chk_positive_price' is violated

INSERT INTO promotions (code, discount_percent) VALUES ('MEGA', 150);
-- ERROR 3819: Check constraint 'chk_valid_discount' is violated

-- Complex business rules
ALTER TABLE orders
ADD CONSTRAINT chk_order_logic CHECK (
    (status = 'cancelled' AND cancelled_at IS NOT NULL) OR
    (status != 'cancelled' AND cancelled_at IS NULL)
);

Scenario 4: Row-Level Locking and Inventory Races

The Problem: Table-Level Locks Cause Overselling

MyISAM's table-level locking creates race conditions where inventory goes negative:

-- Legacy MyISAM: table-level locking
-- Two customers buying the last item simultaneously

-- Customer A reads inventory (quantity = 1)
SELECT quantity FROM inventory WHERE product_id = 999;

-- Customer B reads inventory (quantity = 1)
SELECT quantity FROM inventory WHERE product_id = 999;

-- Customer A updates (locks entire table)
UPDATE inventory SET quantity = 0 WHERE product_id = 999;

-- Customer B waits for the lock, then updates
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 999;

-- Result: quantity = -1, oversold inventory!
-- Warehouse can't fulfil order, customer complaints

The Solution: Row-Level Locking with InnoDB

MySQL 8's row-level locking prevents race conditions. Pessimistic locking takes an explicit row lock and branches on the value whilst holding it, which needs a stored procedure rather than bare SQL:

-- Modern MySQL 8: row-level locking prevents overselling
-- Pessimistic locking: lock the row up front, then branch on it.
-- The branch on a locked value needs a stored program, not bare SQL,
-- so this is wrapped in a procedure rather than run as loose statements.
DELIMITER $
CREATE PROCEDURE reserve_stock(
    IN p_order_id INT,
    IN p_product_id INT
)
BEGIN
    DECLARE v_quantity INT;

    START TRANSACTION;

    -- Lock the specific row until this transaction completes
    SELECT quantity INTO v_quantity
    FROM inventory
    WHERE product_id = p_product_id
    FOR UPDATE;

    IF v_quantity >= 1 THEN
        UPDATE inventory
        SET quantity = quantity - 1
        WHERE product_id = p_product_id;

        INSERT INTO order_items (order_id, product_id, quantity)
        VALUES (p_order_id, p_product_id, 1);

        COMMIT;
    ELSE
        ROLLBACK;
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Product out of stock';
    END IF;
END$
DELIMITER ;

Optimistic locking skips the explicit lock and instead makes the availability check part of the UPDATE's WHERE clause, then checks whether it actually changed a row:

-- Optimistic locking with version numbers
ALTER TABLE inventory ADD COLUMN version INT DEFAULT 0;

UPDATE inventory
SET quantity = quantity - 1,
    version = version + 1
WHERE product_id = 999
    AND quantity >= 1
    AND version = @expected_version;

-- In application code, check ROW_COUNT(): zero means either the
-- stock ran out or another transaction updated the row first - retry
-- the read-and-update cycle rather than assuming the purchase succeeded

Scenario 5: Crash Recovery Without Manual Repair

The Problem: MyISAM Corruption After Crash

Server crashes leave MyISAM tables corrupted. You have to repair them manually and often lose data:

-- Legacy MyISAM: after an unexpected shutdown
-- Tables marked as crashed
SELECT table_name, table_comment
FROM information_schema.tables
WHERE engine = 'MyISAM' AND table_comment LIKE '%crashed%';

-- Manual repair required, and it can lose data
REPAIR TABLE orders;  -- Locks the table for the duration; large tables can take hours
-- Illustrative repair output - the exact figures vary by table:
-- Query OK, <rows_after> rows affected
-- Warning: Number of rows changed from <rows_before> to <rows_after>
-- Any shortfall between those two counts is data REPAIR TABLE could not recover

-- The application is effectively unavailable for the whole repair window,
-- and MyISAM gives no guarantee on how long that window will be

The Solution: InnoDB Automatic Crash Recovery

MySQL 8 automatically recovers from crashes without data loss:

-- Modern MySQL 8: automatic crash recovery
-- InnoDB uses write-ahead logging (redo logs)

-- After crash, automatic recovery on startup
-- MySQL error log shows:
-- InnoDB: Starting crash recovery
-- InnoDB: Reading redo log from checkpoint
-- InnoDB: Applying redo log records
-- InnoDB: Rollback of uncommitted transactions
-- InnoDB: Crash recovery completed - duration depends on redo log size

-- No data loss for committed transactions
SELECT COUNT(*) FROM orders;  -- All committed orders intact

-- Configure for faster recovery
SET GLOBAL innodb_fast_shutdown = 0;  -- Clean shutdown when possible
SET GLOBAL innodb_flush_log_at_trx_commit = 1;  -- Maximum durability
SET GLOBAL innodb_doublewrite = ON;  -- Prevent partial page writes

-- Point-in-time recovery with binary logs
-- Enable binary logging for full recovery capability
SET GLOBAL log_bin = ON;
SET GLOBAL binlog_format = 'ROW';

-- Recover to specific point before corruption
mysqlbinlog --stop-datetime="2024-12-01 10:00:00" \
    /var/log/mysql/binlog.000042 | mysql -u root -p

Scenario 6: Referential Actions Replace Manual Cascade Updates

The Problem: Manual Cascade Updates Miss Records

Without referential actions, updating primary keys means you have to manually update all related tables. This is error-prone:

-- Legacy: manual updates across tables
-- Company merger requires updating customer IDs

-- Update primary customer record
UPDATE customers SET customer_id = 9000 WHERE customer_id = 1000;

-- Must manually update every related table (error-prone)
UPDATE orders SET customer_id = 9000 WHERE customer_id = 1000;
UPDATE invoices SET customer_id = 9000 WHERE customer_id = 1000;
UPDATE support_tickets SET customer_id = 9000 WHERE customer_id = 1000;
-- Forgot customer_addresses table! Addresses now orphaned

-- Months later: customer can't access their addresses
-- Support confused: "Your addresses disappeared after the merger"

The Solution: Automatic Referential Actions

MySQL 8's CASCADE actions keep everything consistent across all tables:

-- Modern MySQL 8: automatic cascade updates
-- Define referential actions once
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer_cascade
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON UPDATE CASCADE;

ALTER TABLE invoices
ADD CONSTRAINT fk_invoices_customer_cascade
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON UPDATE CASCADE;

ALTER TABLE customer_addresses
ADD CONSTRAINT fk_addresses_customer_cascade
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON UPDATE CASCADE
ON DELETE CASCADE;  -- Addresses deleted with customer

-- Single update cascades everywhere
UPDATE customers SET customer_id = 9000 WHERE customer_id = 1000;
-- All related records automatically updated!

-- Verify cascade worked
SELECT 'orders' as table_name, COUNT(*) as updated_records
FROM orders WHERE customer_id = 9000
UNION ALL
SELECT 'invoices', COUNT(*)
FROM invoices WHERE customer_id = 9000
UNION ALL
SELECT 'addresses', COUNT(*)
FROM customer_addresses WHERE customer_id = 9000;

Pre-Migration Assessment

Before you migrate, check your database structure and find potential issues.

Inventory Storage Engines

-- Check which tables use MyISAM
SELECT
    table_name,
    engine,
    table_rows,
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
    AND engine = 'MyISAM'
ORDER BY size_mb DESC;

Find Orphaned Records

Identify records that would violate foreign key constraints:

-- Find child records without a valid parent
SELECT child.id, child.parent_id
FROM child_table child
LEFT JOIN parent_table parent ON child.parent_id = parent.id
WHERE parent.id IS NULL
    AND child.parent_id IS NOT NULL;

Detect Duplicate Keys

-- Find duplicates that would violate unique constraints
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Data Cleanup Before Migration

Clean data is essential for successful migration. Fix integrity issues before you convert storage engines.

Remove Orphaned Records

-- Delete orphaned child records
DELETE child FROM child_table child
LEFT JOIN parent_table parent ON child.parent_id = parent.id
WHERE parent.id IS NULL
    AND child.parent_id IS NOT NULL;

-- Or set to NULL if the relationship is optional
UPDATE child_table child
LEFT JOIN parent_table parent ON child.parent_id = parent.id
SET child.parent_id = NULL
WHERE parent.id IS NULL
    AND child.parent_id IS NOT NULL;

Handle Duplicate Records

-- Keep the oldest record, delete duplicates
DELETE t1 FROM users t1
INNER JOIN users t2
WHERE t1.email = t2.email
    AND t1.id > t2.id;

Fix Invalid Data Types

-- Find invalid dates (common in MySQL 4-5 era)
SELECT * FROM orders
WHERE order_date = '0000-00-00'
    OR order_date < '1970-01-01';

-- Update to NULL or a valid default
UPDATE orders
SET order_date = NULL
WHERE order_date = '0000-00-00';

Converting MyISAM to InnoDB

You need to convert the storage engine carefully to avoid locking issues and keep data consistent.

Basic Conversion

Changing the storage engine is always a full table copy in InnoDB - there's no in-place way to do it. ALGORITHM=INPLACE isn't supported for an ENGINE= change; it fails with error 1846, "ALGORITHM=INPLACE is not supported... Try ALGORITHM=COPY." Expect the table to be locked for the duration:

-- Convert single table - always a full table copy, never in-place
ALTER TABLE table_name ENGINE=InnoDB;

-- Explicit about the algorithm and lock; still a full copy under the hood
ALTER TABLE table_name ENGINE=InnoDB, ALGORITHM=COPY, LOCK=SHARED;

For tables too large to lock during business hours, don't run this directly against production. pt-online-schema-change and gh-ost both perform the copy against a shadow table in the background and swap it in with only a brief lock at the end.

Batch Conversion Script

-- Generate conversion statements for all MyISAM tables
SELECT CONCAT('ALTER TABLE ', table_name, ' ENGINE=InnoDB;') AS conversion_sql
FROM information_schema.tables
WHERE table_schema = DATABASE()
    AND engine = 'MyISAM'
ORDER BY table_rows ASC;  -- Convert smallest tables first

Configure InnoDB Settings

-- Key InnoDB settings for production
SET GLOBAL innodb_buffer_pool_size = 2147483648;  -- 2GB, adjust based on RAM
SET GLOBAL innodb_log_file_size = 536870912;      -- 512MB
SET GLOBAL innodb_flush_log_at_trx_commit = 1;    -- Full ACID compliance
SET GLOBAL innodb_file_per_table = ON;            -- Separate files per table

Implementing Foreign Key Constraints

After you convert to InnoDB, add explicit foreign key constraints to enforce referential integrity.

Add Foreign Keys with Cascading Rules

-- Add a foreign key with appropriate cascading behaviour
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT  -- Prevent deletion of customers with orders
ON UPDATE CASCADE;  -- Update customer_id if customer.id changes

-- For optional relationships
ALTER TABLE products
ADD CONSTRAINT fk_products_category
FOREIGN KEY (category_id) REFERENCES categories(id)
ON DELETE SET NULL  -- Set to NULL if category deleted
ON UPDATE CASCADE;

Verify Foreign Key Constraints

-- List all foreign keys in the database
SELECT
    constraint_name,
    table_name,
    column_name,
    referenced_table_name,
    referenced_column_name
FROM information_schema.key_column_usage
WHERE referenced_table_name IS NOT NULL
    AND table_schema = DATABASE();

MySQL 8.0+ Features for Legacy Databases

MySQL 8.0 introduced features that completely change what's possible compared to MySQL 4-5.

Common Table Expressions (CTEs)

You can replace complex nested subqueries with readable CTEs (MySQL 8.0+):

-- Legacy MySQL 4-5: nested subqueries
SELECT * FROM (
    SELECT customer_id, SUM(amount) as total
    FROM orders
    GROUP BY customer_id
) AS customer_totals
WHERE total > 1000;

-- Modern MySQL 8.0+: CTE
WITH customer_totals AS (
    SELECT customer_id, SUM(amount) as total
    FROM orders
    GROUP BY customer_id
)
SELECT * FROM customer_totals
WHERE total > 1000;

Window Functions

Analytics that were impossible or needed complex self-joins in MySQL 4-5:

-- Running total (impossible in MySQL 4-5 without variables)
SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Ranking within groups
SELECT
    category_id,
    product_name,
    price,
    RANK() OVER (PARTITION BY category_id ORDER BY price DESC) as price_rank
FROM products;

JSON Data Type

You can store and query semi-structured data (MySQL 5.7+):

-- Create table with JSON column
ALTER TABLE products ADD COLUMN attributes JSON;

-- Store structured data
UPDATE products
SET attributes = JSON_OBJECT(
    'color', 'red',
    'size', 'large',
    'features', JSON_ARRAY('waterproof', 'lightweight')
);

-- Query JSON data
SELECT product_name
FROM products
WHERE JSON_EXTRACT(attributes, '$.color') = 'red';

Check Constraints

Enforce business rules at the database level (MySQL 8.0.16+):

-- Add check constraints
ALTER TABLE products
ADD CONSTRAINT chk_positive_price CHECK (price > 0),
ADD CONSTRAINT chk_valid_status CHECK (status IN ('active', 'inactive', 'discontinued'));

ALTER TABLE orders
ADD CONSTRAINT chk_valid_dates CHECK (ship_date >= order_date);

Instant DDL Operations

Make schema changes without table locks (MySQL 8.0+):

-- Add a column instantly (no table rebuild)
ALTER TABLE large_table
ADD COLUMN new_field VARCHAR(100) DEFAULT NULL,
ALGORITHM=INSTANT;

-- Operations that support the INSTANT algorithm:
-- - Adding a column (with restrictions), MySQL 8.0.12+
-- - Renaming a column, MySQL 8.0+
-- - Setting/dropping column default values, MySQL 8.0+
-- - Dropping a column, MySQL 8.0.29+

Performance Features in Modern MySQL

Invisible Indexes

Test how removing an index affects performance without actually dropping it (MySQL 8.0+):

-- Make an index invisible to test its performance impact
ALTER TABLE orders ALTER INDEX idx_customer_id INVISIBLE;

-- Check whether queries still perform well
-- If yes, drop the index; if no, make it visible again
ALTER TABLE orders ALTER INDEX idx_customer_id VISIBLE;

Descending Indexes

Optimise queries with DESC order (MySQL 8.0+):

-- Create a descending index for queries that sort DESC
CREATE INDEX idx_created_desc ON posts(created_at DESC);

-- This query now uses the index efficiently
SELECT * FROM posts ORDER BY created_at DESC LIMIT 10;

Histogram Statistics

Get better query optimisation for skewed data (MySQL 8.0+):

-- Create a histogram for better statistics
ANALYZE TABLE orders UPDATE HISTOGRAM ON status;

-- View histogram information
SELECT * FROM information_schema.column_statistics
WHERE table_name = 'orders' AND column_name = 'status';

Security Enhancements

Role-Based Access Control

Simplify permission management (MySQL 8.0+):

-- Create roles
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- Grant permissions to roles
GRANT SELECT ON mydb.* TO 'app_read';
GRANT INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
GRANT ALL ON mydb.* TO 'app_admin';

-- Assign roles to users
GRANT 'app_read' TO 'reader_user'@'localhost';
GRANT 'app_read', 'app_write' TO 'app_user'@'localhost';

Password Validation

Enforce strong passwords (MySQL 5.6+, better in 8.0):

-- Install and configure password validation
INSTALL COMPONENT 'file://component_validate_password';

SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

Transparent Data Encryption

Encrypt data at rest (InnoDB, MySQL 5.7+):

-- Enable encryption for new tables
SET GLOBAL default_table_encryption=ON;

-- Encrypt an existing table
ALTER TABLE sensitive_data ENCRYPTION='Y';

-- Verify encryption status
SELECT table_name, create_options
FROM information_schema.tables
WHERE create_options LIKE '%ENCRYPTION%';

Migration Validation

After migration, make sure all changes worked.

Verify Storage Engines

-- Confirm all tables use InnoDB
SELECT table_name, engine
FROM information_schema.tables
WHERE table_schema = DATABASE()
    AND engine != 'InnoDB';

Check Foreign Key Integrity

-- Test that foreign key constraints are working
-- This should fail if the constraint is active
INSERT INTO orders (customer_id, amount)
VALUES (99999, 100.00);  -- Non-existent customer

Performance Comparison

-- Compare query performance
-- Before: table lock wait
SHOW STATUS LIKE 'Table_locks_waited';

-- After: row lock wait (should be much lower)
SHOW STATUS LIKE 'Innodb_row_lock_waits';

Conclusion: Modernising Your Database

Upgrading from MyISAM to InnoDB with modern MySQL 8.4 features turns a fragile legacy database into a secure, dependable one, removing data corruption risks through ACID compliance, enabling concurrent access through row-level locking, and providing modern SQL capabilities that were simply impossible in MySQL 4-5.

The largest practical risk in any of this is the engine conversion itself. On a table of any real size, ENGINE=InnoDB is a blocking table copy rather than an in-place operation, so schedule it for a maintenance window or run it through pt-online-schema-change or gh-ost rather than firing it at a live production table.