mysql临时表报错

24123r2

今天大数据研发同事反馈在抽取数据时一直报错.让开发提供报错日志如下:

1 2	Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_4.MYI'; try to repair it   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1074)

从报错发现是临时表报错,需要修复(这种报错是只有myisam引擎出现这种提示repair呀,但是线上都是innodb存储引擎呀??)
相关临时表可以看下面链接为什么是myisam存储引擎.
http://www.cnblogs.com/jevo/p/3262227.html
我们接着看mysql error日志

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_8.MYI'; try to repair it 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_2.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_4.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_7.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_3.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_9.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_5.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_6.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226 161229  1:32:36 [ERROR] /export/servers/mysql/bin/mysqld: Incorrect key file for table '/export/data/mysql/tmp/#sql_6c04_1.MYI'; try to repair it 161229  1:32:36 [ERROR] Got an error from unknown thread, /usr/local/test/mysql-5.5.38/storage/myisam/mi_write.c:226

因为研发反馈是已经不是当时的现场,所以得查原因就比较麻烦.懂C的可以直接看源码(已经说的很明显了)
但是我们物理空间87%还好.但是发现监控中有100%.肯定是空间不足引擎的.

这些原因已经很明白了,就是使用临时表空间不足导致创建失败.

解决方法:

• 扩容磁盘
  
• 通知研发提数控制并发(我个人采用的这个)