【trouble shooting】ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_28b7_0.MYI'; try to rep...

 

 

基础数据

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 /tmp folder 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.

转载于:https://www.cnblogs.com/hellotracy/articles/5094391.html