Getting and incorrect key file error

#1

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it

I'm guessing that this the temp table created by MySQL. Any
suggestions? I rebooted the MySQL server already. I tried simpler
queries, they work fine. I also ran REPAIR TABLE on all these tables.

#2

EastSideDev wrote:

Quote:

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it

I'm guessing that this the temp table created by MySQL. Any
suggestions? I rebooted the MySQL server already. I tried simpler
queries, they work fine. I also ran REPAIR TABLE on all these tables.

Are you running out of disk space where MySQL stores the temp files?

If not, what version nof MySQL are you running?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#3

On Jun 21, 5:23*pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:

Quote:

EastSideDev wrote:
I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it

I'm guessing that this the temp table created by MySQL. Any
suggestions? I rebooted the MySQL server already. I tried simpler
queries, they work fine. I also ran REPAIR TABLE on all these tables.

Are you running out of disk space where MySQL stores the temp files?

If not, what version nof MySQL are you running?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

In response to mysql -V, I get:
mysql Ver 14.12 Distrib 5.0.90, for unknown-linux-gnu (x86_64) using
readline 5.1

I don't know if I'm running out of space for the temp files, but the
server has a 750GB hard drive, and 6GB of RAM, with hardly anything
running. I just checked on available space (from WHM), and here's what
I got (the query is not running):

Device Size Used Available Percent Used Mount Point
/usr/tmpDSK 485M 361M 99M 79% /tmp

#4

EastSideDev wrote:

Quote:

On Jun 21, 5:23 pm, Jerry Stuckle <jstuck... (AT) attglobal (DOT) net> wrote:
EastSideDev wrote:
I am trying to join 4 different tables. Here's the statement:
SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC
I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it
I'm guessing that this the temp table created by MySQL. Any
suggestions? I rebooted the MySQL server already. I tried simpler
queries, they work fine. I also ran REPAIR TABLE on all these tables.
Are you running out of disk space where MySQL stores the temp files?

If not, what version nof MySQL are you running?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstuck... (AT) attglobal (DOT) net
==================

In response to mysql -V, I get:
mysql Ver 14.12 Distrib 5.0.90, for unknown-linux-gnu (x86_64) using
readline 5.1

I don't see any specific problems like this for version 5.0.90, and it's
pretty recent. So I wouldn't be concerned about it at this time. There
are similar bugs for other releases, though.

Quote:

I don't know if I'm running out of space for the temp files, but the
server has a 750GB hard drive, and 6GB of RAM, with hardly anything
running. I just checked on available space (from WHM), and here's what
I got (the query is not running):

Device Size Used Available Percent Used Mount Point
/usr/tmpDSK 485M 361M 99M 79% /tmp

Unfortunately, this doesn't say much. You might require 9K of temp disk
space, you might require 900M. What would be more important is the
results when the query is running.

Also, not being familiar with WHM, I don't know whether this is the
correct directory or not. The one MySQL will use is the tmp= parameter
in your mysql configuration file (usually something like /etc/mysql/my.cnf).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#5

In article <56bb2d96-4e05-4722-80e2-35083c44f7d6 (AT) j4g2000yqh (DOT) googlegroups.com>, EastSideDev <eastsidedev (AT) gmail (DOT) com> wrote:

Quote:

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it

The error message and its cause are irrelevant. Your query is fundamentally
broken: nothing in the WHERE clause establishes any connection between the
events table and the other three.

#6

On 22 June, 01:01, EastSideDev <eastside... (AT) gmail (DOT) com> wrote:

Quote:

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

If you write queries like that you deserve all the problems you are
getting.

If you really think it is acceptable to write programs in such a mess,
then I strongly advise you to take up another career.

#7

On Jun 22, 8:16*am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:

Quote:

On 22 June, 01:01, EastSideDev <eastside... (AT) gmail (DOT) com> wrote:

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

If you write queries like that you deserve all the problems you are
getting.

If you really think it is acceptable to write programs in such a mess,
then I strongly advise you to take up another career.

You must be a real miserable person, in real life. Nothing else
explains your constant vitriol. I suggest you get a life.

#8

On Jun 22, 7:37*am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:

Quote:

In article <56bb2d96-4e05-4722-80e2-35083c44f... (AT) j4g2000yqh (DOT) googlegroups.com>, EastSideDev <eastside... (AT) gmail (DOT) com> wrote:

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it

The error message and its cause are irrelevant. Your query is fundamentally
broken: nothing in the WHERE clause establishes any connection between the
events table and the other three.

The tables are joined at the coID level in the WHERE clause (see my
query). This used to work, until recently.

#9

EastSideDev wrote:

Quote:

On Jun 22, 8:16 am, Captain Paralytic <paul_laut... (AT) yahoo (DOT) com> wrote:
On 22 June, 01:01, EastSideDev <eastside... (AT) gmail (DOT) com> wrote:

I am trying to join 4 different tables. Here's the statement:
SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC
If you write queries like that you deserve all the problems you are
getting.

If you really think it is acceptable to write programs in such a mess,
then I strongly advise you to take up another career.

You must be a real miserable person, in real life. Nothing else
explains your constant vitriol. I suggest you get a life.

Not at all. Paul is a very valuable contributer to this newsgroup.

And he has a very valid point.

Rewrite your query so that it is readable and you will see that Doug is
correct.

Even better, use JOIN to join your tables instead of doing it in the
WHERE clause, and rewrite so that it's more readable and you will see
your problem.

And you never did answer my previous question. Your query is returning
a Cartesian result - which can quite possibly fill up your temp space.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

#10

In article <63e2577c-4045-4fe7-a989-1cf86fa34ce3 (AT) y4g2000yqy (DOT) googlegroups.com>, EastSideDev <eastsidedev (AT) gmail (DOT) com> wrote:

Quote:

On Jun 22, 7:37=A0am, spamb... (AT) milmac (DOT) com (Doug Miller) wrote:
In article <56bb2d96-4e05-4722-80e2-35083c44f... (AT) j4g2000yqh (DOT) googlegroups.=
com>, EastSideDev <eastside... (AT) gmail (DOT) com> wrote:

I am trying to join 4 different tables. Here's the statement:

SELECT profile_image_url, screen_name, url, description, first, last,
coName FROM twitterProfiles, names, companies, events WHERE
twitterProfiles.coID=names.coID AND names.coID=companies.coID AND
events.RSVP='Y' AND events.eventOptOut != 'Y' AND
events.eventID='JulyEvent' ORDER BY followers_count DESC

I am getting the following error message: #126 - Incorrect key file
for table '/tmp/#sql_7ad0_0.MYI'; try to repair it

The error message and its cause are irrelevant. Your query is fundamentally
broken: nothing in the WHERE clause establishes any connection between the
events table and the other three.

The tables are joined at the coID level in the WHERE clause (see my
query).

No, they are not (see your query).

Your WHERE clause joins:
- twitterProfiles to names, via coID
- names to companies, via coID
- and _absolutely nothing_ to events, whether via coID or any other column.

Quote:

This used to work, until recently.

No, it did not. Not as posted, anyway. If some other, earlier version of it
used to work, and it stopped working recently, then the reason it doesn't work
now is whatever change you made recently.