Plan a Compatibility Assessment Before Migration or Mixed-Environment Operations

Learning outcomes

ServiceHub now has dozens of individual compatibility observations. The final risk is organizational: tests live in terminal history, no one knows which SQL statements were exercised, rollback means “switch DNS back,” and success means “the website opened.” A production migration needs a reproducible assessment that converts unknowns into explicit evidence and gates.

This lesson builds that assessment. You will inventory schema and storage engines, SQL and stored programs, accounts/authentication, configuration, data types/collations, connectors and ORMs, backup/restore tooling, binary logs/replication, observability, and performance. Then you will run a dry migration into a disposable target, define acceptance criteria, document rollback boundaries, and record unresolved dependencies instead of hiding them.

1. Start with an inventory that describes the workload, not just the database size

A database migration is an application-and-operations migration. A small database with stored programs, vendor-specific JSON, unusual collations, direct system-table writes, and custom authentication can be harder than a much larger schema using conservative SQL. The inventory must therefore capture dependencies that influence behavior, not only row counts and gigabytes.

2. Generate a target-neutral schema inventory with supported metadata interfaces

Use INFORMATION_SCHEMA and supported SHOW statements for observation. Avoid building the assessment around direct writes to vendor system tables. Store each query and its output with the server identity from Lesson 1 so results remain attributable.

SELECT TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_COLLATION,       TABLE_ROWS, DATA_LENGTH, INDEX_LENGTHFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME;SELECT TABLE_NAME, ORDINAL_POSITION, COLUMN_NAME,       DATA_TYPE, COLUMN_TYPE, IS_NULLABLE,       COLUMN_DEFAULT, EXTRA, CHARACTER_SET_NAME, COLLATION_NAMEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME, ORDINAL_POSITION;SELECT TABLE_NAME, INDEX_NAME, NON_UNIQUE, SEQ_IN_INDEX,       COLUMN_NAME, SUB_PART, INDEX_TYPEFROM information_schema.STATISTICSWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;SELECT CONSTRAINT_NAME, TABLE_NAME, CONSTRAINT_TYPEFROM information_schema.TABLE_CONSTRAINTSWHERE CONSTRAINT_SCHEMA='servicehub'ORDER BY TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME;

Treat TABLE_ROWS as engine-dependent metadata, not an exact row-count guarantee. For acceptance checks, run exact counts on selected critical tables or use purpose-built migration validation tooling at scale.

3. Stored objects and definers can break after a “successful” data copy

Views, routines, triggers, and scheduled events may carry DEFINER identities and SQL modes. A logical dump can restore data while a stored object fails because the definer account does not exist, lacks privileges, or uses syntax that diverged. Inventory these objects before migration and test creation under the target version.

SELECT ROUTINE_TYPE, ROUTINE_NAME, DEFINER, SQL_MODEFROM information_schema.ROUTINESWHERE ROUTINE_SCHEMA='servicehub'ORDER BY ROUTINE_TYPE, ROUTINE_NAME;SELECT TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE,       ACTION_TIMING, DEFINER, SQL_MODEFROM information_schema.TRIGGERSWHERE TRIGGER_SCHEMA='servicehub'ORDER BY TRIGGER_NAME;SELECT EVENT_NAME, DEFINER, STATUS, EVENT_DEFINITIONFROM information_schema.EVENTSWHERE EVENT_SCHEMA='servicehub'ORDER BY EVENT_NAME;SELECT TABLE_NAME, DEFINER, SECURITY_TYPE, VIEW_DEFINITIONFROM information_schema.VIEWSWHERE TABLE_SCHEMA='servicehub'ORDER BY TABLE_NAME;

Do not paste secrets or sensitive routine bodies into public tickets. Store migration evidence in an access-controlled location and redact only the sensitive values, not the existence of the dependency.

4. Account and authentication assessment is separate from schema export

Application connectivity can fail even when all tables restore correctly. Inventory the application account names/host patterns, authentication plugins, required TLS properties, active/default roles, and privileges using supported statements. Do not copy the raw contents of password/authentication fields between vendors as a generic migration technique.

SELECT USER(), CURRENT_USER(), CURRENT_ROLE();SHOW GRANTS FOR CURRENT_USER;-- As an administrator in the disposable lab, inspect explicit course accounts:SHOW CREATE USER 'servicehub_app'@'localhost';SHOW GRANTS FOR 'servicehub_app'@'localhost';SHOW CREATE USER 'servicehub_report'@'localhost';SHOW GRANTS FOR 'servicehub_report'@'localhost';

Real migrations should recreate accounts using target-supported authentication and least privilege, rotate credentials, and verify the application with the exact connector/TLS settings it will use after cutover. Chapter 14 will teach MariaDB security comprehensively; here the requirement is to make authentication a tested migration dependency.

5. Capture the SQL corpus—ORM migrations count as SQL dependencies too

A schema inventory cannot tell you which functions, hints, locking clauses, JSON expressions, generated-key APIs, or metadata queries the application executes. Build a representative SQL corpus from source code, ORM migration files, prepared statements, stored-program calls, slow/query logs where privacy policy permits, and integration tests. Parameterize it; never paste production secrets or personal data into a test fixture.

Classify statements by criticality and write expected results. Read queries need result assertions. Writes need affected-row, constraint, generated-key, warning/error, and transaction assertions. Administrative scripts need privilege and configuration expectations. A statement that is intentionally MySQL-specific should be marked for translation rather than silently excluded from coverage.

6. Dry-run logical migration: source remains untouched

For the small ServiceHub lab, a logical dump-and-restore is a clear, portable rehearsal. Use the dump utility that belongs to the source server and inspect its version. Do not assume a MariaDB dump utility is the correct tool for every MySQL source or that a MySQL dump utility emits target-compatible DDL for every feature.

# Source is MariaDB in this course lab. Use explicit tool identity.mariadb-dump --version# Dump the disposable ServiceHub database with stored objects.mariadb-dump   --single-transaction   --routines --events --triggers   --hex-blob   servicehub > servicehub-dryrun.sql# Restore into a disposable target database/server.mariadb --host=127.0.0.1 --port=3307 < servicehub-dryrun.sql

On Windows PowerShell the same programs can be called using their executable paths; output redirection syntax is supported, but ensure the shell encoding does not rewrite the SQL file. If the real source is MySQL, use a source-appropriate MySQL dump workflow and test the resulting artifact on the exact MariaDB target. Always inspect warnings and errors; a command exit code of zero is not the only validation signal.

7. Validate the restored target with invariants and DDL comparison

After restore, validate both structure and data. For the small lab, exact row counts and business invariants are appropriate. For large production databases, use scalable checksum/verification tooling and application-level reconciliation rather than one giant ad-hoc hash.

SELECT COUNT(*) AS customers FROM servicehub.customers;SELECT COUNT(*) AS technicians FROM servicehub.technicians;SELECT COUNT(*) AS work_orders FROM servicehub.work_orders;SELECT COUNT(*) AS events FROM servicehub.work_order_events;SELECT status, COUNT(*) AS work_ordersFROM servicehub.work_ordersGROUP BY statusORDER BY status;SELECT COUNT(*) AS orphan_eventsFROM servicehub.work_order_events eLEFT JOIN servicehub.work_orders w  ON w.work_order_id=e.work_order_idWHERE w.work_order_id IS NULL;SHOW CREATE TABLE servicehub.work_orders;SHOW CREATE TABLE servicehub.work_order_events;

Compare target results to source-captured expected values. If a difference is intentional—such as a collation or engine change—document the new expected behavior and the rationale. Acceptance is not “identical bytes”; it is “preserved required behavior with approved, explained differences.”

8. Performance assessment needs a baseline and disclosure

Do not declare MariaDB faster or slower from one laptop query. Choose critical workload slices, establish source latency/throughput/resource baselines, recreate representative data and indexes on the target, warm or cold caches intentionally, disclose hardware/container limits, and compare multiple runs. Plans may differ while performance remains acceptable—or plans may look similar while a regression appears under concurrency.

Define performance gates before testing. For example: p95 latency for the dispatch dashboard must not regress more than an approved threshold; bulk nightly import must finish inside its window; target CPU/I/O headroom must satisfy capacity policy. The actual thresholds belong to your system SLOs, not this course.

9. Rollback is a data problem after target writes begin

Before cutover, rollback can be simple: abandon the target and keep the source authoritative. After clients write to MariaDB, switching DNS back to MySQL can lose or fork those new writes unless you deliberately preserved a reverse synchronization path or can reconcile them. This is why migration plans define a point of no return and a rollback window.

A safe runbook therefore includes fencing: ensure only the intended side accepts writes during cutover. If your rollback strategy depends on reverse MariaDB→MySQL replication, Lesson 4 already showed that this is a separate, constrained direction that must be tested—not an assumption.

10. Deliberately wrong: in-place replacement with no tested return path

The riskiest shortcut is to stop MySQL, point MariaDB binaries at the existing data directory, start the service, fix whatever breaks, and call the old files a rollback plan. Modern cross-vendor migrations should not assume binary/data-directory interchangeability simply because very old MariaDB/MySQL releases were close. Independent data dictionary, feature, metadata, and engine evolution make exact-version migration guidance mandatory.

The repair is a dedicated disposable target, source-preserving backup, logical/replication migration method supported for the source/target pair, repeatable target build, application test suite, rehearsed cutover, and a rollback boundary defined before target writes begin. “We can probably reinstall the old package” is not rollback engineering.

11. Compatibility assessment template and acceptance gates

Every unknown needs an owner and resolution path. “Probably compatible” is not a status. If the business accepts a known difference, record who accepted it and what user-visible/operational effect is expected. This turns migration from tribal knowledge into a reviewable engineering decision.

12. Hands-on capstone lab for Chapter 02

Using the Chapter 01 ServiceHub lab, produce a small compatibility assessment folder outside the repository lessons: source-fingerprint.txt, target-fingerprint.txt, schema-inventory.sql, semantic-probes.sql, acceptance-results.md, and the disposable logical dump. Do not add secrets. The exact filenames are suggestions; the evidence is what matters.

Run the Lesson 1 identity matrix, Lesson 2 configuration inventory, Lesson 3 semantic probes, and—if two servers are available—the Lesson 4 replication/cutover rehearsal. Restore the database from scratch once to prove reproducibility. Then classify every dependency as passed, accepted difference, remediation, blocker, or unknown.

Verification checklist

• Exact source and target versions/builds are recorded.
• Schema engines, types, collations, indexes and stored objects are inventoried.
• Application SQL and connector/ORM versions are included in scope.
• Accounts/authentication/TLS assumptions are explicit.
• A dry-run migration restores successfully to a disposable target.
• Business invariants and target DDL are verified.
• Performance gates are defined from real SLOs, not generic percentages.
• Cross-vendor replication is either tested for the exact direction or explicitly not used.
• The point of no return and rollback data strategy are documented.
• Every unknown has an owner or an explicit risk acceptance.

13. Chapter summary and bridge to server architecture

Chapter 02 replaced “MariaDB is just MySQL” with an evidence model. You separated compatibility layers, stopped mapping version numbers, tested configuration divergence, exposed JSON and generated-column semantics, treated sequences and RETURNING as dialect choices, learned the directionality of mixed-vendor replication, and built a formal migration assessment with dry-run restore and rollback gates.

Chapter 03 now returns to MariaDB itself. With compatibility assumptions under control, you can inspect mariadbd startup, data directories, option-file precedence, threads/sessions, system/status variables, metadata, logs, time zones, character sets, and baseline configuration without carrying MySQL-specific administrative mechanisms forward by accident.