Mysql

Major means breakage possible, minor means safe. For twenty years a MySQL version number carried that promise, and you could plan by reading it. With the move to calendar-style releases the number tells you one thing: when it was built. The promise moved elsewhere, and you have to know where to look. The signals now are the channel and the dates. Is this an LTS or an innovation release. When does support end. What do the compatibility notes actually say, because “no major version” does not mean “no behavior change”. Optimizer changes ship in any release. Your query plans do not read version numbers. ...

An SQL mode set in 2019 that nobody remembers why. An authentication plugin the driver happened to support. A charset and collation nobody chose. That is what a MySQL upgrade is made of. MySQL 9.7 LTS is out, so I am making the list again. An LTS release of a database is a date more than a feature list. The date until which somebody else worries about patches, and the date after which the worry is yours. For a working PHP project the question is not “what is new”. It is “what did we assume”. ...

Half the industry is sitting on MySQL 5.7 right now, unsupported since last autumn, and calling it stability. MySQL 8.4 came out today, and the important word in the announcement is not a feature. It is LTS. First long-term release of the new model: innovation releases every quarter for the brave, an LTS every couple of years for production, bugfixes only after that. People underestimate how much pain the old model caused. 8.0 was a rolling target. A “minor” 8.0.x could change optimizer behavior or deprecate something you used. Staying current meant re-testing, staying behind meant missing security fixes. ...

The same table, the same rows, about 1.5 times the disk. That was the measurement after someone made UUIDv4 the primary key in InnoDB, and that is before counting secondary indexes. Every new API wants UUIDs. Distributed generation, no coordination, nothing leaks about row counts. All true. Then the database quietly starts paying. InnoDB clusters the table by primary key. Auto-increment inserts always land in the rightmost page, warm and predictable. Random UUIDs land anywhere in the tree: page splits everywhere, half-empty pages, and the set of hot pages becomes the whole index. Secondary indexes store the primary key in every entry, so each of them inherits the full 16 bytes too. In PostgreSQL the heap does not cluster, so the pain is smaller, but index bloat and lost locality are still there. ...

MySQL 8.1 came out this week, and its support window is measured in months. That is by design. Oracle changed the release model: two tracks now. Innovation releases every quarter or so, 8.1 is the first, short support, new features fast. And an LTS line with years of support, promised for next year. 8.0 stays on bugfixes until its end of life in 2026. For years the question was “when do we finally move to 8.0”. Now the question has a second dimension: which track are you even on. That is a product decision, and it is worth making explicitly instead of by inertia. ...

“MySQL server has gone away”, first thing in the morning, on a worker that had no traffic all night. That was week one after we moved a project from FPM to RoadRunner. For all these years FPM managed our database connections. Not with clever code. By dying. Process ends, connection closes, transaction rolls back, session variables reset. Nobody thought about connection lifecycle, because there was none. Move the same code to RoadRunner or Swoole workers and the topic is back. A connection lives for hours now. Two things go wrong, and both showed up within a week. ...

Two hundred thousand rows over the wire to compute forty numbers. That was our monthly report on MySQL 5.7: fetch all transactions for the period, loop in PHP, accumulate a running balance, compare each row with the previous one, rank customers by volume. Classic 5.7 shape, because the database could not say “previous row” or “rank within group”. We are finally moving that project to MySQL 8. Two years after GA, which by database standards is reckless haste. The first win had nothing to do with performance. We deleted PHP. ...

Error 1213 in the logs, and the chat lights up: the database is broken. It is not. Two transactions locked rows in opposite order, A waits for B, B waits for A, and InnoDB did the only sane thing: picked a victim and killed it. The cycle is gone. This is a feature. The classic shape is a money transfer. One request moves funds from account 1 to account 2, another from 2 to 1, both update the first account and then the second. Opposite order, instant cycle under load. Tests never show it, because tests do not run two of these in the same millisecond. ...

Statement three of a migration fails. Column name typo. On PostgreSQL the first two ALTERs roll back with it, the schema returns to the exact state before the migration, you fix the typo and run again. On MySQL the first two ALTERs are already permanent. Same up() method, same php artisan migrate, same green output. The migration tool gives one abstraction over two very different databases. The abstraction covers syntax. It does not cover what happens on failure. ...

Using index in the Extra column of EXPLAIN. That is the cheapest read MySQL can do, and you often get it almost for free. SELECT user_id, created_at FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 20; With an index on (user_id, created_at) everything the query needs is in the index leaves. The table is never touched. A composite index finds rows fast, a covering index answers the query on its own. Postgres calls this Index Only Scan, with one condition: the visibility map must be fresh, so a table that vacuum never visits quietly falls back to heap fetches. ...