Track disk related issues for MySQL ERROR 1034 (HY000) during an ALTER TABLE

track-disk-usage.sh

# During an ALTER TABLE run, if you experience the following error:
#
# mysql> ALTER TABLE `my_table` ALGORITHM=INPLACE, ENGINE=InnoDB;
# ERROR 1034 (HY000): Incorrect key file for table 'my_table'; try to repair it 
#
# It may be disk related. To track this, run the following commands (in a screen session)
# and leave them running whilst running the ALTER again. After the error displays, check
# the files for obvious signs of disk related issues. Please note, the results from the
# df-full.log may not be accurate (on their own), so use this with the lsof.log
# Also, please ensure the following commands are run seperatley in parallel (with seperate
# screen sessions) or they will run one after the other (if you ran this file directly).

# Track list of open files
date > lsof.log; while true; do lsof -F sn0 | tr -d '\000' | grep deleted | grep mysqltmp | sed 's/^[a-z]*\([0-9]*\)n/\1 /' | sort -rn >> lsof.log; sleep 120; done

# Track disk space usage
date > df.log; while true; do df -P | grep 'mysql-fs' >> df.log; sleep 120; done
 
# Track full disk space stats
date > df-full.log; while true; do df -P >> df-full.log; sleep 120; done