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 <sq...@dahl-stamnes.net>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=joh...@pixelated.net > > -- ----------------------------- Johnny Withers 601.209.4985 joh...@pixelated.net
