WIUNIX ACADEMY
MySQL online DDL: reproduce and diagnose metadata-lock waits
Trace a read transaction blocking an online index build on a fresh fixture, distinguish metadata timeout and plan a bounded real migration.
Make this guide yoursYOUR SYSTEM. YOUR STEPS.
Where are you working?
Choose your OS and version. The prerequisites, commands and troubleshooting follow your environment.
USE YOUR OWN VALUES
Configure this tutorial
Set the example values for your environment; related explanations and copy panels update together. Never enter passwords, private keys or tokens. Values stay in this tab and are not sent to the server or share links. Check actual DNS, certificates and network policy before running commands.
Different IP addresses, domains or ports? Open the settings below, change the values and select “Apply to tutorial”, then copy the updated commands.
Set values for my environment3 editable values
THE OUTCOME
What will you have at the end?
Trace a read transaction blocking an online index build on a fresh fixture, distinguish metadata timeout and plan a bounded real migration.
Supported operating systems and versions
Documented paths for Ubuntu, Debian and Red Hat families with preinstalled compatible products/clients. The first step states each branch’s limits. Service commands were not executed during authoring.
Before you begin
- Existing authorized service and protected client authentication; use the isolated fixture scope specified below.
LOCK=NONE does not mean no metadata locks
An online InnoDB operation can allow concurrent DML yet still require exclusive metadata locks during parts of its lifecycle. A transaction that merely read a table can retain a metadata lock until it ends. A queued ALTER can then make later readers wait behind it, turning one forgotten transaction into an application incident. This guide reproduces the blocker/waiter portion on a fresh three-row table, then distinguishes metadata timeout from row-lock timeout.
Use MySQL8.4, not assumed MariaDB compatibility
Use an existing isolated MySQL8.4 InnoDB staging server with performance_schema and the sys schema available. Three client sessions must share the same server/account scope. This is not a production migration recipe, and MariaDB’s DDL algorithms/diagnostic tables are not interchangeable here. Keep existing TLS verification and protected option-file authentication. No real application table or replication topology is changed.
Choose a brand-new lab schema and a bounded wait
The browser form contains only a protected config path, fresh schema name and metadata wait seconds. Values stay in this tab; Apply keeps text and copy panels synchronized. The option file already has approved host/user/TLS settings and must be readable only by its authorized owner. Do not paste its password into the page. Prepare all three terminals before starting the blocking step so the observation fits inside the bounded wait.
LET’S BUILD
Verify the installed tools and the host on which commands run
Use an existing isolated MySQL 8.4 InnoDB server and the MySQL client. Oracle lists MySQL 8.4 on Debian 12/13, Ubuntu 26.04 and RHEL/Rocky 9/10. Ubuntu 26.10 is not listed: its selection covers a separately provisioned compatible administration client connecting to the approved server, not a supported local server installation. AlmaLinux is a conditional EL-compatible client path here, not an Oracle platform certification claim. APT installations may use mysql-community-client and mysql.service; Yum installations use mysql-community-client and mysqld.service. Do not assume the OS-provided mysql command or default database module is MySQL 8.4: inspect client and server versions and stop on a different product. The per-user protected defaults file keeps its explicit path on every distribution.
These are documented command paths, not executed compatibility tests. Read /etc/os-release and verify the exact product/version before continuing. Existing accounts, approved network access and protected credentials are prerequisites. Do not restart existing services to provision tools, initialize a database, open a firewall port or alter a system Python installation to make this exercise run.
Approved administration client; node-specific checks only on the existing node
cat /etc/os-release
id
command -v mysql
mysql --version
test -r "/home/academy/.mysql-lab.cnf"The OS, tool path and version match the selected procedure; stop before data changes if they do not.
LET’S BUILD
Verify server identity and instrumentation
Open the first administration connection without selecting a database. Verify VERSION and hostname; check that the metadata-lock instrument is enabled. If sys/performance_schema visibility is denied, use an approved observer role rather than granting broad privileges in the article. Do not enable server instrumentation globally without its owning change review.
Authorized MySQL8.4 staging administrator
mysql --defaults-extra-file='/home/academy/.mysql-lab.cnf' -e "SELECT VERSION(),@@hostname,@@performance_schema; SELECT NAME,ENABLED,TIMED FROM performance_schema.setup_instruments WHERE NAME='wait/lock/metadata/sql/mdl';"The server is MySQL8.4 and metadata-lock observation is available.
LET’S BUILD
Create a fresh synthetic schema without overwrite
The schema must be absent: CREATE DATABASE without IF NOT EXISTS deliberately fails on a collision. If that happens, stop; do not drop or reuse the existing schema. This command needs the staging owner’s explicit create permission. The fixture has no customer data and three rows only. Keep the schema name in the record for later precise cleanup.
Authorized MySQL8.4 staging administrator
mysql --defaults-extra-file='/home/academy/.mysql-lab.cnf' <<'SQL'
CREATE DATABASE `academy_mdl_20261028`;
CREATE TABLE `academy_mdl_20261028`.mdl_probe(id INTEGER PRIMARY KEY,category VARCHAR(32) NOT NULL) ENGINE=InnoDB;
INSERT INTO `academy_mdl_20261028`.mdl_probe VALUES(1,'alpha'),(2,'beta'),(3,'alpha');
SELECT * FROM `academy_mdl_20261028`.mdl_probe;
SQLOne new schema and three-row InnoDB table exist, with no secondary index.