MySQL，错误126:表的密钥文件不正确。

I read the following question that has relevance, but the replies didn't satify me: MySQL: #126 - Incorrect key file for table

我读了下面这个有相关性的问题，但是回复没有让我感到满意:MySQL: #126 -不正确的密钥文件。

The problem

When running a query I get this error

在运行查询时，我得到了这个错误。

ERROR 126 (HY000): Incorrect key file for table`

错误126 (HY000):表的密钥文件不正确。

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?

当我试图找到问题时，我找不到，所以我不知道如何修复它与修理命令。有什么方法可以让我找到这个问题的答案吗?我已经尝试过了?

The query

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

counted rows

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

5 个解决方案

#1

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.

看起来，您的查询将返回一个大型中间结果集，该结果集需要创建临时表，并且mysql临时磁盘表(/tmp)的配置位置对于生成的临时表来说不够大。

You could try increasing the tmpfs partition size by remounting it:

您可以尝试通过重新计算tmpfs分区大小:

mount -t tmpfs -o remount,size=1G tmpfs /tmp

You can make this change permanent by editing /etc/fstab

您可以通过编辑/etc/fstab使此更改成为永久性的。

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

如果您无法做到这一点，您可以通过在my.cnf文件中编辑“tmpdir”条目来尝试更改磁盘临时表的位置(如果还没有的话，可以添加它)。请记住，您选择的目录应该由mysql用户编写。

You could also try preventing the creation of an on disk temporary table by increasing the values for the mysql configuration options:

您还可以通过增加mysql配置选项的值来阻止在磁盘临时表上创建:

tmp_table_size
max_heap_table_size

to larger values. You will need to increase both of the above parameters

更大的值。您将需要增加以上两个参数。

Example:

例子:

set global tmp_table_size = 1G;
set global max_heap_table_size = 1G;

#2

Splitting a complex query into multiple ones would be faster without needing to increase the temp table size

如果不需要增加临时表的大小，将一个复杂的查询拆分为多个查询会更快。

#3

In my case I just cleared temp files form temp location:

在我的例子中，我只是清除了temp文件的临时位置:

my.ini

my.ini

tmpdir = "D:/xampp/tmp"

tmpdir =“D:/ xampp / tmp”

And it worked for me.

这对我很有效。

#4

If your /tmp mount on a linux filesystem is mounted as overflow, often sized at 1MB, ie

如果您的/tmp安装在linux文件系统上，就会被安装为溢出，通常大小为1MB。

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
udev            7.9G   12K  7.9G   1% /dev
tmpfs           1.6G  348K  1.6G   1% /run
/dev/xvda1      493G  6.9G  466G   2% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
none            5.0M     0  5.0M   0% /run/lock
none            7.9G     0  7.9G   0% /run/shm
none            100M     0  100M   0% /run/user
overflow        1.0M  4.0K 1020K   1% /tmp               <------

this is likely due to you not specifying /tmp as its own partition and your root filesystem filled up and /tmp was remounted as a fallback.

这可能是因为您没有指定/tmp作为它自己的分区，并且您的根文件系统被填满，/tmp被重新安装作为一个后备。

I ran into this issue after running out of space on an EC2 volume. Once I resized the volume, I ran into the /tmp overflow partition filling up while executing a complicated view.

在EC2卷上耗尽了空间之后，我就遇到了这个问题。一旦我调整了音量大小，我就会在执行复杂的视图时遇到/tmp溢出分区。

To fix this after you've cleared space/resized, just unmount the fallback and it should remount at its original point (generally your root partition):

sudo umount -l /tmp

_{Note: -l will lazily unmount the disk.}

注意:-l将延迟卸载磁盘。

#5

-7

You just need to repair your table which use in search query. this problem generally occur on search query.

您只需要修复在搜索查询中使用的表。这个问题通常出现在搜索查询中。

go to "table_name" -> operation- > repair (just one click) effect may take some time to apply

转到“table_name”->操作->修复(只需一个点击)效果可能需要一些时间。

#1