表'/tmp/#sql_18b4_0.MYI'的密钥文件不正确；尝试修复它 [英] Incorrect key file for table '/tmp/#sql_18b4_0.MYI'; try to repair it

问题描述

我从开发人员那里得到了一个查询，该查询未在服务器上执行并且给出了以下错误-

I got a query from our developers that is not executing on server and giving below error-

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

我已经逐个检查了所有表及其索引，所有内容似乎都在文件中.甚至我已经在其他查询联接中检查了所有这些表，这些联接正在获取比该查询更多的数据并且工作正常.

I have checked all tables individually and their index, everything seems file. Even I have checked all these tables in some other query join which is fetching more data than this query and working fine.

即使这些表几乎每个表保留少于1000条记录.

Even these tables hardly keep less than 1000 records per table.

查询是:

SELECT `PsMasterSubject`.`id`, `PsMasterSubject`.`name`, `PsProgram`.`name`, `PsStreamLevel`.`id` 
FROM `misdb`.`ps_master_subjects` AS `PsMasterSubject` 
LEFT JOIN `misdb`.`ps_programs` AS `PsProgram` ON (`PsMasterSubject`.`ps_program_id` = `PsProgram`.`id`) 
LEFT JOIN `misdb`.`ps_stream_levels` AS `PsStreamLevel` ON (`PsStreamLevel`.`id` AND `PsProgram`.`ps_stream_level_id`) 
LEFT JOIN `misdb`.`ps_program_levels` AS `PsProgramLevel` ON (`PsProgramLevel`.`id` AND `PsStreamLevel`.`ps_program_level_id`) 
WHERE 1 = 1 
ORDER BY `PsMasterSubject`.`id` DESC LIMIT 10;

我遇到了类似的问题，但是我检查了我的餐桌没有破损.

I am getting some issues same like this but I have checked that my table is not currupt.

我们将不胜感激任何快速帮助.

Any quick help will be highly appreciated.

推荐答案

哦，这对我的开发人员来说是一个愚蠢的错误，经过30分钟的脑力激荡，以不同的方式设计了此查询，我得到了开发人员正在使用join的问题以错误的方式，由于此mysql无法正确连接表数据并占用/tmp目录中的所有空间并引发此错误.正确的查询在这里-

Oh shit this was a silly mistake from my developer end, After 30 minutes brain storming to design this query in different way I got this issue that developer was using join in wrong way, due to this mysql was not able to proper join tables data and consuming all space in /tmp directory and throwing this error. Correct query is here-

SELECT `PsMasterSubject`.`id`, `PsMasterSubject`.`name`, `PsProgram`.`name`, `PsStreamLevel`.`id` 
FROM `misdb`.`ps_master_subjects` AS `PsMasterSubject` 
LEFT JOIN `misdb`.`ps_programs` AS `PsProgram` ON (`PsMasterSubject`.`ps_program_id` = `PsProgram`.`id`) 
LEFT JOIN `misdb`.`ps_stream_levels` AS `PsStreamLevel` ON (`PsStreamLevel`.`id` = `PsProgram`.`ps_stream_level_id`) 
LEFT JOIN `misdb`.`ps_program_levels` AS `PsProgramLevel` ON (`PsProgramLevel`.`id` = `PsStreamLevel`.`ps_program_level_id`) 
WHERE 1 = 1 
ORDER BY `PsMasterSubject`.`id` DESC LIMIT 10;

现在的问题是，这是一个mysql错误，因为mysql应该抛出错误的语法错误，但是mysql正在尝试为临时数据创建一个临时表.

Now question is here that is this a mysql bug as mysql should throw wrong syntax error but here mysql is trying to create a temporary table for temp data.

如果有人能对我说清楚，我将非常感激.

I will be very thankful if anyone can clear this to me.

这篇关于表'/tmp/#sql_18b4_0.MYI'的密钥文件不正确；尝试修复它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！