Do not uselessly grant CREATE and ALTER TABLE

This lesson should have been learned with the CREATE TABLE of death, but it is worth a refresh.

Do not uselessly grant CREATE and ALTER TABLE

The reason I am posting this reminder is that another crashing bug related to DDL came to my attention.  This bug is only fixed in a recent version of MySQL (probably not affecting 5.6 and 5.7), so if you are running the latest 8.0 or 8.4, you should be fine.  I am not sharing the detail, I might in the future (like I did for the CREATE TABLE of death).  For triggering it, one need CREATE and ALTER TABLE.

The even more scary thing about this bug / crash is that it is also triggered by a rollback.  So after the crash, when MySQL attempts crash recovery, it crashes again when rolling-back uncommitted transaction.  There might still be a way to recover data with innodb_force_recovery, but it would compromise atomicity and is probably a big hurdle.  In this respect, in addition to being a crashing bug, it can be considered a data corruption bug.

A crash in rollback is non-trivial to recover

I gave a talk about a similar situation at SRECon: Autopsy of a Cascading Outage from a MySQL Crashing Bug.  The slides are self-contained and were appreciated.  If you face this or a similar situation, I hope you have a good backup and can replay binlogs (maybe it is also good time to test backups and binlog replay).

Inserting in Two Tables in a Single Round-Trip with JSON Duality Views in MySQL 9.7

A few months ago, I was asking myself how to insert in two tables in a single round-trip to the database.  I wanted to do that to optimize a process.  My optimization involved splitting a table in two, which would need inserting in two tables atomically.  The downside was changing an auto-commit INSERT to a transaction with two inserts, which was changing the shape of the workload from a single round-trip to the database to four: BEGIN, INSERT1, INSERT2, and COMMIT.  I had no satisfying solution to that, I now have one: JSON Duality Views in MySQL 9.7.