mariadb-upgrade, System Tables, Configuration Drift, and Post-Upgrade Validation

Learning outcomes

A MariaDB process can start after a binary upgrade and still be operationally incomplete. The mysql system schema can require adjustment, an option may be ignored or rejected, a DEFINER account may be missing, and application plans can regress. Startup is evidence of process viability—not proof of upgrade success.

1. What mariadb-upgrade is for

Current MariaDB documentation describes mariadb-upgrade as the tool that updates the mysql system tables and checks tables for upgrade compatibility. It is normally run after starting the new server. Some package workflows may invoke it automatically, so the runbook should verify whether it ran successfully rather than assume either manual or automatic behavior.

# 1. Stop old service and install the tested target packages.# 2. Start the NEW mariadbd using reviewed option files.# 3. Inspect startup/error logs.# 4. Run the target mariadb-upgrade if the version-specific guide requires it.sudo mariadb-upgrade# 5. Inspect its exit status/output, then validate the server again.

2. Capture config before and after

SHOW VARIABLES WHERE Variable_name IN('sql_mode','innodb_buffer_pool_size','innodb_log_file_size','innodb_flush_method', 'binlog_format','log_bin','server_id','gtid_strict_mode','event_scheduler');SHOW STATUS LIKE 'Uptime';SELECT VERSION(), @@version_comment;

An option file may be ignored because of group name, package include order, spelling, changed defaults or removal. Store the effective-value snapshot alongside the source option files and diff both.

3. Deliberately incomplete approach and symptoms

Suppose the operator installs the new binary, sees port 3306 open, and immediately returns traffic. Errors later appear for event scheduling, privilege metadata, or system views. The root cause is not “MariaDB is corrupt”; it may simply be that post-upgrade system-table work and validation were skipped.

SELECT TABLE_SCHEMA, TABLE_NAME, ENGINEFROM information_schema.TABLESWHERE TABLE_SCHEMA IN ('mysql','sys')ORDER BY TABLE_SCHEMA, TABLE_NAME;SELECT EVENT_SCHEMA, EVENT_NAME, STATUS, DEFINERFROM information_schema.EVENTS;SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, DEFINERFROM information_schema.ROUTINES;SELECT TABLE_SCHEMA, TABLE_NAME, DEFINER, SECURITY_TYPEFROM information_schema.VIEWSWHERE TABLE_SCHEMA NOT IN ('information_schema','performance_schema','sys');

4. Validate accounts and privileges without dumping secrets

SELECT User, Host, plugin, account_locked, password_expiredFROM mysql.userORDER BY User, Host;-- For selected application accounts:SHOW GRANTS FOR 'svc_app'@'10.%';SHOW PRIVILEGES;

System-table internals evolve. Prefer supported account-management statements and metadata interfaces over copying raw mysql.* table rows between products or versions. Verify definers explicitly for restored views/routines/events.

5. Schema and application acceptance checks

CHECK TABLE servicehub.orders FOR UPGRADE;SHOW CREATE TABLE servicehub.orders;EXPLAIN SELECT order_id, customer_id, total_amountFROM servicehub.ordersWHERE customer_id = 42ORDER BY created_at DESC LIMIT 20;SELECT COUNT(*) AS orders,       SUM(total_amount) AS revenueFROM servicehub.orders;

Choose queries from real workload digests, not only trivial probes. Compare row counts/business invariants, plans, latency percentiles, error rate, temp-table behavior and key InnoDB/replication/Galera metrics against the pre-upgrade baseline.

6. Disposable local validation lab

CREATE DATABASE IF NOT EXISTS upgrade20_l2;CREATE TABLE upgrade20_l2.accounts(id INT PRIMARY KEY, balance DECIMAL(12,2) NOT NULL) ENGINE=InnoDB;INSERT INTO upgrade20_l2.accounts VALUES (1,100.00),(2,200.00);CREATE OR REPLACE VIEW upgrade20_l2.v_total SQL SECURITY INVOKER ASSELECT SUM(balance) AS total_balance FROM upgrade20_l2.accounts;SELECT * FROM upgrade20_l2.v_total;

Run this on a disposable source container/version, take a logical backup, move to a target container/version following the official guide, run the required upgrade process, then repeat SHOW CREATE, counts, view query, log inspection and version capture. The exact source image should be one you actually need to test; do not invent compatibility from a classroom path.

Production judgment and bridge to Lesson 3

Keep post-upgrade traffic constrained until logs, system metadata, accounts, object definers, representative SQL and performance signals pass explicit gates. Lesson 3 widens the problem from MariaDB→MariaDB upgrades to MySQL↔MariaDB migrations, where protocol familiarity hides deeper directional differences.

Post-upgrade validation is layered: process, metadata, data, application, then performance

A server process that starts is only the first gate. Validate the error log from the new startup, the reported server version and package, effective configuration, loaded plugins/engines, and whether any removed/renamed option was ignored or replaced. Configuration drift is especially dangerous when a package upgrade changes include paths or ships a new default file: the old setting may still exist on disk while the running server no longer reads it.

Next validate server metadata. mariadb-upgrade and its target-version requirements are about making system tables and table metadata compatible with the new server; they do not validate the application's business semantics. Check the tool's exit state/output, inspect the error log, verify account/role/grant behavior, and run targeted table checks where the upgrade guide requires them. Capture the pre/post definitions of critical server variables rather than assuming defaults stayed constant.

Data validation should combine structural and business evidence. Confirm expected schemas/tables/indexes, row counts for high-value entities, invariants such as “every paid order has exactly one ledger record,” and representative checksums or aggregates where appropriate. A whole-table byte checksum is not always practical or stable across logical migrations, so select checks that prove the properties the application depends on.

Application validation belongs before broad traffic. Execute the same critical read/write transactions, authentication flows, stored routines/events, migration tool commands, and connector pool behavior that the production application uses. Capture generated SQL and query plans for known sensitive statements. If the optimizer chooses a different plan, determine whether latency remains inside the service objective rather than declaring every plan change a regression.

Performance comparison must use the same workload definition and observation window. Record warmup policy, concurrency, data volume, cache state, percentile latency, throughput, CPU, storage latency, buffer/redo indicators, connection/thread pressure, replica or wsrep signals, and error rate. Compare distributions—not one lucky timing. If the target fails a predeclared gate, stop rollout and investigate while the canary or rollback option is still available.

Configuration-drift worksheet: compare intended, persisted, and effective state

Configuration has at least three representations: what your configuration repository says should be set, what option files or service definitions persist on the host, and what the running MariaDB server actually reports. Upgrade validation should compare all three. A value can disappear because an option was renamed, an include path changed, a package supplied a new default, or the server rejected an obsolete setting. Conversely, a stale option file can remain on disk and mislead a future operator even when the running server ignores it.

Capture a focused pre/post inventory of correctness- and performance-sensitive variables, not an unreviewed dump of thousands of values. Include SQL mode, character set/collation, durability/binlog settings, InnoDB sizing, connection/threading, time zone, authentication/TLS controls, replication/Galera variables where applicable, and any nondefault optimizer switches. Mark each difference as expected or unexpected and link it to the release note or configuration change that explains it.

System-schema validation should also include authorization semantics. Reconnect using representative application, migration, backup, and monitoring accounts; do not verify grants only while logged in as an administrator. Run the least-privilege operations those identities need and confirm failed operations still fail. This catches both missing privileges and accidental privilege expansion introduced by a migration or account-management change.

Finally, retain the pre/post inventory with the upgrade artifact. When a performance regression appears days later, the ability to answer “what effective state changed?” is often more valuable than the package install transcript.

Post-upgrade evidence packet

Keep one concise packet with the new server/version output, successful upgrade-tool result, startup/error-log review, configuration diff, account/grant probes, critical table/invariant checks, representative application transaction results, and before/after performance summary. This makes the change auditable and gives incident responders a baseline if a defect appears after the maintenance window.

Authoritative references

• MariaDB Documentation — mariadb-upgrade.
• MariaDB Documentation — version-specific upgrade guides.
• MariaDB Documentation — system tables, account management, CHECK TABLE and logging.