0

The search query below is failing and results in a "Incorrect Key file for table '/tmp/....'; try to repair it" SQL error.

SELECT DISTINCT(t.entry_id), t.entry_id, t.channel_id, t.forum_topic_id, t.author_id, t.ip_address, t.title, t.url_title, t.status, t.view_count_one, t.view_count_two, t.view_count_three, t.view_count_four, t.allow_comments, t.comment_expiration_date, t.sticky, t.entry_date, t.year, t.month, t.day, t.entry_date, t.edit_date, t.expiration_date, t.recent_comment_date, t.comment_total, t.site_id as entry_site_id, w.channel_title, w.channel_name, w.search_results_url, w.search_excerpt, w.channel_url, w.comment_url, w.comment_moderate, w.channel_html_formatting, w.channel_allow_img_urls, w.channel_auto_link_urls, w.comment_system_enabled, m.username, m.email, m.url, m.screen_name, m.location, m.occupation, m.interests, m.aol_im, m.yahoo_im, m.msn_im, m.icq, m.signature, m.sig_img_filename, m.sig_img_width, m.sig_img_height, m.avatar_filename, m.avatar_width, m.avatar_height, m.photo_filename, m.photo_width, m.photo_height, m.group_id, m.member_id, m.bday_d, m.bday_m, m.bday_y, m.bio, md., wd. FROM exp_channel_titles AS t LEFT JOIN exp_channels AS w ON t.channel_id = w.channel_id LEFT JOIN exp_channel_data AS wd ON t.entry_id = wd.entry_id LEFT JOIN exp_members AS m ON m.member_id = t.author_id LEFT JOIN exp_member_data AS md ON md.member_id = m.member_id WHERE t.entry_id IN (4,20,38,42,94,104,106,118,134) ORDER BY entry_date desc LIMIT 0, 8

We believe that the exp_channel_data table and its 1371 columns is at the source of the issue . When we remove it from the query (by removing wd.* before the FROM) everything works as expected. We've read multiple stackoverflow posts dealing with "Incorrect Key File" issues in SQL and have noted that the most common response was to check that /tmp wasn't running out of space, but our /tmp directory has 15GB free. We've come across a forum post on your website that may relate to what we are experiencing; https://ellislab.com/forums/viewthread/61159/#301157.

  • What version of EE is this? When does the SQL get run? What's your template code that triggers this? Odd that's it's referring to "wd" and "w" instead of cd and c. Has the /tmp folder got correct ownership and permissions? Quick look and different answers and it does sound like server setup: serverfault.com/questions/336736/incorrect-key-file-mysql-error – Peter Lewis Apr 6 '15 at 9:28
  • The error is indicating that the necessary temp table did not get created correctly and the most likely cause for that to happen is that there was not enough space in the tmp directory to create the file, however that is not the case in this instance for several reasons. The root partition where the temp directory of the mysql server resides has 15GB free and I do not see a way you could fill that up working on a database that is only 2.1 MB in size also the mysql server I copied the database to for testing has 80GB free. – Gourav Anvekar Apr 13 '15 at 1:26
  • I have seen the tmp partition run out of space while working with large tables (10GB) and you can watch the temp files grow in size until they fill the drive space but that is not the case here the temp files never get above 64K -rw-rw----. 1 mysql mysql 0 Apr 7 15:19 #sql_7f9_0.MAD -rw-rw----. 1 mysql mysql 64K Apr 7 15:19 #sql_7f9_0.MAI – Gourav Anvekar Apr 13 '15 at 1:27
0

Removing the DISTINCT from the above query allows the query to be executed without throwing an error, this is because the DISTINCT in combination with the ORDER BY causes mysql to build a temp table which cannot be created because the temp table (which is the entire exp_channel_table combined with the entire exp_member_data plus all off the other columns listed) exceeds the maximum row length of mysql.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.