MySQL 表 '/tmp/#sql_3c51_0.MYI' 的密钥文件不正确；尝试修复它

Incorrect key file for table '/tmp/#sql_3c51_0.MYI'; try to repair it

提问by Suneth Kalhara

I wrote a query and this runs on my local server correctly it has less data,

我写了一个查询，它在我的本地服务器上正确运行，它的数据较少，

but when i run this on production server it gets an error - (this has more data around 6GB)

但是当我在生产服务器上运行它时出现错误 - （这有更多大约 6GB 的数据）

Incorrect key file for table '/tmp/#sql_3c51_0.MYI'; try to repair it

Here is my query

这是我的查询

SELECT   
    `j25_virtuemart_products`.`virtuemart_product_id`,
    `product_name`, 
    `product_unit`,
    `product_s_desc`,
    `file_url_thumb`,
    `virtuemart_custom_id`, 
    `custom_value`   
    FROM 
    `j25_virtuemart_product_customfields`,
    `j25_virtuemart_products`,
    `j25_virtuemart_products_en_gb`,
    `j25_virtuemart_product_medias`,
    `j25_virtuemart_medias`     
    WHERE
    (
    `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_products_en_gb`.`virtuemart_product_id`
    AND 
    `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_product_customfields`.`virtuemart_product_id`)

AND

    `j25_virtuemart_products`.`virtuemart_product_id`=`j25_virtuemart_product_medias`.`virtuemart_product_id`
    AND 
    `j25_virtuemart_product_medias`.`virtuemart_media_id`=`j25_virtuemart_medias`.`virtuemart_media_id`

    GROUP BY `j25_virtuemart_products`.`virtuemart_product_id`

    LIMIT 0, 1000;

Anyone know how to recover from that error - something like otimize this query or any other way thank you

任何人都知道如何从该错误中恢复 - 例如优化此查询或任何其他方式谢谢

采纳答案by Hawili

Do these steps

做这些步骤

Stop mysql service

停止mysql服务

rename the .myi file to x.old

将 .myi 文件重命名为 x.old

Start mysql

启动mysql

REPAIR all the tables in query,MySQL will rebuild key file

修复查询中的所有表，MySQL 将重建密钥文件

回答by Mircea Vutcovici

The problem is caused by the lack of disk space in /tmp folder. The /tmp volume is used in queries that require to create temporary tables. These temporary tables are in MyISAM format even if the query is using only tables with InnoDB.

该问题是由 /tmp 文件夹中的磁盘空间不足引起的。/tmp 卷用于需要创建临时表的查询。即使查询仅使用带有 InnoDB 的表，这些临时表也是 MyISAM 格式。

Here are some solutions:

以下是一些解决方案：

• optimize the query so it will not create temporary tables (rewrite the query, split it in multiple queries, or add proper indexes, analyze the execution plan with pt-query-digestand EXPLAIN <query>) See this Percona article about temporary tables.
• optimize MySQL so it will not create temporary tables (sort_buffer_size, join_buffer_size). See: https://dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it
• make tables smaller. If possible, delete unneeded rows
• use data types that take less space
• add more disk space on the volume where /tmp folder resides
• change the temp folder user by mysql by setting the TMPDIRenvironment variable prior to mysqld start-up. Point TMPDIRto a folder on a disk volume that has more free space. You can also use tmpdiroption in /etc/my.cnfor --tmpdirin the command line of the mysqld service. See: B.5.3.5 Where MySQL Stores Temporary Files

• 优化查询，使其不会创建临时表（重写查询，将其拆分为多个查询，或添加适当的索引，使用pt-query-digest和分析执行计划EXPLAIN <query>）请参阅Percona 这篇关于临时表的文章。
• 优化 MySQL 使其不会创建临时表（sort_buffer_size、join_buffer_size）。请参阅：https: //dba.stackexchange.com/questions/53201/mysql-creates-temporary-tables-on-disk-how-do-i-stop-it
• 使表更小。如果可能，删除不需要的行
• 使用占用更少空间的数据类型
• 在 /tmp 文件夹所在的卷上添加更多磁盘空间
• 通过TMPDIR在 mysqld 启动之前设置环境变量，通过 mysql 更改临时文件夹用户。指向TMPDIR具有更多可用空间的磁盘卷上的文件夹。您还tmpdir可以在mysqld 服务的命令行中/etc/my.cnf或--tmpdir在命令行中使用选项。请参阅：B.5.3.5 MySQL 存储临时文件的位置

回答by Zafar Malik

So many answers are above and the question owner already got the solution as suggested by @Hawili, and a long time has been passed since this problem was raised. But as this a common issue, I wanted to share my experience so that if someone got this issue again due to different reasons then can get solution from here.

上面有这么多答案，问题所有者已经按照@Hawili 的建议得到了解决方案，并且自提出此问题以来已经过去了很长时间。但由于这是一个常见问题，我想分享我的经验，以便如果有人由于不同原因再次遇到此问题，则可以从这里获得解决方案。

Case 1:

情况1：

Most common reason is that a query is fetching data greater than the size of your /tmp partition. Whenever you get this issue during a query, look at your /tmp folder size. Temporary tables are created and removed automatically, and if available space here gets down to 0 during this query it means either you need to optimize your query or need increase /tmp's partition size.

最常见的原因是查询正在获取大于 /tmp 分区大小的数据。每当您在查询期间遇到此问题时，请查看您的 /tmp 文件夹大小。临时表是自动创建和删除的，如果在此查询期间此处的可用空间降至 0，则意味着您需要优化查询或需要增加 /tmp 的分区大小。

Note: Sometimes it isn't an individual query: if a combination of heavy queries do this at the same time on the same server then you can get this issue, where normally the individual queries would execute without any error.

注意：有时它不是单个查询：如果大量查询的组合在同一台服务器上同时执行此操作，那么您可能会遇到此问题，通常单个查询执行时不会出现任何错误。

Case 2:

案例2：

In case of a corrupt myisam table where you need to repair, the directory path will be different than /tmp in the error message.

如果需要修复损坏的 myisam 表，则目录路径将与错误消息中的 /tmp 不同。

Case 3: (rare case)

情况3：（罕见情况）

Sometimes due to an incorrect table join you can get this error. This is actaully a syntax error but mysql can throw this error instead. You can check details here at below link-

有时由于表连接不正确，您可能会收到此错误。这实际上是一个语法错误，但 mysql 可以改为抛出此错误。您可以在以下链接中查看详细信息-

Incorrect key file for table '/tmp/#sql_18b4_0.MYI'; try to repair it

表 '/tmp/#sql_18b4_0.MYI' 的密钥文件不正确；尝试修复它

回答by ajacian81

Check the location of your tmp dir, by running df -h. Make sure there's enough space to grow the temp file, it could be several gigs.

通过运行检查 tmp 目录的位置df -h。确保有足够的空间来增长临时文件，它可能是几个演出。

Edit: If you have enough free space, I'd check to make sure every column you're indexing on or including in the WHERE clause is indexed.

编辑：如果您有足够的可用空间，我会检查以确保您正在索引或包含在 WHERE 子句中的每一列都被索引。

回答by klodoma

Check your database server that you have enough disk space on it. If disks are full this error is displayed. Now, which folders you should look at it depends on your setup.

检查您的数据库服务器上是否有足够的磁盘空间。如果磁盘已满，则会显示此错误。现在，您应该查看哪些文件夹取决于您的设置。

回答by Sourja Banerjee

I used this following command and the error was gone:

我使用了以下命令，错误消失了：

mysqlcheck --all-databases -r #repair

mysqlcheck --all-databases -r #repair

I got this solution from the cpanel forum

我从cpanel 论坛得到了这个解决方案

回答by Rossano Fenner

Same problem to me

对我来说同样的问题

Run df -hto see if your partition /tmp have enough space

运行df -h看看你的分区 /tmp 是否有足够的空间

In my case /tmp was a overflow filesystem:

在我的例子中 /tmp 是一个溢出文件系统：

overflow 1,0M 24K 1000K 3% /tmp

overflow 1,0M 24K 1000K 3% /tmp

What happened:

发生了什么：

I had some problem here and my partition /was full, then Debian distro created a new partition /tmpin RAM memor, to use temporarily. This /tmp1MB partition is not big enough for system use.

我在这里遇到了一些问题，我的分区/已满，然后 Debian 发行版/tmp在 RAM 内存中创建了一个新分区，以临时使用。这个/tmp1MB 的分区不足以供系统使用。

Solution: Run the following command to remove this temporary created partition /tmp

解决方案：运行以下命令删除这个临时创建的分区 /tmp

sudo umount -l /tmp

sudo umount -l /tmp

回答by undefined

Restart MySQL service using this command line /etc/init.d/mysqld restartin terminal.

/etc/init.d/mysqld restart在终端中使用此命令行重新启动 MySQL 服务。