MySQL，错误126:表的密钥文件不正确 [英] MySQL, Error 126: Incorrect key file for table
问题描述
我阅读了以下相关问题，但答复并未使我满意: MySQL:#126-错误的密钥文件桌子
I read the following question that has relevance, but the replies didn't satify me: MySQL: #126 - Incorrect key file for table
运行查询时出现此错误
错误126(HY000):表的密钥文件不正确
ERROR 126 (HY000): Incorrect key file for table`
问题
当我试图找到问题时，我找不到问题，所以我不知道如何使用修复命令来解决它. 除了我已经尝试过的方法以外，是否有其他任何方法可以指示我如何找到导致此问题的问题?
The question
When I'm trying to find the problem I cant't find one, so I don't know how to fix it with the repair command. Is there any pointers to how I can find the problem causing this issue in any other way then I already have tried?
mysql> SELECT
-> Process.processId,
-> Domain.id AS domainId,
-> Domain.host,
-> Process.started,
-> COUNT(DISTINCT Joppli.id) AS countedObjects,
-> COUNT(DISTINCT Page.id) AS countedPages,
-> COUNT(DISTINCT Rule.id) AS countedRules
-> FROM Domain
-> JOIN CustomScrapingRule
-> AS Rule
-> ON Rule.Domain_id = Domain.id
-> LEFT JOIN StructuredData_Joppli
-> AS Joppli
-> ON Joppli.CustomScrapingRule_id = Rule.id
-> LEFT JOIN Domain_Page
-> AS Page
-> ON Page.Domain_id = Domain.id
-> LEFT JOIN Domain_Process
-> AS Process
-> ON Process.Domain_id = Domain.id
-> WHERE Rule.CustomScrapingRule_id IS NULL
-> GROUP BY Domain.id
-> ORDER BY Domain.host;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_2b5_4.MYI'; try to repair it
mysqlcheck
root@scraper:~# mysqlcheck -p scraper
Enter password:
scraper.CustomScrapingRule OK
scraper.Domain OK
scraper.Domain_Page OK
scraper.Domain_Page_Rank OK
scraper.Domain_Process OK
scraper.Log OK
scraper.StructuredData_Joppli OK
scraper.StructuredData_Joppli_Product OK
已计数的行
mysql> select count(*) from CustomScrapingRule;
+----------+
| count(*) |
+----------+
| 26 |
+----------+
1 row in set (0.04 sec)
mysql> select count(*) from Domain;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.01 sec)
mysql> select count(*) from Domain_Page;
+----------+
| count(*) |
+----------+
| 134288 |
+----------+
1 row in set (0.17 sec)
mysql> select count(*) from Domain_Page_Rank;
+----------+
| count(*) |
+----------+
| 4671111 |
+----------+
1 row in set (11.69 sec)
mysql> select count(*) from Domain_Process;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.02 sec)
mysql> select count(*) from Log;
+----------+
| count(*) |
+----------+
| 41 |
+----------+
1 row in set (0.00 sec)
mysql> select count(*) from StructuredData_Joppli;
+----------+
| count(*) |
+----------+
| 11433 |
+----------+
1 row in set (0.16 sec)
mysql> select count(*) from StructuredData_Joppli_Product;
+----------+
| count(*) |
+----------+
| 130784 |
+----------+
1 row in set (0.20 sec)
更新
磁盘使用情况
Update
Disk usage
root@scraper:/tmp# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/xvda1 20G 4.7G 15G 26% /
none 4.0K 0 4.0K 0% /sys/fs/cgroup
udev 237M 4.0K 237M 1% /dev
tmpfs 49M 188K 49M 1% /run
none 5.0M 0 5.0M 0% /run/lock
none 245M 0 245M 0% /run/shm
none 100M 0 100M 0% /run/user
推荐答案
您的查询似乎返回了一个大型的中间结果集，该结果集要求 创建一个临时表，并为mysql的配置位置临时 磁盘表(/tmp)不足以容纳生成的临时表.
It appears that your query is returning a large intermediate result set requiring the creation of a temporary table and that the configured location for mysql temporary disk tables (/tmp) is not large enough for the resulting temporary table.
您可以尝试通过重新安装来增加tmpfs分区的大小:
You could try increasing the tmpfs partition size by remounting it:
mount -t tmpfs -o remount,size=1G tmpfs /tmp
您可以通过编辑/etc/fstab来使此更改永久生效
You can make this change permanent by editing /etc/fstab
如果无法执行此操作，则可以尝试更改磁盘位置 临时表，方法是编辑my.cnf文件中的"tmpdir"条目(或添加 如果还不存在的话).记住你的目录 选择应该由mysql用户可写
If you are unable to do this you could try changing the location of disk temporary tables by editing the "tmpdir" entry in your my.cnf file (or add it if it is not already there). Remember that the directory you choose should be writable by the mysql user
您还可以尝试通过增加 mysql配置选项的值:
You could also try preventing the creation of an on disk temporary table by increasing the values for the mysql configuration options:
tmp_table_size
max_heap_table_size
到更大的值.您将需要同时增加上述两个参数
to larger values. You will need to increase both of the above parameters
示例:
set global tmp_table_size = 1G;
set global max_heap_table_size = 1G;
这篇关于MySQL，错误126:表的密钥文件不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
