Mysql – Can you rollback a query in state ‘committing alter table to storage engine’

innodbmariadbMySQL

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

Best Answer

I am sorry to have to inform you, but ALTER TABLE cannot be rolled back. In fact, ALTER TABLE triggers an implicit commit.

According to Page 418 Paragraph 3 of the Book

MySQL 5.0 Certification Study Guide

the following commands can and will trigger an implicit commit

  • ALTER TABLE
  • BEGIN
  • CREATE INDEX
  • DROP DATABASE
  • DROP INDEX
  • DROP TABLE
  • RENAME TABLE
  • TRUNCATE TABLE
  • LOCK TABLES
  • UNLOCK TABLES
  • SET AUTOCOMMIT = 1
  • START TRANSACTION

I have mentioned this before

In your case, the ALTER TABLE will either finish or you will have a temp table left hanging around. In either case, it will not be a rollback in a transactional sense.

Related Question