Recently there have been several issues reported to me about DDL activity causing MySQL crash scenarios. In one case it stemmed from dropping multiple databases, one after the other in rapid succession. But in the case that I was recently dealing with directly, where we were upgrading to MySQL 5.7, it was the result of mysql_upgrade running an ALTER TABLE FORCE on a 2.2Tb table in order to convert it to the new microsecond precision supporting data format.

The issue occurred after the intermediate table had been completely filled with all the necessary data and right when MySQL would swap out the existing table for the intermediate. After a period of time MySQL crashed and the following InnoDB monitor output was found in the error log.

2017-11-19T00:22:44.070363Z 7 [ERROR] InnoDB: The age of the last checkpoint is 379157140, which exceeds the log group capacity 377483674.
InnoDB: …

I’m frequently tracking semaphores waits, and if you’ve examined them before, it can be a little matching up the threads listed in the SEMAPHORES section with the transactions in the TRANSACTIONS section.

Semaphore waits are related to internal synchronization between threads in mysqld, and not directly to row locks or other items associated with user queries, so that’s why the SEMAPHORES section only reports the OS thread id.

Fortunately, the TRANSACTIONS sections also reports the OS thread handle, but in hex format.

Here is an example semaphore wait:

--Thread 1079654736 has waited at ibuf0ibuf.c line 3549
for 943.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7f2a48830bf8 '&block->lock'
a writer (thread id 1079654736) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: ffffffffffffffff
Last time read locked in file buf0flu.c line 1318

So how do …