I have a 192GB table that I started adding an index to over night.

This was still going several hours later.

At the time the alter table started the database was very quiet.

There were other queries attempting to run during that time, including some cron jobs, so when I woke up there were several copies of some of these queries blocked on this index add. I tried killing the ALTER TABLE, but it's still showing:

204997797      root       localhost     web_db      23793 Killed manage k alter table reports add key requisition_ix (requisition_id)

I have also killed the other queries that were running during the night, but have not yet stopped new connections to the database.

Is there a way to force kill it?

percona-xtradb-cluster-server-5.5 (currently standalone)

There is currently 376GB available space on the server. There is on #sql-5db_c3804a5.frm file but no other temp files I can see.

In the error.log I see things like this:

211015  8:07:22 [ERROR] Table web_db/reports contains 14 indexes inside InnoDB, which is different from the number of indexes 13 defined in the MySQL

If there is absolutely no way to kill it, is there a way to monitor the progress of the index add/rollback?


Is there a way to force kill it?

Noway. You must wait for killed query modifications rollback. Even if you should try to restart server the rollback will be continued during the restarting. And forcing this process may cause data damage. Wait...

Is there a way to see what the progress is?

No such feature.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.