Incorrect key file for table

Hello,

While doing a select query I got the following error in the error-log file:

Incorrect key feil for table '/tmp/#sql_5f8_0.MYI'; try to repair it

It seem rather meaningless to try to repair a temporary table... So is this=
a=20
bug in MySQL, or?

The database I'm using has only InnoDb tables and I'm using version=20
5.1.42-log.
The query is build using Propel (from a web-application made by Symfony=20
framework).

=2D-=20
J=F8rn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Incorrect key file for table

--0015174c43c0aa86ae0499ce0f82
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Check your free diskspace on your temp location.

On Fri, Jan 14, 2011 at 1:31 PM, J=F8rn Dahl-Stamnes
wrote:

> Hello,
>
> While doing a select query I got the following error in the error-log
> file:
>
> Incorrect key feil for table '/tmp/#sql_5f8_0.MYI'; try to repair it
>
> It seem rather meaningless to try to repair a temporary table... So is th=
is
> a
> bug in MySQL, or?
>
> The database I'm using has only InnoDb tables and I'm using version
> 5.1.42-log.
> The query is build using Propel (from a web-application made by Symfony
> framework).
>
> --
> J=F8rn Dahl-Stamnes
> homepage: http://www.dahl-stamnes.net/dahls/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dvegivamp@tuxera.b=
e
>
>


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0015174c43c0aa86ae0499ce0f82--

RE: Incorrect key file for table

>-----Original Message-----
>From: Jørn Dahl-Stamnes [mailto:sql06@dahl-stamnes.net]
>Sent: Friday, January 14, 2011 7:31 AM
>To: mysql@lists.mysql.com
>Subject: Incorrect key file for table
>
>Hello,
>
>While doing a select query I got the following error in the error-log file:
>
> Incorrect key feil for table '/tmp/#sql_5f8_0.MYI'; try to repair it
>
>It seem rather meaningless to try to repair a temporary table... So is this a
>bug in MySQL, or?
>
[JS] Check for space in /tmp. I've seen temporary files that are 10x the size
of the actual table(s).

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>The database I'm using has only InnoDb tables and I'm using version
>5.1.42-log.
>The query is build using Propel (from a web-application made by Symfony
>framework).
>
>--
>Jørn Dahl-Stamnes
>homepage: http://www.dahl-stamnes.net/dahls/
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=jerry@gii.co.jp





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Incorrect key file for table

On Friday 14 January 2011 14:03, Johan De Meersman wrote:
> Check your free diskspace on your temp location.

About 900+ Mb free.

But I don't think that a full filesystem was the problem (I don't think the=
=20
mysqld server was able to fill the disk with 900 Mb in 1-2 seconds). After=
=20
some debugging I found that it was an error in the SQL statment:

mysql> describe SELECT images.* FROM images, albums, accesses WHERE=20
images.IMAGE_CATEGORY_ID=3D22 AND albums.ACCESS_ID=3Daccesses.ID;
+----+-------------+----------+-------+---------------+----- --------+------=
=2D--+-----------------------+------+----------------------- ---------+
| id | select_type | table | type | possible_keys | key | key_l=
en=20
| ref | rows | Extra |
+----+-------------+----------+-------+---------------+----- --------+------=
=2D--+-----------------------+------+----------------------- ---------+
| 1 | SIMPLE | accesses | index | PRIMARY | PRIMARY | 4 =
=20
| NULL | 3 | Using index |
| 1 | SIMPLE | albums | ref | albums_FI_4 | albums_FI_4 | 4 =
=20
| photo_dev.accesses.id | 68 | Using index |
| 1 | SIMPLE | images | ALL | images_FI_2 | NULL | NULL =
=20
| NULL | 9712 | Using where; Using join buffer |
+----+-------------+----------+-------+---------------+----- --------+------=
=2D--+-----------------------+------+----------------------- ---------+

A join was missing. Strange that this passed the syntax check because the=20
select statment does not make sense (in the application).

It should containt a "AND images.ALBUM_ID=3Dalbums.ID'. When I fixed this, =
it=20
worked :)

Thanx anyway.

=2D-=20
J=F8rn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Incorrect key file for table

--0016367faaa17ca0c10499d6cbd2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

The result of your query without the join
probably exceeded your tmp_table_size variable. When this
occurs, MySQL quit writing the temp table to disk thus producing an
incorrect table file. (I think).

JW


On Fri, Jan 14, 2011 at 3:48 PM, J=F8rn Dahl-Stamnes
wrote:

> On Friday 14 January 2011 14:03, Johan De Meersman wrote:
> > Check your free diskspace on your temp location.
>
> About 900+ Mb free.
>
> But I don't think that a full filesystem was the problem (I don't think t=
he
> mysqld server was able to fill the disk with 900 Mb in 1-2 seconds). Afte=
r
> some debugging I found that it was an error in the SQL statment:
>
> mysql> describe SELECT images.* FROM images, albums, accesses WHERE
> images.IMAGE_CATEGORY_ID=3D22 AND albums.ACCESS_ID=3Daccesses.ID;
>
> +----+-------------+----------+-------+---------------+----- --------+----=
-----+-----------------------+------+----------------------- ---------+
> | id | select_type | table | type | possible_keys | key |
> key_len
> | ref | rows | Extra |
>
> +----+-------------+----------+-------+---------------+----- --------+----=
-----+-----------------------+------+----------------------- ---------+
> | 1 | SIMPLE | accesses | index | PRIMARY | PRIMARY | 4
> | NULL | 3 | Using index |
> | 1 | SIMPLE | albums | ref | albums_FI_4 | albums_FI_4 | 4
> | photo_dev.accesses.id | 68 | Using index |
> | 1 | SIMPLE | images | ALL | images_FI_2 | NULL | NUL=
L
> | NULL | 9712 | Using where; Using join buffer |
>
> +----+-------------+----------+-------+---------------+----- --------+----=
-----+-----------------------+------+----------------------- ---------+
>
> A join was missing. Strange that this passed the syntax check because the
> select statment does not make sense (in the application).
>
> It should containt a "AND images.ALBUM_ID=3Dalbums.ID'. When I fixed this=
, it
> worked :)
>
> Thanx anyway.
>
> --
> J=F8rn Dahl-Stamnes
> homepage: http://www.dahl-stamnes.net/dahls/
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Djohnny@pixelated.=
net
>
>


--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--0016367faaa17ca0c10499d6cbd2--

Re: Incorrect key file for table

On Saturday 15 January 2011 00:28, Johnny Withers wrote:
> The result of your query without the join
> probably exceeded your tmp_table_size variable. When this
> occurs, MySQL quit writing the temp table to disk thus producing an
> incorrect table file. (I think).

Yes, part of this was my fault (the missing join) but I still wonder why th=
e=20
server processed the query. Would it not be more appropriate with an error=
=20
message saying that this query contain an error?

=2D-=20
J=F8rn Dahl-Stamnes
homepage: http://www.dahl-stamnes.net/dahls/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Incorrect key file for table

--0015174c16e62280fb0499e34fe4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

On Sat, Jan 15, 2011 at 8:07 AM, J=F8rn Dahl-Stamnes
wrote:

> On Saturday 15 January 2011 00:28, Johnny Withers wrote:
> > The result of your query without the join
> > probably exceeded your tmp_table_size variable. When this
> > occurs, MySQL quit writing the temp table to disk thus producing an
> > incorrect table file. (I think).
>
> Yes, part of this was my fault (the missing join) but I still wonder why
> the
> server processed the query. Would it not be more appropriate with an erro=
r
> message saying that this query contain an error?
>

Most likely (too lazy to actually check :-p ) your SQL was syntacticlaly
correct, so it tried to answer the question you asked. Which, unfortunately=
,
was not the question you wanted to ask :-)


--=20
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0015174c16e62280fb0499e34fe4--

Re: Incorrect key file for table

On 1/15/2011 02:07, Jørn Dahl-Stamnes wrote:
> On Saturday 15 January 2011 00:28, Johnny Withers wrote:
>> The result of your query without the join
>> probably exceeded your tmp_table_size variable. When this
>> occurs, MySQL quit writing the temp table to disk thus producing an
>> incorrect table file. (I think).
>
> Yes, part of this was my fault (the missing join) but I still wonder why the
> server processed the query. Would it not be more appropriate with an error
> message saying that this query contain an error?
>

The error was only logical. The SQL syntax was fine. The MySQL server
has no other way to gauge the accuracy of what you intended the query to
be.

for example, this is perfectly legal SQL:

SELECT * FROM table1, table2;

Even if you had used explicit JOIN operators, the use of an ON clause is
still optional. Here is my same example rewritten to use the JOIN operator.

SELECT * FROM table1 INNER JOIN table2;

Again, this is a perfectly legal statement, even if it may not make
logical sense in the context of your application or data to leave out
the joining criteria.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org