MySQL Maintenance – Is It Safe to Stop Maintenance Check in Progress?

maintenanceMySQLmysql-5.5

I am using HeidiSQL and running maintenance checks on all tables in the database (mysql 5.5.46-0+deb7u1-log) using:

CHECK TABLE `database`.`table` QUICK FAST MEDIUM

It just goes through and runs that command on each table, one at a time. However, I need to restart the server. Is it safe to kill this check without corrupting anything?

Best Answer

Please note what the MySQL Documentation on CHECK TABLE says about running on InnoDB tables, bulletpoint 7 under the heading CHECK TABLE Usage Notes for InnoDB Tables

When running CHECK TABLE on large InnoDB tables, other threads may be blocked during CHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for CHECK TABLE operations. If InnoDB detects semaphore waits of 240 seconds or more it starts printing InnoDB monitor output to the error log. If a lock request extends beyond the semaphore wait threshold, InnoDB aborts the process. To avoid the possibility of a semaphore wait timeout entirely, you can run CHECK TABLE QUICK instead of CHECK TABLE.

CHECK TABLE never implies doing writes, just blocking other connections in the worst. CHECK TABLE should abort itself after 4 minutes as a courtesy to other connections. In your case, if there are other processes, wait 4-5 minutes. Then kill the process. If there is nothing else running, kill the process now. Doing a shutdown with it still running may block the shutdown for 4 minutes or more, but will eventually shutdown.

Please run SET GLOBAL innodb_fast_shutdown = 0 beforehand.

Related Question