My ugliest on-call shift of the 5.7 years started with a power event during an online ALTER on a 300GB table. When the box came back, the table answered queries, but the dictionary said the table did not exist — or the .frm said it existed and InnoDB disagreed, depending on which catalog you believed. There was an orphaned #sql-ib-48291.ibd file eating 300GB of disk, a temp table visible in SHOW ENGINE INNODB STATUS but not in INFORMATION_SCHEMA, and the documented fix involved copying .frm files around and praying. I lost a weekend to that incident, and I still remember it every time someone calls DDL "just metadata".
MySQL 8.0 replaced that whole arrangement with a transactional data dictionary stored in InnoDB, and DDL became atomic: it commits in full or rolls back in full, even across a crash. That change rewired more than crash behavior — how INFORMATION_SCHEMA answers, where table definitions live on disk, what an upgrade does, and which old file-level tricks are gone for good.
What did a crash mid-ALTER leave behind before 8.0?
A split-brain dictionary. Before 8.0, table metadata lived in two places at once: the server kept .frm files in the database directory, and InnoDB kept its own dictionary inside the system tablespace — the SYS_TABLES family nobody was supposed to touch. DDL updated them in separate, non-transactional steps, so a crash in the middle could leave the two disagreeing: a .frm with no InnoDB table, an InnoDB table with no .frm, or the classic orphaned #sql temporary table from a failed ALTER that you could neither query nor drop. Every senior MySQL DBA from that era has a personal recipe for the surgery: fake .frm files, DROP TABLE with carefully ordered restarts, sometimes editing ibdata1 with tools you do not admit to using. The recipes worked often enough to be dangerous, and they are all obsolete now, which is the best thing about 8.0.
What does atomic DDL actually guarantee in 8.0?
That a DDL statement on InnoDB tables is all-or-nothing, including across a server crash. In 8.0 the dictionary itself is a set of InnoDB tables inside the mysql.ibd tablespace in the datadir, so metadata changes participate in the same transaction machinery as row changes, with undo, redo, and crash recovery. During DDL the server writes records to an internal DDL log, the mysql.innodb_ddl_log table, and recovery replays it: a statement that committed gets finalized — temporary files removed, renames completed — and one that did not gets rolled back and cleaned up. The practical outcome is that the crash-during-ALTER scenario ends with the table either entirely old or entirely new, and there is never an orphaned #sql table squatting on your disk. TRUNCATE benefits too: it is implemented as drop-and-recreate of the tablespace, and in 8.0 that sequence is crash-safe, where 5.7 could strand the .ibd of a truncated table behind a dictionary that no longer admitted owning it.
What is still not atomic in 8.0?
Anything involving storage engines that do not participate, and any attempt to treat DDL as part of your SQL transaction. MyISAM and friends have no transactional dictionary to commit into, so DDL touching them commits per engine; a statement mixing InnoDB and MyISAM tables is only as atomic as its weakest table. Multi-table DROP TABLE on pure InnoDB is atomic — the whole list drops or none of it does — but add one MyISAM table and the guarantee evaporates mid-list. CREATE TABLE ... SELECT was another late arrival: it only became atomic for InnoDB in 8.0.21, so war stories from early 8.0 about half-created tables after a crash are real history, not confusion. And two category errors to avoid: atomic does not mean non-blocking — the statement still needs its metadata locks, and the queue mechanics in metadata lock diagnosis are unchanged — and atomic does not mean transactional in the application sense, because DDL still implies a commit and can never roll back with your surrounding transaction.
Why does INFORMATION_SCHEMA suddenly show stale row counts?
Because the views now read from the data dictionary, and dictionary statistics are cached for up to information_schema_stats_expiry seconds — 86400 by default. On 5.7, asking INFORMATION_SCHEMA.TABLES for TABLE_ROWS poked the storage engine every time; on 8.0 you get the cached value until it expires or ANALYZE TABLE refreshes it. The first place this bites is usually a monitoring script: a table-size report that used to be current now drifts a day behind, and the confusion compounds because only the statistics columns are stale — a newly created table itself shows up the moment its DDL commits. The knob is session-aware, so you can have both worlds — cheap cached reads for dashboards, exact numbers where you are willing to pay for them:
SET SESSION information_schema_stats_expiry = 0;
SELECT table_name, table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = 'billing'
ORDER BY data_length DESC
LIMIT 10;
-- and the modern home of persistent optimizer statistics:
SELECT table_name, n_rows, clustered_index_size, sum_of_other_index_sizes
FROM mysql.innodb_table_stats
WHERE database_name = 'billing';Setting the expiry to 0 makes each read fetch from the engine, which is what SHOW TABLE STATUS effectively did in the old days and costs the same I/O. I keep the global default and set 0 only in the sessions that need freshness — a compromise that keeps both the dashboards cheap and the capacity reports honest.
What did you lose at the file level?
Direct access, deliberately. The .frm files are gone, the dictionary lives in mysql.ibd which the server owns absolutely, and the 5.7 trick of copying table definitions around as files died with them. In their place, InnoDB tablespaces — all but the undo and global temporary ones — now carry serialized dictionary information: SDI, a JSON copy of the table's metadata kept as redundancy against dictionary loss, and the ibd2sdi utility can dump it from an .ibd offline, the sanctioned replacement for reading .frm files with strings. What SDI did not change is the transportable-tablespace ritual: moving an .ibd between instances still runs FLUSH TABLES ... FOR EXPORT and still produces the .cfg file, exactly as in the old days. The upgrade path reflects the same one-way design: moving from 5.7 to 8.0 merges .frm files and the InnoDB dictionary into the new dictionary in a single conversion pass, there is no downgrade, and the upgrade complains loudly about orphans left by past crashes — one more reason the field notes in the 5.7 to 8.0 upgrade start with cleaning house. Instant DDL in 8.0 also sits on top of the dictionary, and which alters actually qualify is its own rabbit hole, covered in instant DDL: what actually qualifies.
Where MonPG stands on MySQL
I build MonPG, so plainly: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. DDL is exactly the kind of event that should be visible in monitoring rather than discovered in a postmortem — long-running alters, metadata lock queues building behind them, table-size step changes after a rebuild — and surfacing those from performance_schema is core to the MySQL work. The MySQL monitoring (coming soon) page is where that lands as it ships. In the meantime the same approach runs on the PostgreSQL side today, and the rest of these MySQL notes live on the blog.