MySQL Bugs: #10075: "Incorrect key file for table ..." error with large query

Bug #10075	"Incorrect key file for table ..." error with large query
Submitted:	21 Apr 2005 21:29	Modified:	5 Oct 2005 15:39
Reporter:	[ name withheld ] (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.4	OS:	Windows (Windows 2000)
Assigned to:	Alexey Botchkov	CPU Architecture:	Any

Description:
Running a large spatial query I get a "Incorrect key file for table 'C:\WIN2000\TEMP\#sql_398_0.MYI'; try to repair it" error.

How to repeat:
The behaviour can be reproduced as:
1. Download zip codes file at
http://spatialnews.geocomm.com/newsletter/2000/jan/cenzuszipcodes.zip

2. Create staging table:
CREATE TABLE `ST_ZIP_CODES` (
  `STATE_FIPS_CODE` VARCHAR(50),
  `ZIP_CODE` VARCHAR(5),
  `STATE_ABBREVIATION` VARCHAR(45),
  `ZIP_CODE_NAME` VARCHAR(45),
  `LONGITUDE` FLOAT,
  `LATITUDE` FLOAT,
  `1990_POPULATION` INTEGER,
  `ALLOCATION_FACTOR` FLOAT
)
TYPE = InnoDB;

3. Load it with data coming from the file

load data local infile 'c:/zips.txt'
replace
into table st_zip_codes
fields terminated by ','
optionally enclosed  by '"'
lines
terminated by '\n'

4. Create final destination table:

create table ZIP_CODES (
STATE_FIPS_CODE CHAR(2),
ZIP_CODE CHAR(5),
STATE_ABBREVIATION CHAR(2),
ZIP_CODE_NAME VARCHAR(50),
ZIP_LOC POINT NOT NULL,
PRIMARY KEY (ZIP_CODE),
SPATIAL KEY (ZIP_LOC)
)
TYPE = MyISAM;

ALTER TABLE `test`.`zip_codes`
, ADD COLUMN `1990_POPULATION` INTEGER UNSIGNED AFTER `ZIP_LOC`
, ADD COLUMN `ALLOCATION_FACTOR` FLOAT AFTER `1990_POPULATION`;

5. Load it with an insert .. select 

INSERT INTO
zip_codes
(
STATE_FIPS_CODE,
ZIP_CODE,
STATE_ABBREVIATION,
ZIP_CODE_NAME,
ZIP_LOC,
1990_POPULATION,
ALLOCATION_FACTOR
)
SELECT
STATE_FIPS_CODE,
ZIP_CODE,
STATE_ABBREVIATION,
ZIP_CODE_NAME,
GeomFromText(CONCAT('POINT(', LONGITUDE ,' ', LATITUDE, ')')),
1990_POPULATION,
ALLOCATION_FACTOR FROM ST_ZIP_CODES

5. Run this select:

SELECT
  zc.zip_code_name,
  c.zip_code_name,
  ROUND(GLength(LineStringFromWKB(LineString(AsBinary(c.zip_loc), AsBinary(zc.zip_loc)))))
    AS distance
FROM zip_codes c, zip_codes zc
-- where zc.zip_code = 35004
ORDER BY distance ASC LIMIT 1;

You'll get this error:

Incorrect key file for table 'C:\WIN2000\TEMP\#sql_398_0.MYI'; try to repair it

Thanks for your bug report !

>  FROM zip_codes c, zip_codes zc
> -- where zc.zip_code = 35004
> ORDER BY distance ASC LIMIT 1;

Did you really want to comment out the WHERE clause? 

What's basically happening here is that you've created a self-join cartesian product, and because of the ORDER BY clause, the server has to materialize the entire result into a temporary table before applying the "LIMIT 1".

Your server is running out of diskspace where MySQL stores temporary tables, see http://dev.mysql.com/doc/mysql/en/temporary-files.html for information on how to configure the server to use a location with more space.