SuperAPI AI Directory
Partner with us中文AI newsFavorites
Skillsmariadb-agents-migration-validator

mariadb-agents-migration-validator

Data & analytics

View repository

Updated 10-15 22:18

About this skill

mariadb-agents-migration-validator

A deterministic procedure for validating a MySQL SQL dump, schema, or codebase BEFORE importing into MariaDB. This is an ORCHESTRATION skill : it does not teach migration, it applies a fixed 10-dimension scan and produces a graded validation report. Every finding cites the canonical skill that explains the fix and the exact MySQL-vs-MariaDB divergence.

Quick Reference

  • Scan ALL 10 divergence dimensions in order. NEVER skip a dimension because the dump "looks standard".
  • Grade every finding with one of three severities : BLOCKER, WARNING, SUGGESTION.
  • BLOCKER : the dump will not import correctly or apps will break. Examples : caching_sha2_password users, INVISIBLE INDEX keyword, GTID-continuity expectation, GROUPS window frame, MySQL 8 functional-index syntax.
  • WARNING : the dump imports but carries a known footgun. Examples : JSON columns (no enforcement after import), sql_mode default differences, non-InnoDB storage engines.
  • SUGGESTION : a non-blocking improvement. Examples : utf8 (utf8mb3) charset, AUTO_INCREMENT that could become a sequence.
  • MariaDB JSON is a LONGTEXT alias, NOT native binary. Use CHECK (JSON_VALID(col)) for structure ; use functional indexes on virtual columns for index access. A migrated JSON column has NO write-time validation until the CHECK is added. (D-010, L-005)
  • Every finding cites the canonical skill that explains the fix. NEVER report a divergence without a skill-ref.
  • Output a single findings table : finding, severity, location, MySQL-vs-MariaDB divergence, remediation step, skill-ref. Close with a verdict line.
  • ALWAYS recommend running mariadb-upgrade exactly once AFTER the import, regardless of the verdict.
  • A clean dump still gets a report : the table has zero rows and the verdict is PASS.

The 10 Divergence Dimensions

# Dimension What it flags Severity Canonical skill
1 JSON columns every JSON column ; needs CHECK (JSON_VALID(col)) post-import WARNING mariadb-syntax-json
2 Authentication caching_sha2_password users ; no such plugin in MariaDB BLOCKER mariadb-core-security-model
3 GTID any expectation of GTID continuity ; format incompatible BLOCKER mariadb-core-replication-model
4 INVISIBLE INDEX MySQL 8 INVISIBLE keyword ; MariaDB uses IGNORED BLOCKER mariadb-syntax-indexing
5 sql_mode MySQL 8 sql_mode defaults that differ WARNING mariadb-core-defaults-and-sql-modes
6 Sequences MySQL has only AUTO_INCREMENT ; MariaDB CREATE SEQUENCE differs SUGGESTION mariadb-syntax-sql-ddl
7 Functional indexes MySQL 8 expression-index syntax ; needs virtual column BLOCKER mariadb-syntax-indexing
8 Storage engine non-InnoDB engines that may not exist on the target WARNING mariadb-core-storage-engines
9 Window / CTE GROUPS window frame ; not in MariaDB BLOCKER mariadb-syntax-window-and-cte
10 Charset utf8 (utf8mb3) usage ; recommend utf8mb4 SUGGESTION mariadb-errors-encoding-and-collation

The full pattern-detection rule set per dimension (regex hint + IF condition THEN severity) is in references/methods.md.

Validation Decision Tree

For the supplied dump / schema / codebase :

  1. Scan every column definition for the JSON type :
       JSON column found -> WARNING
         divergence : MySQL stores JSON as packed binary, MariaDB stores it as
                      LONGTEXT with NO write-time validation.
         remediation : after import, ADD CONSTRAINT ... CHECK (JSON_VALID(col)).
       -> ref mariadb-syntax-json

  2. Scan CREATE USER / ALTER USER / mysql.user rows for the auth plugin :
       IDENTIFIED WITH caching_sha2_password   -> BLOCKER
       IDENTIFIED WITH sha256_password         -> BLOCKER
         divergence : MariaDB ships ed25519, mysql_native_password, unix_socket ;
                      it does NOT ship caching_sha2_password or sha256_password.
         remediation : drop those users, re-create with ed25519.
       -> ref mariadb-core-security-model

  3. Inspect the migration intent for GTID continuity :
       plan expects GTID-tracked replication MySQL <-> MariaDB -> BLOCKER
         divergence : MySQL GTID is uuid:seqno ; MariaDB GTID is
                      domain-server-sequence. They are NOT interconvertible.
         remediation : one-way dump-and-load only ; if a cut-over replica is
                       needed, use binlog-positional replication (MASTER_USE_GTID=NO).
       -> ref mariadb-core-replication-model

  4. Scan DDL and app code for the INVISIBLE keyword on an index :
       INDEX ... INVISIBLE  /  ALTER INDEX idx INVISIBLE   -> BLOCKER
         divergence : MySQL 8 uses INVISIBLE ; MariaDB 10.6+ uses IGNORED.
         remediation : rewrite INVISIBLE to IGNORED.
       -> ref mariadb-syntax-indexing

  5. Compare the dump's expected sql_mode against MariaDB defaults :
       SET sql_mode containing MySQL-8-only modes, or relying on MySQL-8 default
       set differing from MariaDB -> WARNING
         remediation : review and set sql_mode explicitly post-import.
       -> ref mariadb-core-defaults-and-sql-modes

  6. Note AUTO_INCREMENT columns :
       AUTO_INCREMENT present -> SUGGESTION (usually no action)
         divergence : MariaDB additionally has CREATE SEQUENCE ; MySQL does not.
         remediation : keep AUTO_INCREMENT as-is ; convert deliberately later if wanted.
       -> ref mariadb-syntax-sql-ddl

  7. Scan index definitions for MySQL 8 functional-index syntax :
       INDEX / KEY with a parenthesised expression, e.g. (( col1 + col2 ))  -> BLOCKER
         divergence : MariaDB does NOT support inline expression indexes.
         remediation : add a generated (virtual) column, then index that column.
       -> ref mariadb-syntax-indexing

  8. Scan ENGINE= clauses :
       ENGINE not in {InnoDB, Aria, MyISAM, MEMORY, CSV, ARCHIVE} -> WARNING
       ENGINE=ROCKSDB / SPIDER / CONNECT -> WARNING (plugin must be installed)
         remediation : confirm the engine plugin exists on the target, or
                       convert to InnoDB.
       -> ref mariadb-core-storage-engines

  9. Scan window-function clauses for the frame unit :
       OVER ( ... GROUPS BETWEEN ... ) -> BLOCKER
         divergence : MariaDB supports ROWS and RANGE frames only ; GROUPS is a
                      MySQL 8 / PostgreSQL feature absent from MariaDB.
         remediation : rewrite the frame using ROWS or RANGE.
       -> ref mariadb-syntax-window-and-cte

 10. Scan CHARSET / COLLATE clauses :
       CHARACTER SET utf8 / utf8mb3 -> SUGGESTION
         remediation : convert to utf8mb4 to store the full Unicode range.
       -> ref mariadb-errors-encoding-and-collation

JSON Storage Caveat

MariaDB JSON is a LONGTEXT alias, not native binary. Use CHECK (JSON_VALID(col)) for structure ; use functional indexes on virtual columns for index access. A MySQL 8 dump validates JSON on every write ; once imported into MariaDB the same column accepts 'not even json' silently. Dimension 1 therefore grades a JSON column as a WARNING, and the remediation step ALWAYS includes adding the CHECK.

Output Format

Always produce a findings table, then a verdict, then the standing recommendation. Severity sort order : BLOCKER first, then WARNING, then SUGGESTION.

## Migration Validation : <dump name or "supplied schema">

| Finding | Severity | Location | MySQL vs MariaDB divergence | Remediation | Skill ref |
|---------|----------|----------|-----------------------------|-------------|-----------|
| caching_sha2_password user | BLOCKER | `CREATE USER 'app'@'%'` | MariaDB has no caching_sha2_password plugin | Drop user, re-create IDENTIFIED VIA ed25519 | mariadb-core-security-model |
| JSON column | WARNING | `orders.payload` | MariaDB stores JSON as LONGTEXT, no write validation | Add CHECK (JSON_VALID(payload)) after import | mariadb-syntax-json |
| ... | ... | ... | ... | ... | ... |

**Verdict** : FAIL (1 blocker, 1 warning) | Re-validate after blockers are remediated.

**Standing recommendation** : run `mariadb-upgrade` exactly once after the import completes. See mariadb-impl-migration-mysql-to-mariadb.

Verdict rules :

  • Any BLOCKER -> FAIL. The dump must not be imported until blockers are remediated.
  • Zero BLOCKER, one or more WARNING -> PASS WITH WARNINGS. May import, but warnings carry documented cost.
  • Zero BLOCKER and zero WARNING -> PASS.
  • In every case, append the standing recommendation to run mariadb-upgrade once after import.

Cross-Reference Map

This skill orchestrates and references the following skills. When a finding needs more depth than the remediation column allows, point the user at the cited skill :

  • mariadb-impl-migration-mysql-to-mariadb : the full migration workflow, in-place upgrade vs dump-and-restore, and the mariadb-upgrade step.
  • mariadb-syntax-json : JSON-as-LONGTEXT, CHECK (JSON_VALID()), virtual-column functional indexes.
  • mariadb-core-security-model : user model, ed25519, mysql.global_priv vs mysql.user.
  • mariadb-core-replication-model : MariaDB GTID format, why MySQL GTID does not bridge.
  • mariadb-syntax-indexing : IGNORED keyword, virtual-column indexes replacing functional indexes.
  • mariadb-core-defaults-and-sql-modes : sql_mode defaults per version.
  • mariadb-syntax-sql-ddl : AUTO_INCREMENT, CREATE SEQUENCE differences.
  • mariadb-core-storage-engines : engine availability, InnoDB vs alternatives.
  • mariadb-syntax-window-and-cte : supported window frames (ROWS, RANGE ; no GROUPS).
  • mariadb-errors-encoding-and-collation : utf8mb3 vs utf8mb4.

When NOT to Use This Skill

  • For actually performing the migration : use mariadb-impl-migration-mysql-to-mariadb.
  • For reviewing a native MariaDB schema (no MySQL origin) : use mariadb-agents-schema-reviewer.
  • This skill scans for MySQL-vs-MariaDB divergence only ; it does not profile queries or tune the server.

References

  • references/methods.md : the complete 10-dimension scan procedure, each dimension as a deterministic IF / THEN rule with severity, regex / pattern hint, the exact MySQL-vs-MariaDB divergence, and skill-ref.
  • references/examples.md : 8 worked validations including a MySQL 8 dump with JSON + caching_sha2_password, an INVISIBLE INDEX dump, a clean 5.7 dump, a GROUPS-frame view, a functional-index dump, and a remediation checklist.
  • references/anti-patterns.md : 6 validator anti-patterns, why each one ships a broken import, and the correct procedure.

Source Verification

  • https://mariadb.com/docs/release-notes/community-server/about/compatibility-and-differences/mariadb-vs-mysql-compatibility : JSON stored as TEXT/BLOB (not packed binary), GTID incompatibility, sha256_password absence.
  • https://mariadb.com/kb/en/ignored-indexes/ : IGNORED keyword (10.6+), ALTER TABLE ... ALTER INDEX key_name IGNORED syntax, distinction from MySQL 8 INVISIBLE.
  • Vooronderzoek docs/research/vooronderzoek-mariadb.md §6 (Migration MySQL to MariaDB), migration divergence table.
  • LESSONS L-002 (no GROUPS frame), L-004 (GTID incompatible), L-005 (JSON LONGTEXT alias), L-006 (IGNORED vs INVISIBLE).
  • DECISIONS D-010 (LONGTEXT-alias must appear in Quick Reference of every JSON-touching skill).