Data migration compatibility cookbook

This cookbook is a pre-migration checklist for applications and schemas that must run on both MySQL and MatrixOne. It complements the source-specific guides in this section. It is intentionally stricter than a syntax check: some SQL accepted by a particular MatrixOne release can still have different semantics, operational behavior, or migration tooling support.

Scope and validation version

The validation examples in this page were run against MatrixOne v4.2.6 on 2026-10-28. Re-run the checks against the exact MatrixOne version used for a migration. A successful probe is not a promise that every function, driver, or backup workflow is portable.

Migration compatibility restrictions

Area

Migration rule

Portable pattern

Stored procedures and triggers

Do not put application behavior in stored procedures or triggers.

Keep business logic and integrity actions in the application or migration tool.

Table locks

Do not use LOCK TABLES or UNLOCK TABLES.

Use transactions and application-level coordination.

Cascading foreign keys

Do not rely on ON DELETE CASCADE or ON UPDATE CASCADE.

Delete or update dependent rows explicitly in the application, and verify the affected-row count.

Character set

Use only utf8mb4; use utf8mb4_bin unless a documented collation requirement says otherwise.

Set the connection, database, table, and client configuration explicitly.

Upsert

Do not depend on ON DUPLICATE KEY UPDATE or REPLACE.

Use an explicit read-then-insert/update workflow inside a transaction, with a unique key and retry handling.

Boolean values

Do not rely on a driver-specific binding for BOOLEAN.

Write and read 0/1 explicitly, or normalize the value in the application.

Identifiers

Do not use Chinese database or table names.

Use ASCII snake_case names for databases, tables, columns, indexes, and constraints.

Auto-increment

Do not define a custom AUTO_INCREMENT starting value as part of the migration contract.

Let MatrixOne allocate new IDs, or migrate stable IDs explicitly and keep a separate business key.

Logical backup

Do not use mysqldump against MatrixOne.

Use mo-dump; use the documented mobackup workflow for physical backups.

Window and JSON functions

Do not assume MySQL function compatibility from the function name alone.

Run the exact query and result checks on the target MatrixOne version before release.

Events and stored functions

Do not migrate MySQL EVENT, event_scheduler, or MySQL-style compound stored-function bodies as if they were portable.

Use MatrixOne SQL Tasks or SQL/Python functions only after reviewing their different syntax and lifecycle rules.

JSON coverage

Do not assume the full MySQL JSON surface is available. JSON_SEARCH, JSON_TABLE, JSON_ARRAYAGG, and JSON_OBJECTAGG require separate review.

Test every production JSON expression and compare both results and errors.

Spatial features

Do not treat parser-accepted MySQL spatial types or GIS functions as functional support.

Replace them or pass a functional query test on the target version.

Indexes

Do not use functional or expression indexes in a portable schema.

Review every index definition separately after DDL conversion.

Bulk formats

Do not use LOAD XML.

Use the documented CSV or JSONL LOAD DATA syntax and test MatrixOne-specific options.

Generated IDs and result metadata

Do not assume MySQL auto-increment step or multi-row LAST_INSERT_ID() behavior.

Preserve source IDs explicitly when required and test generated-ID handling in the application.

Foreign-key actions

Do not use SET DEFAULT referential actions.

Keep dependent-row changes explicit and covered by migration tests.

Window frames

Do not rely on MySQL’s implicit frame for ordered LAST_VALUE or NTH_VALUE.

Specify the window frame explicitly when MySQL-compatible results are required.

Backup tools

Do not use xtrabackup or mariabackup for MatrixOne.

Use mo-dump for logical backup and the documented MatrixOne physical-backup tool for physical recovery.

System variables

Do not treat a successful SET as proof that a MySQL system variable has the same effect.

Verify the resulting behavior on MatrixOne.

Important: The rules above describe the migration contract. In the v4.2.6 probe, table locks, cascading foreign keys, both common upsert forms, Chinese identifiers, and a custom auto-increment start were accepted. They remain prohibited here because accepting syntax does not guarantee identical behavior across engines, drivers, versions, or backup/restore tools.

Additional semantic checks before cutover

The table above defines the portability rules. The following checks cover cases where a statement can execute successfully while the migrated result or runtime behavior is still different:

  • Identifier case: Whether database, table, and column names are case-sensitive depends on lower_case_table_names. Check the source setting and resolve objects that differ only by case before migration. Even when the target accepts Chinese identifiers, keep the portability contract’s ASCII naming rule to avoid inconsistencies in backup, monitoring, and cross-platform tooling.

  • Character sets and collations: Convert latin1, gbk, and other source encodings to utf8mb4 according to the actual source encoding; changing only the DDL CHARSET is unsafe. A collation with the same name does not prove identical case, accent, or trailing-space comparison semantics. Compare the behavior of key fields in joins, ordering, grouping, deduplication, and unique constraints.

  • Type and value mapping: Check driver and application serialization for DECIMAL, BIGINT UNSIGNED, UUID, and Boolean values separately. Do not assume that the UUID version, string format, or ORM mapping produced by MySQL UUID() is identical in MatrixOne. Use an integer for a multi-valued business state instead of encoding it as Boolean.

  • Date/time and sql_mode: Define how 0000-00-00, invalid dates, implicit conversions, fractional seconds, and connection time zones are handled. Verify the time-zone conversion semantics of TIMESTAMP separately from the wall-clock value semantics of DATETIME; do not silently replace historical zero dates without a business decision.

  • Generated IDs and result metadata: Even when accepted, REPLACE can delete and reinsert a row, changing omitted columns, generated IDs, relationships, and audit columns. After importing source IDs, verify generated IDs, lastrowid, generated-key metadata, and LAST_INSERT_ID() behavior. Never treat an auto-increment value as a contiguous sequence or commit order.

  • Procedures, functions, and triggers: Partial support for procedures or functions does not make MySQL definitions portable: verify syntax, parameters, control flow, return values, and lifecycle rules individually. Move trigger logic out of the database and cover every write path, including bulk imports, background jobs, and data repair tools.

  • Concurrency and retries: Do not carry MySQL table-lock or transaction- isolation assumptions into MatrixOne. Retry a complete transaction when a conflict is retryable. A commit timeout is not proof that the commit failed; use a business idempotency key to confirm the result and prevent duplicate writes, charges, or messages.

  • Savepoints and DDL: ORM nested transactions and batch jobs that depend on SAVEPOINT or ROLLBACK TO SAVEPOINT must be tested on the target version. Verify DDL implicit-commit and rollback behavior separately, and inspect the actual object and data state after a failed migration script.

  • Query semantics and pagination: Test production window functions, JSON functions, CTEs, NULL handling, return types, and boundary behavior. Do not rely on the result order without an explicit ORDER BY; when pagination order values are not unique, add a unique tie-breaker to prevent duplicates or omissions. Recheck the execution plan at the real data volume.

  • Drivers, ORMs, CDC, and backup tools: MySQL protocol connectivity does not prove compatibility of prepared statements, bulk writes, generated DDL, affected-row counts, error codes, system schemas, grants, binlog/GTID/replica behavior, or CDC tools. Check backup exit codes and perform an actual restore. After restore, compare schema, row counts, keys, relationships, business aggregates, and Chinese, binary, NULL, precision, and time values; validate large tables in primary-key ranges.

  • Cutover and rollback: Once MatrixOne accepts new writes, the old MySQL instance no longer contains the complete current state. Define a write-stop window, incremental catch-up, and rollback plan before cutover. A rollback must account for new or changed MatrixOne data to avoid loss, duplication, or divergent databases.

Step 1: Inventory the source schema

Before exporting data, scan DDL, migration files, stored routines, triggers, ORM models, and application SQL for the prohibited patterns:

CREATE PROCEDURE       CREATE FUNCTION       CREATE TRIGGER
LOCK TABLES             UNLOCK TABLES        ON DELETE CASCADE
ON UPDATE CASCADE       ON DUPLICATE KEY     REPLACE INTO
AUTO_INCREMENT =        mysqldump           non-utf8mb4 character sets
Chinese database/table identifiers

Treat this as a source-code scan as well as a database metadata scan. A dump can omit or transform objects, so checking only the exported SQL is not enough.

Step 2: Normalize the schema

Use an ASCII schema and make the character set explicit:

CREATE DATABASE app_data
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_bin;

CREATE TABLE app_data.customer (
    customer_id BIGINT PRIMARY KEY,
    display_name VARCHAR(200) NOT NULL,
    enabled BOOLEAN NOT NULL DEFAULT TRUE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

For migrated records, prefer an explicit stable ID when the source ID is part of a business relationship. For newly generated IDs, omit a custom starting value and let the target allocate IDs:

CREATE TABLE app_data.import_log (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    source_key VARCHAR(128) NOT NULL,
    UNIQUE KEY uk_import_log_source_key (source_key)
);

Do not use AUTO_INCREMENT=10000 (or another custom start) as a portability requirement. If a source sequence must be preserved, import the explicit IDs and validate collisions before enabling generated IDs.

Step 3: Replace non-portable behavior

Cascades and triggers

Instead of depending on a trigger or cascade, make the order explicit:

START TRANSACTION;
DELETE FROM app_data.order_item WHERE order_id = ?;
DELETE FROM app_data.orders WHERE order_id = ?;
COMMIT;

The application should check the affected-row counts and retry the transaction when the driver reports a transient conflict. This also makes the behavior visible in an offline migration script.

Upsert

Do not use either of these forms in portable migration code:

INSERT INTO app_data.customer ... ON DUPLICATE KEY UPDATE ...;
REPLACE INTO app_data.customer ...;

Use a transaction with an application-level decision instead:

1. Read by the unique business key.
2. If the row exists, UPDATE it; otherwise INSERT it.
3. Check the affected-row count and retry on a duplicate-key race.

REPLACE is especially risky because its delete-and-insert behavior can change foreign-key relationships, generated values, and audit columns.

Boolean values

Write values explicitly and normalize them at the application boundary:

INSERT INTO app_data.customer (customer_id, display_name, enabled)
VALUES (?, ?, 1);

SELECT customer_id,
       CASE WHEN enabled = 1 THEN TRUE ELSE FALSE END AS enabled
FROM app_data.customer;

Do not assume that a language driver binds a MySQL BOOLEAN column and a MatrixOne boolean column identically.

Do not use a Boolean column as a numeric aggregate without an explicit normalization step. In a MatrixOne probe, SUM(flag) on a BOOLEAN column returned invalid argument aggregate function sum, bad value [BOOL]; the portable form is an explicit predicate, for example SUM(CASE WHEN flag = 1 THEN 1 ELSE 0 END). Validate the equivalent expression with the target driver before moving an analytical query into production.

Step 4: Export and import data

There are two different tool cases:

  1. Source MySQL export: a source-side tool may be used to produce input data, but remove unsupported DDL and application behavior before importing.

  2. MatrixOne backup or MatrixOne-to-MatrixOne migration: use mo-dump for a logical SQL/CSV export. Do not run mysqldump against MatrixOne; it depends on MySQL system-table behavior that MatrixOne does not provide. For physical backup and restore, follow the mobackup documentation.

For a large CSV import, use LOAD DATA after creating and checking the target schema. For a SQL file, use SOURCE only after reviewing the file for the patterns in the inventory list.

Step 5: Validate the migrated result

Run checks at three levels:

-- Row counts for every migrated table.
SELECT COUNT(*) FROM app_data.customer;

-- Null, duplicate, and key checks for each business key.
SELECT customer_id, COUNT(*)
FROM app_data.customer
GROUP BY customer_id
HAVING COUNT(*) > 1;

-- A deterministic sample or checksum agreed with the source system.
SELECT customer_id, display_name, enabled
FROM app_data.customer
ORDER BY customer_id
LIMIT 100;

Then run representative application queries, including every window or JSON function used in production. Compare both values and error behavior; a query that parses successfully can still produce a different result.

Minimal MatrixOne compatibility probe

Run the following probes against the target version in a disposable database. The statements marked policy failure are expected to be rejected by the migration review even when the server accepts them.

SELECT VERSION();
SHOW VARIABLES LIKE 'character_set_server';
SHOW VARIABLES LIKE 'collation_server';

CREATE DATABASE migration_probe CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

-- Must remain a policy failure even if accepted by the target release.
CREATE TABLE migration_probe.parent_t (id INT PRIMARY KEY);
CREATE TABLE migration_probe.child_t (
    id INT PRIMARY KEY,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES migration_probe.parent_t(id)
        ON DELETE CASCADE
);

-- Test exact production window and JSON expressions on this target version.
SELECT ROW_NUMBER() OVER (ORDER BY 1) AS row_number_probe;
SELECT JSON_EXTRACT('{"a": 1}', '$.a') AS json_probe;

DROP DATABASE migration_probe;

v4.2.6 probe record

The following results were observed on MatrixOne 8.0.30-MatrixOne-v4.2.6:

Probe

Result

Interpretation

Server character set/collation

utf8mb4 / utf8mb4_bin

Matches the migration contract.

Stored procedure and trigger minimal probes

Rejected by the SQL parser

Keep both out of portable application behavior.

LOCK TABLES / UNLOCK TABLES

Accepted

Policy failure; do not use it in migration code.

ON DELETE CASCADE

Accepted and child row was deleted

Policy failure; perform dependent-row changes explicitly.

ON DUPLICATE KEY UPDATE / REPLACE

Accepted

Policy failure; use the explicit application workflow.

Chinese database/table identifiers

Accepted

Policy failure; use ASCII identifiers.

AUTO_INCREMENT=10000

Accepted and generated 10000

Policy failure; do not rely on a custom start.

Boolean read/write

Passed as 1 and 0

Bind and normalize explicitly.

ROW_NUMBER() and JSON_EXTRACT()

Minimal probes passed

Still test the exact production functions and result set.

This record is version-specific and should be updated when the compatibility baseline changes.