Can you rollback a query in state 'committing alter table to storage engine'

Question

We've got an InnoDB table with 70 million rows, and we have been trying to run an alter table statement to modify and add a couple of columns. The query seems to have altered the table, and is now in the state of 'committing alter table to storage engine'.

START TRANSACTION;
ALTER TABLE table
  MODIFY COLUMN column1 int(11) NOT NULL DEFAULT 0,
  MODIFY COLUMN column2 tinyint(1) NOT NULL DEFAULT 1,
  ADD COLUMN column3 int(11),
  ADD COLUMN column4 int(11) NOT NULL DEFAULT 1,
  ADD COLUMN column5 varchar(255);
COMMIT;

This has been running overnight, and is at 19 hours at the current time. We do not have the performance schema enabled so cannot look at an estimated time of completion. My concern lies as to what the query is doing and whether the query will rollback if killed. I have seen other questions relate to queries that are stuck in copying to tmp tables, or awaiting a table lock. However I cannot find anything about being stuck while the alter table is committing.

Is it safe to kill a query in this state, and if the query is killed, will it rollback successfully?

The server is running MariaDB 10.2

Answer 1

I implemented the ALGORITHM=INPLACE and LOCK=NONE for InnoDB in MySQL 5.6. Depending on the previous table definition, this operation could imply ALGORITHM=INPLACE, or it could fall back to ALGORITHM=COPY. Starting with MariaDB 10.3 (MDEV-11369), ADD COLUMN would be instantaneous; before that, the table would have to be rebuilt. (The syntax ALGORITHM=INPLACE is very misleading.) Starting with MariaDB 10.2.13 and 10.3.5 (MDEV-11415), ALGORITHM=COPY will no longer write undo log records for the individual rows, and the rollback (in case of client disconnect or killed server) should be much faster.

Answer 2

From the documentation:

Some statements cannot be rolled back. In general, these include data definition language (DDL) statements, such as those that create or drop databases, those that create, drop, or alter tables or stored routines.

You should design your transactions not to include such statements. If you issue a statement early in a transaction that cannot be rolled back, and then another statement later fails, the full effect of the transaction cannot be rolled back in such cases by issuing a ROLLBACK statement.