Data Types, SQL Semantics, JSON Differences, Generated Columns, and Compatibility Traps

Learning outcomes

ServiceHub’s schema passes a basic DDL import into MariaDB, so the migration team assumes data semantics are preserved. A JSON column still says JSON, generated columns still calculate, and strings still sort. But the same type name can hide a different physical representation, collation can reorder values, SQL mode can change conversion rules, and a feature such as a sequence or INSERT ... RETURNING can exist on one server without being portable to the other.

This lesson turns those differences into runnable assertions. The goal is not to memorize a giant MariaDB-versus-MySQL table. Instead, you will build small probes whose outputs reveal representation, metadata, and semantics on the exact versions under test.

1. The same type name can hide a different storage contract

MariaDB accepts JSON as a data type name, but current MariaDB documentation defines it as an alias for LONGTEXT COLLATE utf8mb4_bin with JSON validation behavior. MariaDB added the alias partly for compatibility with MySQL dumps and statement-based replication. MySQL, in contrast, stores the JSON data type in its own binary JSON representation. A connector that only inspects the DDL text may therefore miss a meaningful difference in metadata and physical representation.

USE servicehub_compat;DROP TABLE IF EXISTS json_probe;CREATE TABLE json_probe (  id INT PRIMARY KEY,  doc JSON NOT NULL) ENGINE=InnoDB;INSERT INTO json_probe VALUES  (1,'{"status":"open","priority":2}'),  (2,'{"status":"closed","priority":4}');SHOW CREATE TABLE json_probe;SHOW FULL COLUMNS FROM json_probe;SELECT id,       JSON_VALID(doc) AS is_valid,       JSON_TYPE(doc) AS json_type,       JSON_EXTRACT(doc,'$.status') AS statusFROM json_probeORDER BY id;

On MariaDB, SHOW CREATE TABLE exposes the underlying long-text representation and validation constraint semantics. That does not make MariaDB JSON “fake”; it means the implementation contract differs. Index design, comparison behavior, row-based replication, connector metadata, and migration tools must account for the exact representation rather than the friendly type alias.

2. JSON value tests must check result, type, and metadata

A migration test should assert more than “JSON_EXTRACT returned something.” Capture whether the returned value is quoted, how NULL/missing paths are represented, whether duplicate object keys are preserved/accessible, which collations apply to text comparisons, and what metadata the driver reports. These details matter to ORMs and application serializers.

SELECT JSON_VALID('{"a":1}') AS valid_doc,       JSON_VALID('{broken}') AS invalid_doc;SELECT JSON_EXTRACT('{"a":1,"b":null}', '$.a') AS a_value,       JSON_EXTRACT('{"a":1,"b":null}', '$.b') AS b_json_null,       JSON_EXTRACT('{"a":1,"b":null}', '$.missing') AS missing_path;SELECT JSON_UNQUOTE(JSON_EXTRACT('{"name":"ServiceHub"}', '$.name')) AS name_text;SELECT DATA_TYPE, COLUMN_TYPE, COLLATION_NAMEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_compat'  AND TABLE_NAME='json_probe'  AND COLUMN_NAME='doc';

Run the same logical corpus on the source and target, but do not assume identical metadata means identical storage. For application compatibility, record the value observed by the connector as well as the SQL client. A JDBC, Node.js, Python, or ORM layer can map vendor metadata to different host-language types.

3. Generated columns: similar grammar, version-sensitive optimizer behavior

Both products support generated columns, but the vocabulary and optimizer behavior are not a stable cross-vendor contract. MariaDB uses VIRTUAL and PERSISTENT, with STORED accepted as an alias for persistent storage. Current MariaDB documentation also identifies version-sensitive improvements in recognizing indexed virtual-column expressions. A migration test should therefore separate “DDL accepted” from “query can exploit the index.”

DROP TABLE IF EXISTS generated_probe;CREATE TABLE generated_probe (  ticket_id BIGINT NOT NULL AUTO_INCREMENT,  customer_code VARCHAR(32) NOT NULL,  normalized_code VARCHAR(32)    GENERATED ALWAYS AS (UPPER(customer_code)) VIRTUAL,  code_len INT    GENERATED ALWAYS AS (CHAR_LENGTH(customer_code)) PERSISTENT,  PRIMARY KEY (ticket_id),  KEY ix_generated_normalized (normalized_code)) ENGINE=InnoDB;INSERT INTO generated_probe(customer_code)VALUES ('acme-001'),('globex-002'),('initech-003');SHOW CREATE TABLE generated_probe;SELECT * FROM generated_probe ORDER BY ticket_id;EXPLAIN SELECT ticket_id FROM generated_probeWHERE normalized_code='ACME-001';

The result should show generated values without application-side writes. The plan on this tiny dataset may still choose a table scan; do not call the index “unused” based on three rows. Scale data only when you are testing optimizer behavior, and compare actual query outcomes before comparing plan shape.

4. SQL modes convert “accepted input” into a moving contract

SQL modes control strictness and several syntax/semantic behaviors. If a source session silently truncates or converts questionable data while the target rejects it—or vice versa—the migration can fail even though DDL is identical. The correct test is to record source and target SQL modes, then run a corpus of boundary values in a transaction or disposable table and assert warnings/errors.

DROP TABLE IF EXISTS conversion_probe;CREATE TABLE conversion_probe (  id INT PRIMARY KEY,  small_code VARCHAR(3) NOT NULL,  qty TINYINT NOT NULL) ENGINE=InnoDB;SELECT @@session.sql_mode AS active_sql_mode;-- Run these one at a time and record success/error/warnings.INSERT INTO conversion_probe VALUES (1,'TOOLONG',10);SHOW WARNINGS;INSERT INTO conversion_probe VALUES (2,'OK',999);SHOW WARNINGS;ROLLBACK;

Do not turn this into a recommendation to weaken strictness. The lesson is that application behavior depends on the mode under which statements execute. During migration, prefer explicit validation and clean data over reproducing permissive behavior that allowed corruption-like states.

5. Collations are semantic dependencies, not decorative labels

A collation controls comparison and ordering rules for character data. MariaDB and MySQL support overlapping but nonidentical collation sets, and modern defaults differ. This can change unique-key conflicts, equality comparisons, ORDER BY results, case/accent behavior, and cross-vendor replication of newly created tables. Never solve a collation mismatch by blindly converting everything; inventory columns and define required linguistic/binary semantics first.

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAMEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_compat'  AND COLLATION_NAME IS NOT NULLORDER BY TABLE_NAME, ORDINAL_POSITION;SELECT 'a' = 'A' COLLATE utf8mb4_general_ci AS general_ci_equal;SHOW COLLATION WHERE Charset='utf8mb4';

The exact available collation names depend on the target version. If you need a specific source collation, check that it exists on the target and test representative strings—including non-ASCII business data—before converting production tables.

6. MariaDB sequences are useful—and a portability decision

MariaDB supports standalone sequence objects with CREATE SEQUENCE and NEXT VALUE FOR. A sequence can be useful when you need identifiers independent of one table, configurable caching, or Oracle-style migration patterns. But introducing a sequence into code that must also run on MySQL changes the portability contract.

DROP SEQUENCE IF EXISTS ticket_public_no;CREATE SEQUENCE ticket_public_no  START WITH 500000  INCREMENT BY 1  CACHE 100;SELECT NEXT VALUE FOR ticket_public_no AS public_no_1;SELECT NEXT VALUE FOR ticket_public_no AS public_no_2;SHOW CREATE SEQUENCE ticket_public_no;DROP SEQUENCE ticket_public_no;

Sequence values are generated state, not gap-free accounting numbers. Caching, crashes, rollback, and concurrent allocation can create gaps. The compatibility question is whether the application contract requires a portable auto-generated key or can deliberately adopt a MariaDB-specific sequence abstraction.

7. RETURNING can reduce round trips but must be feature-tested

MariaDB supports INSERT ... RETURNING, REPLACE ... RETURNING, and single-table DELETE ... RETURNING on supported releases. This can return generated/default values to the client in the same statement. That is attractive for application code, but it is also a dialect dependency. Do not infer cross-vendor support from the fact that RETURNING appears as a keyword in another server’s parser.

DROP TABLE IF EXISTS returning_probe;CREATE TABLE returning_probe (  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,  label VARCHAR(80) NOT NULL,  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP) ENGINE=InnoDB;INSERT INTO returning_probe(label)VALUES ('compatibility-test')RETURNING id, label, created_at;DELETE FROM returning_probeWHERE label='compatibility-test'RETURNING id, label, created_at;

Application integration must also verify the connector can consume the returned result set correctly. A statement supported by the server can still expose ORM or driver assumptions about affected-row counts and generated-key APIs.

8. Deliberately wrong: declare a schema portable because CREATE TABLE succeeded

A schema import can succeed while changing the underlying JSON representation, applying a different default collation, accepting a generated-column expression with different optimizer behavior, or retaining source-era defaults that your target would not otherwise choose. “DDL imported” is therefore a milestone, not an acceptance criterion.

Repair the process by comparing SHOW CREATE TABLE, INFORMATION_SCHEMA.COLUMNS, indexes/constraints, representative values, warnings, and application metadata. Then run semantic assertions for every type or function the application depends on. If a feature is intentionally MariaDB-specific, document that decision so future maintainers do not assume MySQL portability.

9. Hands-on lab: semantic compatibility test pack

Create a script containing the JSON, generated-column, conversion, collation, sequence, and RETURNING probes above. Mark MariaDB-only statements explicitly. On a MySQL comparison server, run only statements valid for that product and replace MariaDB-specific probes with equivalent capability checks rather than forcing syntax.

10. Summary and bridge

Data compatibility is about semantics and metadata, not familiar type names. JSON, generated columns, SQL modes, collations, sequences, and RETURNING all show why version-specific runnable tests beat broad compatibility claims. The next lesson raises the stakes: binary logs and replication introduce event formats, GTID models, DDL, authentication, and topology directionality that can turn a seemingly compatible pair into an unsafe HA design.