Shared Heritage vs Independent Evolution: Why Syntax Compatibility Is Not Feature Parity

Learning outcomes

ServiceHub has a working MariaDB 12.3 lab from Chapter 01. A developer now proposes moving an older MySQL-backed service by changing only the connection hostname because “MariaDB speaks MySQL.” The application does connect, simple SELECT statements work, and the team concludes the migration is finished. That conclusion confuses one successful compatibility layer with the entire database contract.

MariaDB and MySQL share ancestry, a large SQL vocabulary, familiar client protocols, and many ecosystem conventions. They have also evolved independently for years. Compatibility therefore has multiple dimensions: network protocol, authentication, SQL grammar, SQL semantics, metadata, storage engines, configuration, operational utilities, binary logging, replication, optimizer behavior, and performance. This lesson replaces a yes/no compatibility label with an evidence matrix.

1. A compatibility claim must name the layer

At the protocol layer, MariaDB deliberately maintains a high degree of compatibility with MySQL client protocols and APIs. That is why many MySQL-capable connectors can establish a session with MariaDB. But the protocol only carries commands and results. It does not promise that the target server accepts every source statement, interprets every type identically, exposes the same system variables, chooses the same execution plan, or writes compatible replication events.

A useful mental model is a stack. The bottom of the stack is transport and authentication: can the client reach a server and authenticate? Above that is parser compatibility: does the SQL text parse? Above parsing is semantic compatibility: does it return or modify the same logical data? Above semantics are metadata and tooling assumptions: do drivers, migrations, ORMs, backup scripts, monitoring queries, and privilege-management scripts see the structures they expect? At the operational top are durability, replication, failover, backup, and performance behavior. A migration is only as safe as the highest layer you actually tested.

2. First prove which server answered you

A client binary name is weak evidence. A MySQL-branded client can connect to MariaDB, and a MariaDB client can often connect to MySQL. Always begin a compatibility test by asking the server to identify itself and by capturing session settings that can influence behavior.

SELECT VERSION() AS server_version,       @@version_comment AS version_comment,       @@hostname AS hostname,       @@port AS port,       @@sql_mode AS sql_mode,       @@character_set_server AS server_charset,       @@collation_server AS server_collation,       @@default_storage_engine AS default_engine;SELECT CONNECTION_ID() AS connection_id,       USER() AS login_identity,       CURRENT_USER() AS privilege_identity,       DATABASE() AS current_database;SHOW ENGINES;SHOW PLUGINS;

On MariaDB, VERSION() and the version comment should identify a MariaDB build. On MySQL Community Server, they should identify MySQL. Package vendors can add build text, so record the whole result rather than parsing only the leading number. The SQL mode, default character set/collation, and engine inventory matter because identical application SQL can diverge when these defaults differ.

3. Shared syntax is useful—but syntax is the shallowest SQL test

Both products understand core relational statements such as CREATE TABLE, SELECT, INSERT, joins, transactions, indexes, views, and many functions. That common surface is valuable. But a migration test must move from parser success to behavior assertions. For example, a table definition can parse on both products while its default collation, generated-column rules, JSON representation, optimizer choices, or accepted table options differ.

CREATE DATABASE IF NOT EXISTS servicehub_compat;USE servicehub_compat;DROP TABLE IF EXISTS tickets;CREATE TABLE tickets (  ticket_id BIGINT NOT NULL AUTO_INCREMENT,  customer_code VARCHAR(32) NOT NULL,  priority TINYINT NOT NULL DEFAULT 3,  payload JSON NULL,  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,  PRIMARY KEY (ticket_id),  UNIQUE KEY uq_ticket_customer (customer_code)) ENGINE=InnoDB;INSERT INTO tickets(customer_code,priority,payload)VALUES ('ACME-001',2,'{"source":"mobile","region":"west"}');SHOW CREATE TABLE tickets;SHOW FULL COLUMNS FROM tickets;SELECT ticket_id, customer_code, priority,       JSON_EXTRACT(payload,'$.source') AS sourceFROM tickets;

The important artifact is not merely “query succeeded.” Save SHOW CREATE TABLE, column metadata, returned values, warnings, and SQL mode. Later in this chapter you will run deliberately divergent probes. The compatibility notebook should therefore distinguish supported and equivalent, supported but different, unsupported, and not yet tested.

4. Metadata compatibility is where tooling often breaks

Application frameworks and operational tools frequently query metadata rather than business tables. An ORM can issue INFORMATION_SCHEMA queries to detect generated columns. A monitoring script can read mysql.user. A deployment tool can search for variables such as persisted configuration settings. These queries are part of your compatibility surface even when your application SQL is simple.

MariaDB, for example, stores modern global privilege records in mysql.global_priv, while mysql.user is retained as a compatibility view. A script that directly updates system tables is unsafe even if an old query still appears to work. The stable migration pattern is to use supported account-management statements such as CREATE USER, GRANT, and ALTER USER, then use metadata views for observation.

SELECT TABLE_NAME, ENGINE, TABLE_COLLATIONFROM information_schema.TABLESWHERE TABLE_SCHEMA='servicehub_compat'ORDER BY TABLE_NAME;SELECT COLUMN_NAME, DATA_TYPE, COLUMN_TYPE, IS_NULLABLE,       COLUMN_DEFAULT, EXTRA, COLLATION_NAMEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA='servicehub_compat'  AND TABLE_NAME='tickets'ORDER BY ORDINAL_POSITION;SHOW VARIABLES LIKE 'gtid%';SHOW VARIABLES LIKE 'wsrep%';SHOW VARIABLES LIKE 'persist%';

Do not turn an empty result into a universal statement about the product. It only shows what the exact target exposes under the chosen variable pattern and installed plugins. Record the server version beside every probe.

5. Optimizer and performance compatibility require measurements, not slogans

Two servers can return identical rows and still choose different access paths, join orders, temporary-table strategies, or cost estimates. That is not automatically a bug. Optimizers are implementation-specific and evolve independently. Therefore a compatibility review should compare the outcome first, then collect plans and measured latency under controlled data, cache, concurrency, and hardware conditions.

EXPLAINSELECT ticket_id, created_atFROM servicehub_compat.ticketsWHERE customer_code='ACME-001';ANALYZESELECT ticket_id, created_atFROM servicehub_compat.ticketsWHERE customer_code='ACME-001';

MariaDB has its own ANALYZE statement for execution statistics. MySQL exposes different plan and runtime-reporting syntax, including EXPLAIN ANALYZE in supported releases. Never copy one engine’s explain syntax or output parser into the other without a version check. For this tiny table, the plan is pedagogical only; do not infer production performance from a one-row dataset.

6. Deliberately wrong: “the connector connected, therefore we are compatible”

Suppose the ServiceHub application uses a MySQL-capable driver. You change the host from MySQL to MariaDB, the health check executes SELECT 1, and the deployment is marked green. Later a migration script runs SET PERSIST, a reporting query assumes MySQL JSON metadata, and a replication cutover expects MySQL GTID auto-positioning. The original smoke test was not false; it was simply testing only the protocol/session layer.

The repair is to convert every implicit dependency into an explicit test category. Keep the connection smoke test, but add schema DDL, generated columns, JSON, collations, representative reads/writes, stored programs, authentication, configuration, backup/restore, replication, and performance. An unsupported or different feature is not automatically a migration blocker if you can redesign around it—but it must not remain unknown.

7. Hands-on lab: build the first compatibility matrix

Run the following probes against MariaDB Community 12.3.2. If you have MySQL Community 8.4.11 available, run the same read-only probes there and store outputs in separate files. Both are free local products; a second engine is strongly recommended for this chapter because comparative behavior is the subject. Do not point the lab at production.

SELECT VERSION(), @@version_comment;SELECT @@sql_mode, @@character_set_server, @@collation_server;SHOW ENGINES;SHOW PLUGINS;SHOW VARIABLES LIKE 'gtid%';SHOW VARIABLES LIKE 'wsrep%';SHOW VARIABLES LIKE 'persist%';USE servicehub_compat;SHOW CREATE TABLE tickets;SHOW FULL COLUMNS FROM tickets;SELECT ticket_id, customer_code,       JSON_VALID(payload) AS valid_json,       JSON_TYPE(payload) AS json_type,       JSON_EXTRACT(payload,'$.region') AS regionFROM tickets;EXPLAIN SELECT * FROM tickets WHERE customer_code='ACME-001';

Cleanup is optional because the servicehub_compat database will be reused by later lessons. If you need a clean reset, confirm you are on a disposable instance and run DROP DATABASE servicehub_compat;.

8. Summary and bridge

MariaDB and MySQL have strong shared heritage, but compatibility is layered and directional. Treat connection success as the beginning of evidence collection, not the end. Identity, SQL semantics, metadata, tools, configuration, replication, and performance each need their own tests. The next lesson makes this more concrete by comparing release numbering, renamed utilities, configuration surfaces, defaults, MariaDB-only capabilities, and MySQL-only administrative mechanisms.