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
