List:General Discussion« Previous MessageNext Message »
From:Johnny Withers Date:January 14 2011 11:28pm
Subject:Re: Incorrect key file for table
View as plain text  
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ørn Dahl-Stamnes
<sql06@stripped>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 the
> mysqld server was able to fill the disk with 900 Mb in 1-2 seconds). After
> 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=22 AND albums.ACCESS_ID=accesses.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        | NULL
> | 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=albums.ID'. When I fixed this, it
> worked :)
>
> Thanx anyway.
>
> --
> 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=1
>
>


-- 
-----------------------------
Johnny Withers
601.209.4985
johnny@stripped

Thread
Incorrect key file for tableJørn Dahl-Stamnes14 Jan
  • Re: Incorrect key file for tableJohan De Meersman14 Jan
    • Re: Incorrect key file for tableJørn Dahl-Stamnes14 Jan
      • Re: Incorrect key file for tableJohnny Withers15 Jan
        • Re: Incorrect key file for tableJørn Dahl-Stamnes15 Jan
          • Re: Incorrect key file for tableJohan De Meersman15 Jan
          • Re: Incorrect key file for tableMySQL)15 Jan
  • RE: Incorrect key file for tableJerry Schwartz14 Jan