【trouble shooting】ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_28b7_0.MYI'; try to repair it
基础数据
mysql> select * from person limit 9;
+------+------------+------+--------+
| id | first_name | age | gender |
+------+------------+------+--------+
| 1 | Bob | 25 | M |
| 2 | Jane | 20 | F |
| 3 | Jack | 30 | M |
| 4 | Bill | 32 | M |
| 5 | Nick | 22 | M |
| 6 | Kathy | 18 | F |
| 7 | Steve | 36 | M |
| 8 | Anne | 25 | F |
| 9 | Mike | 25 | M |
+------+------------+------+--------+
9 rows in set (0.00 sec)
存储过程
delimiter //
CREATE PROCEDURE simpleproc(IN param1 INT,OUT param2 INT)
BEGIN
while param1>0 DO
insert into person
select * from person;
set param1=param1 -1;
end while;
SELECT COUNT(*) INTO param2 FROM person;
END//
delimiter ;
调用存储过程，输入参数100次
mysql> call simpleproc(100,@a);
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_28b7_0.MYI'; try to repair it
【解决方案】
第一反应，需要详细了解错误代码代表着什么，MySQL自带一个解读error code的程序，它是在linux层面运行的，可以查询关于“系统”或“数据库”的错误，详细介绍：perror — Explain Error Codes
[root@localhost tmp]# cd /usr/local/mysql/bin
[root@localhost bin]# ls
innochecksum mysqlbug mysql_embedded mysql_tzinfo_to_sql
msql2mysql mysqlcheck mysql_find_rows mysql_upgrade
myisamchk mysql_client_test mysql_fix_extensions mysql_waitpid
myisam_ftdump mysql_client_test_embedded mysqlhotcopy mysql_zap
myisamlog mysql_config mysqlimport perror
myisampack mysql_convert_table_format mysql_plugin person.txt
my_print_defaults mysqld mysql_secure_installation replace
mysql mysqld-debug mysql_setpermission resolveip
mysqlaccess mysqld_multi mysqlshow resolve_stack_dump
mysqlaccess.conf mysqld_safe mysqlslap
mysqladmin mysqldump mysqltest
mysqlbinlog mysqldumpslow mysqltest_embedded
[root@localhost bin]# ./perror 126
OS error code 126: Required key not available
MySQL error code 126: Index file is crashed
'/tmp/#sql_28b7_0.MYI' ，MYI的后缀，是MyISM引擎的表，尽管提示让我们去修复它 'try to repair it'，本来可以使用myisamcheck程序来“检查”这个表的问题
[root@localhost bin]# ls
innochecksum mysqlbug mysql_embedded mysql_tzinfo_to_sql
msql2mysql mysqlcheck mysql_find_rows mysql_upgrade
myisamchk mysql_client_test mysql_fix_extensions mysql_waitpid
myisam_ftdump mysql_client_test_embedded mysqlhotcopy mysql_zap
myisamlog mysql_config mysqlimport perror
myisampack mysql_convert_table_format mysql_plugin person.txt
my_print_defaults mysqld mysql_secure_installation replace
mysql mysqld-debug mysql_setpermission resolveip
mysqlaccess mysqld_multi mysqlshow resolve_stack_dump
mysqlaccess.conf mysqld_safe mysqlslap
mysqladmin mysqldump mysqltest
mysqlbinlog mysqldumpslow mysqltest_embedded
但是这个表在/tmp目录的，说明它只是一个临时表，临时表在使用完以后会被删除的，所以无法被找到
[root@localhost tmp]# ls -la
total 2264
drwxrwxrwt. 9 root root 4096 Jan 2 11:45 .
dr-xr-xr-x. 18 root root 4096 Dec 21 09:47 ..
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .font-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .ICE-unix
-rwxr-xr-x. 1 root root 2294895 Dec 26 09:45 largeRelationsInsertFile.sql
drwxrwxrwx. 2 root root 6 Dec 24 11:20 lijunda
srwxrwxrwx. 1 mysql mysql 0 Dec 30 20:08 mysql.sock
drwxrwxrwx. 2 1001 1001 6 Dec 20 21:00 mysql-unique-ids
-rwxr-xr-x. 1 root root 9677 Dec 26 10:21 smallRelationsInsertFile.sql
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .Test-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .X11-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .XIM-unix
实际上这个报错是因为，大量的数据操作，需要大量的临时空间，临时空间用来存放临时数据，/tmp 临时空间用完了就会报错了，这个涉及到的原理晚点补充
It can be because the
/tmpfolder is running out of space.The name you're seeing is just some internal random name.
详细参考：
1.MySQL: Can't create/write to file '/tmp/#sql_3c6_0.MYI' (Errcode: 2) - What does it even mean?
2.Why does MySQL produce so many temporary MYD files?
所以下一步的思路是查看 /tmp的空间容量
[root@localhost tmp]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 6.7G 4.2G 2.6G 62% /
devtmpfs 911M 0 911M 0% /dev
tmpfs 921M 0 921M 0% /dev/shm
tmpfs 921M 12M 909M 2% /run
tmpfs 921M 0 921M 0% /sys/fs/cgroup
/dev/sda1 497M 146M 351M 30% /boot
tmpfs 185M 0 185M 0% /run/user/0
[root@localhost tmp]# df -h /tmp
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/centos-root 6.7G 4.2G 2.6G 62% /
一般情况下，如果/tmp的空间容量不足，有两个方法
1. 通过my.cnf配置文件，把tmp目录，换到一个空间足够大的分区上
2. 增加硬盘空间
3. 减少临时空间的利用，call simpleproc(100) 变成 2次 call simpleproc(50) 或3次什么的
现在的情况，因为/tmp已经在最大容量的 '/dev/mapper/centos-root'的分区了，可以考虑第2种或者第3种方法
重新调用stored procedure ，确实在 /tmp目录下生成一个叫 '#sql_28b7_0' 的临时表
[root@localhost tmp]# ls -lah
total 515M
drwxrwxrwt. 9 root root 4.0K Jan 2 17:13 .
dr-xr-xr-x. 18 root root 4.0K Dec 21 09:47 ..
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .font-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .ICE-unix
-rwxr-xr-x. 1 root root 2.2M Dec 26 09:45 largeRelationsInsertFile.sql
drwxrwxrwx. 2 root root 6 Dec 24 11:20 lijunda
srwxrwxrwx. 1 mysql mysql 0 Dec 30 20:08 mysql.sock
drwxrwxrwx. 2 1001 1001 6 Dec 20 21:00 mysql-unique-ids
-rwxr-xr-x. 1 root root 9.5K Dec 26 10:21 smallRelationsInsertFile.sql
-rw-rw----. 1 mysql mysql 342M Jan 2 17:13 #sql_28b7_0.MYD
-rw-rw----. 1 mysql mysql 1.0K Jan 2 17:13 #sql_28b7_0.MYI
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .Test-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .X11-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .XIM-unix
而这个临时表是会随着数据量的增加变大的，当它大到一定程度，就会把/tmp撑爆了
[root@localhost tmp]# ls -lah
total 1.5G
drwxrwxrwt. 9 root root 4.0K Jan 2 17:17 .
dr-xr-xr-x. 18 root root 4.0K Dec 21 09:47 ..
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .font-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .ICE-unix
-rwxr-xr-x. 1 root root 2.2M Dec 26 09:45 largeRelationsInsertFile.sql
drwxrwxrwx. 2 root root 6 Dec 24 11:20 lijunda
srwxrwxrwx. 1 mysql mysql 0 Dec 30 20:08 mysql.sock
drwxrwxrwx. 2 1001 1001 6 Dec 20 21:00 mysql-unique-ids
-rwxr-xr-x. 1 root root 9.5K Dec 26 10:21 smallRelationsInsertFile.sql
-rw-rw----. 1 mysql mysql 1.4G Jan 2 17:18 #sql_28b7_0.MYD
-rw-rw----. 1 mysql mysql 1.0K Jan 2 17:17 #sql_28b7_0.MYI
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .Test-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .X11-unix
drwxrwxrwt. 2 root root 6 Dec 18 00:44 .XIM-unix
If a statement is bigger than this, the thread opens a temporary file to store the transaction. The temporary file is deleted when the thread ends.
