Force kill "ALTER TABLE" safely?

MySQL & MariaDB Percona XtraDB Cluster 5.x
,
1

I tried to add an index to a 192GB InnoDB table. There is 434GB available space on the server. There was very little going on in the database at the time.

Several hours later it was still going, so I tried to KILL it.

Now it’s in this state:

       Id      User         Host/IP         DB       Time    Cmd    State Query
       --      ----         -------         --       ----    ---    ----- ----------
204997797      root       localhost     web_db      26586 Killed manage k alter table reports add key requisition_ix (requisition_id)

Is there a way to safely kill this?

Alternatively, is there a way to monitor the progress?

2 Likes
2

Apparently there’s no way to get it killed faster. You just have to wait.
Also there seems to be no way to monitor the progress.
It finally finished.

2 Likes
3

hi, mwood
you can read this document to monitor the progress of alter table,
https://dev.mysql.com/doc/refman/5.7/en/monitor-alter-table-performance-schema.html

1 Like
4

hi, mwood
Did the DML service logic work with the table in the killed state?

5

Hi @yunhyeonglee, welcome to Percona community.

Would you like to clarify what you meant by service logic? When the command is in killed state it is still performing cleanup; the appropriate locks should be in place.

Thanks,
K

6

There are two questions.
Does the DML service query work in that table while the alter query is killed?

As far as I know, for alter queries, it kills in a few seconds, is this right?