Can you roll back the request in the state "overwrite the table of changes in the repository",

Question

We have an InnoDB table with 70 million rows, and we are trying to run the alter table statement to modify and add multiple columns. The query seems to have changed the table, and now it is in a state of "forced replacement of the table for storage."

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; 

It works overnight, and currently it is 19 hours. We don’t have the performance scheme turned on, so we can’t see the estimated completion time. My concern is what the request does, and whether the request will be rolled back if it is killed. I saw that other questions relate to queries that are stuck when copying to tmp tables or waiting for a table lock. However, I cannot find anything that you were stuck while writing the alter table.

Is it possible to kill a request in this state, and if the request is killed, will it roll back successfully?

Server starts MariaDB 10.2

Answer 1

From the documentation :

Some operators cannot be undone. In general, they include data definition language (DDL) instructions, such as those that create or delete databases, those that create, delete, or modify tables or stored procedures.

You must design your transactions to not include such statements. If you issue an expression at an early stage of a transaction that cannot be undone, and then another statement fails, the full effect of the transaction cannot be undone in such cases by issuing the ROLLBACK operator.

Answer 2

I implemented ALGORITHM = INPLACE and LOCK = NONE for InnoDB in MySQL 5.6. Depending on the previous definition of the table, this operation may mean ALGORITHM = INPLACE, or it may return to ALGORITHM = COPY. Starting with MariaDB 10.3 ( MDEV-11369 ), ADD COLUMN will be instant; 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 canceled journal entries for individual lines, and rollback (if the client or the killed one is disconnected server) should be much faster.

Answer 3

Since ALTER TABLE is a DDL statement, it invokes an implicit commit when it is executed. This means that it cannot be undone, but the interruption of DDL (by killing the connection) will lead to the rollback of already applied changes in a controlled mode.

Given that you use the ALTER TABLE operation by default (no ALGORITHM ), its cancellation should be relatively quick, as it should do everything, at least according to my information, refuse a new copy of the table.