1

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.

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

Query is:

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.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Check this [link](http://stackoverflow.com/questions/11805793/incorrect-key-file-for-table-tmp-sql-3c51-0-myi-try-to-repair-it) – Saty Jul 08 '15 at 06:01
  • Thanks @saty that is different issue, now i have got solution of my problem. – Zafar Malik Jul 08 '15 at 06:06

1 Answers1

1

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;

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.

Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Since you didn't specify which syntax error was made, here it is: an `AND` towards the end of 4th and 5th line instead of a `=`. – dr_ Jun 03 '16 at 11:44
  • if you see query in question and in answer then difference is that developer used "AND" instead of "=" in 2nd and 3rd join. – Zafar Malik Jun 06 '16 at 03:40
  • That's exactly what I wrote. – dr_ Jun 06 '16 at 09:02