0

On MySQL 5.7. I would like to prevent all alter table scripts \ update scripts that are taking longer than 10 minutes to run.

Is it possible?

I've tried to set MAX_EXECUTION_TIME and it does work but only for SELECT queries. I still could execute a long update\alter query.

1
  • Why do you want to block ALTER TABLE that takes more than 10 minutes? Are you aware there are free tools like pt-online-schema-change that allow you to continue accessing the table while it applies the alter? Sep 12, 2021 at 17:23

1 Answer 1

0

No straightforward way.

You could write code to perform SHOW PROCESSLIST every minute, ignore system actions that run 'forever', locate only "alter/update" commands (but not scripts), check the "Time", and issue "kill pid".

If this is a team effort, I would (instead) implement a review process for every "alter/update script" to try to fix scripts before attempting them.

Keep in mind that killing a command after 10 minutes:

  • Wasted some resources for 10 minutes.
  • May have to spend more than 10 minutes in ROLLBACK (in the case of UPDATE), again wasting resources.
  • And the desired task is still left to be done.

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.