Incorrect key file for table error on outer join logic in MySQL

Question

I have a lengthy MySQL query I'm attempting to use a FULL OUTER JOIN on, which does not exist in MySQL:

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
ORDER BY RAND() DESC;

The LEFT JOIN above is where I'm looking to instead use FULL OUTER JOIN logic. I understand it has to use multiple JOINs with UNION, but my attempts have resulted in various syntax errors.

What's the correct syntax to change the above to a FULL OUTER JOIN logic?

I'm trying this:

select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT OUTER JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id),
j25_mt_images AS mi,
j25_mt_cl as cl
UNION ALL
select c.cat_id,c.cat_name as cat_name, 
 c.cat_desc, c.cat_image, mi.filename, 
 l.link_id, l.user_id, l.address,l.city, 
 l.country,l.link_created,l.link_desc, 
 l.email,l.fax,l.link_hits, l.link_modified,
 l.link_name,l.postcode, l.price,l.link_rating, 
 l.state,l.telephone,l.link_votes,
 l.website, l.link_id, l.link_visited, cf.value
FROM j25_mt_cats as c,
j25_mt_links as l
RIGHT OUTER JOIN j25_mt_cfvalues AS cf ON cf.link_id = l.link_id,
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
 AND mi.link_id = l.link_id AND mi.ordering < 2  
 AND c.cat_id = cl.cat_id and c.cat_published = 1 
 AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
 AND cf.link_id IS NULL
ORDER BY RAND() DESC;

But it takes minutes to run, then returns #126 - Incorrect key file for table '/tmp/#sql_4f82_0.MYI'; try to repair it when I think I'm closer to the correct syntax. The disk isn't full or even close. Nor is this a table, it's just a query I'm running in phpMyAdmin.

You should see this http://stackoverflow.com/questions/4796872/full-outer-join-in-mysql — Thịt Bò Tái, Sep 23 '13 at 03:28
@ThịtBòTái Yes, that's the syntax I tried and I'm getting syntax errors and the sort. Edited my attempt into question. — Zeno, Sep 23 '13 at 03:30
hi @Zeno, ur table is crashed, please repair it, this is the references link that u can refer, http://dev.mysql.com/doc/refman/5.0/en/repair-table.html — Low Chee Mun, Sep 23 '13 at 06:19
Do **not** mix implicit joins (using `,` and `where` conditions) with explicit joins (using `JOIN` keywords) — , Sep 23 '13 at 07:00
@a_horse_with_no_name Does that mean I need to write this from scratch? — Zeno, Sep 24 '13 at 03:01
No, just get do your joins in a consistent manner (preferably everything using explicit `JOIN`s) — , Sep 24 '13 at 06:06

Low Chee Mun · Answer 1 · 2013-09-23T04:14:07.237

if possible please provide table design , sample column, the problem of unknown column i guess is because of your bottom join missing some cast

select c.cat_id,c.cat_name as cat_name, 
c.cat_desc, c.cat_image, mi.filename, 
l.link_id, l.user_id, l.address,l.city, 
l.country,l.link_created,l.link_desc, 
l.email,l.fax,l.link_hits, l.link_modified,
l.link_name,l.postcode, l.price,l.link_rating, 
l.state,l.telephone,l.link_votes,
l.website, l.link_id, l.link_visited, cf.value
from j25_mt_cats as c,
j25_mt_links as l 
LEFT OUTER JOIN j25_mt_cfvalues AS cf ON (cf.link_id = l.link_id)

//you mi is missng here [Start]

j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
AND mi.link_id = l.link_id AND mi.ordering < 2  
AND c.cat_id = cl.cat_id and c.cat_published = 1 
AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
AND cf.link_id IS NULL

// END

UNION ALL
select c.cat_id,c.cat_name as cat_name, 
c.cat_desc, c.cat_image, mi.filename, 
l.link_id, l.user_id, l.address,l.city, 
l.country,l.link_created,l.link_desc, 
l.email,l.fax,l.link_hits, l.link_modified,
l.link_name,l.postcode, l.price,l.link_rating, 
l.state,l.telephone,l.link_votes,
l.website, l.link_id, l.link_visited, cf.value

//u forgot to define c,l,cf at here

FROM j25_mt_cats as c, j25_mt_links as l 
RIGHT OUTER JOIN j25_mt_cfvalues as cf ON cf.link_id = l.link_id,
j25_mt_images AS mi,
j25_mt_cl as cl
where cf.cf_id = 40 and cl.link_id = l.link_id 
AND mi.link_id = l.link_id AND mi.ordering < 2  
AND c.cat_id = cl.cat_id and c.cat_published = 1 
AND c.cat_approved = 1 and l.link_published = 1 and l.link_approved = 1
AND cf.link_id IS NULL
ORDER BY RAND() DESC;

Thanks, strangely enough it's the `mi` one I'm getting an error on. Updated my question. — Zeno, Sep 23 '13 at 03:51
Weird, that makes it try to query for minutes then errors with `#126 - Incorrect key file for table '/tmp/#sql_4f82_0.MYI'; try to repair it` — Zeno, Sep 23 '13 at 04:27

score 1 · Accepted Answer · answered Sep 24 '13 at 08:13

1

This problem occurs, tipically, when you fill the temporary directory, and in your case, probably, your query creates a huge temporary file.

Apart optimize your query, try monitoring your partition where the temporary directory is located show variables like 'tmpdir'; during your query.

answered Sep 24 '13 at 08:13

Cristian Porta

4,393
3
19
26

Yep, 3GB file being created in `/tmp` confirmed. – Zeno Sep 30 '13 at 02:11

Incorrect key file for table error on outer join logic in MySQL

2 Answers2