Migration Testing Checklist

-- CHECK 1: Record count matching
SELECT 'customers' AS tbl,
  (SELECT COUNT(*) FROM source_db.customers) AS src,
  (SELECT COUNT(*) FROM target_db.customers) AS tgt,
  (SELECT COUNT(*) FROM source_db.customers) -
  (SELECT COUNT(*) FROM target_db.customers) AS diff
UNION ALL
SELECT 'accounts',
  (SELECT COUNT(*) FROM source_db.accounts),
  (SELECT COUNT(*) FROM target_db.accounts),
  (SELECT COUNT(*) FROM source_db.accounts) -
  (SELECT COUNT(*) FROM target_db.accounts)
UNION ALL
SELECT 'transactions',
  (SELECT COUNT(*) FROM source_db.transactions),
  (SELECT COUNT(*) FROM target_db.transactions),
  (SELECT COUNT(*) FROM source_db.transactions) -
  (SELECT COUNT(*) FROM target_db.transactions);
-- All diff values should be 0

-- CHECK 2: Missing records
SELECT COUNT(*) AS missing_customers
FROM source_db.customers s
LEFT JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE t.customer_id IS NULL;
-- Should be 0

-- CHECK 3: Checksum comparison
SELECT 'source' AS db,
  SUM(CRC32(CONCAT_WS('|', customer_id, email, phone))) AS chk
FROM source_db.customers
UNION ALL
SELECT 'target',
  SUM(CRC32(CONCAT_WS('|', customer_id, email, phone)))
FROM target_db.customers;
-- Both checksum values should match

-- CHECK 4: Field-level differences
SELECT COUNT(*) AS email_mismatches
FROM source_db.customers s
JOIN target_db.customers t ON s.customer_id = t.customer_id
WHERE s.email != t.email;
-- Should be 0

-- CHECK 5: Financial totals match
SELECT
  'source' AS db, SUM(balance) AS total_balance
FROM source_db.accounts
UNION ALL
SELECT
  'target', SUM(balance)
FROM target_db.accounts;
-- Totals must match exactly for financial data

-- CHECK 6: Orphan records in all child tables
SELECT 'orders orphans' AS check_name,
  COUNT(*) AS orphan_count
FROM target_db.orders o
LEFT JOIN target_db.customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL
UNION ALL
SELECT 'txn orphans',
  COUNT(*)
FROM target_db.transactions t
LEFT JOIN target_db.accounts a ON t.account_id = a.account_id
WHERE a.account_id IS NULL;
-- All counts should be 0

-- CHECK 7: Foreign key constraints exist
SELECT COUNT(*) AS fk_count
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'target_db'
  AND REFERENCED_TABLE_NAME IS NOT NULL;
-- Compare with source FK count

-- CHECK 8: NOT NULL constraints preserved
SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'target_db'
  AND COLUMN_NAME IN ('email', 'customer_id', 'account_id', 'amount')
  AND IS_NULLABLE = 'YES';

-- CHECK 9: Index comparison
SELECT TABLE_NAME, COUNT(DISTINCT INDEX_NAME) AS idx_count
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'target_db'
GROUP BY TABLE_NAME
ORDER BY TABLE_NAME;
-- Compare with source index counts

-- CHECK 10: Verify optimizer statistics are fresh
ANALYZE TABLE target_db.customers;
ANALYZE TABLE target_db.accounts;
ANALYZE TABLE target_db.transactions;

-- CHECK 11: Critical query performance
-- Run benchmark queries and compare with pre-migration times
-- (See Performance Testing lesson for the full benchmark set)

-- CHECK 12: Foreign key checks re-enabled
SELECT @@FOREIGN_KEY_CHECKS;
-- Must be 1 (enabled)