# Mariadb Agents Migration Validator > Use when validating a MySQL SQL dump or schema before importing into MariaDB, checking a codebase for MySQL-specific syntax that breaks on MariaDB, or producing a migration remediation checklist. Prevents the common mistake of importing a MySQL 8 dump with JSON-binary assumptions, caching_sha2_password users, INVISIBLE INDEX syntax, or GTID-continuity expectations. Covers a deterministic migration-validation procedure : scan SQL dump or schema for MySQL-specific divergences, flag JSON-storage / GTID-format / auth-plugin / INVISIBLE-vs-IGNORED / sequence-syntax / sql_mode issues, grade severity, produce a remediation list with skill-references to mariadb-impl-migration-mysql-to-mariadb and others. Keywords: migration validation, validate MySQL dump, check schema before import, MySQL to MariaDB compatibility check, will this dump import, migration remediation, MySQL-specific syntax, caching_sha2_password, INVISIBLE INDEX, JSON binary, GTID, pre-migration check, is my dump compatible - Skill: `impertio-studio/mariadb-agents-migration-validator` (Agent Skill, multi-file: 4 files) - Install (CLI): `npx skillmds@latest add impertio-studio/mariadb-agents-migration-validator` - Raw SKILL.md: https://api.skillmd.com/api/skills/impertio-studio/mariadb-agents-migration-validator/raw - Safety review: pending (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Data & Analytics - License: MIT - Author: Impertio-Studio (https://skillmd.com/u/impertio-studio) - Updated: 2026-09-21 - Page: https://skillmd.com/skills/impertio-studio/mariadb-agents-migration-validator --- # 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 : | 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).