Instance Class Change or Minor Version Upgrade Failure Caused by Long Transactions

Scenario

An instance class change or a minor version upgrade fails due to long transactions.

Possible Causes

• To minimize the impact on workloads, an instance class change or a minor version upgrade is performed in rolling mode, so a primary/standby switchover is required.
• To ensure data consistency, the primary instance needs to be set to read-only before the switchover. This is a requirement for a successful class change or version upgrade.
• If there are long transactions executing on the primary instance, the primary instance may not be able to be set to read only. This operation may time out or fail. As a result, the instance class change or minor version upgrade fails.

Solution

1. Run the show processlist command to view the transactions that are being executed. Then run the following command to identify which one is taking too long:

   select t.*,to_seconds(now())-to_seconds(t.trx_started) idle_time from INFORMATION_SCHEMA.INNODB_TRX t \G;

   Example output:

   

   In the preceding command output, idle_time is the calculated time the transaction stays idle. trx_mysql_thread_id indicates the thread ID of the transaction, which is the thread ID queried by running show processlist.

   The value of trx_query is NULL, but it does not mean that the transaction is not executed. If the transaction contains multiple SQL statements and all of them have been executed, no statement is displayed in the trx_query field. If the transaction is being executed, InnoDB cannot determine whether there are any subsequent SQL statements and when the transaction is committed. In this case, trx_query cannot determine which SQL statement is being executed, so NULL is displayed.

2. Kill the long transaction, and then change the instance class or upgrade the minor version.
3. Do not execute any long transactions when you are changing the instance class or upgrading the minor version.