ERROR 126 (HY000): Incorrect key file for table

[James Cobban]

I am getting "ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_56c4_1.MYI'; try to repair it" on an update. Since the table in question is obviously a temporary table created by MySQL as part of the process of implementing my request, it does not exist outside of the update itself, and so it seems to me that there is nothing I could do to repair it.

I am representing civil marriage registrations as a pair of tables. The table "Marriage" contains the description of the marriage, while a pair of records in "MarriageIndi" record information about the bride and groom.

CREATE TABLE MarriageIndi
	(M_RegDomain		VARCHAR(4)	# administrative domain
		NOT NULL DEFAULT "CAON",	# usually 2-char country code + 2-char state code
	 M_RegYear		INTEGER NOT NULL,
	 M_RegNum		INTEGER NOT NULL,
	 M_Role			CHAR(1),	# 'G'	Groom
						# 'B'	Bride
						# 'M'	Minister
		PRIMARY KEY(M_RegDomain, M_RegYear, M_RegNum, M_Role),
	 M_Surname		VARCHAR(32),
		INDEX (M_Surname),
	 M_SurnameSoundex 	CHAR(4),
		INDEX (M_SurnameSoundex),
	 M_GivenNames		VARCHAR(48),
		INDEX (M_GivenNames),
	 M_Age			INTEGER,
	 M_BYear		INTEGER,
	 M_Residence		VARCHAR(32),
	 M_BirthPlace		VARCHAR(32),
	 M_MarStat		VARCHAR(3),	# 'B'	Bachelor
						# 'S'	Spinster
						# 'W'	Widow[er]
	 M_Occupation		VARCHAR(32),
	 M_FatherName		VARCHAR(48),
	 M_MotherName		VARCHAR(48),
	 M_Religion		VARCHAR(32),
	 M_WitnessName		VARCHAR(48),
	 M_WitnessRes		VARCHAR(32),
	 M_Remarks		VARCHAR(48) );
CREATE TABLE Marriage
	(M_RegDomain		VARCHAR(4)	# administrative domain
		NOT NULL DEFAULT "CAON",	# usually 2-char country code + 2-char state code
	 M_RegYear		INTEGER NOT NULL,
	 M_RegNum		INTEGER NOT NULL,
		PRIMARY KEY(M_RegDomain, M_RegYear, M_RegNum),
	 M_MsVol		VARCHAR(5),
	 M_RegCounty		CHAR(3),
	 M_RegTownship		VARCHAR(16),
	 M_Date			VARCHAR(16),
	 M_Place		VARCHAR(64),
	 M_LicenseType		CHAR(1),
	 M_Remarks		VARCHAR(48) );

This pair of tables is intended to be maintained through a web interface at http://www.jamescobban.net/Ontario/MarriageRegQuery.html. There are a few thousand sample records up there, so if you like you can do a search and see the database in action. At this point I am trying to load the full database from existing spreadsheets. However the structure of the spreadsheets is simplified in that there is only one table with three records, where the fields in the third record have different meanings from the same records for the bride and groom. So in the migration I first load the MarriageIndi table with the temporary 3 records per entry, and then issue a REPLACE statement to create the contents of the Marriage table from the third record in the MarriageIndi table.

REPLACE INTO Marriage (M_RegDomain, M_RegYear, M_RegNum,
	 M_MsVol,
	 M_RegCounty,
	 M_RegTownship,
	 M_Date	,
	 M_Place,
	 M_LicenseType,
	 M_Remarks)
	SELECT
	 MarriageIndi.M_RegDomain, 
	 MarriageIndi.M_RegYear, 
	 MarriageIndi.M_RegNum,
	 MarriageIndi.M_MarStat,
	 MarriageIndi.M_Occupation,
	 MarriageIndi.M_FatherName,
	 MarriageIndi.M_Religion,
	 MarriageIndi.M_WitnessName,
	 MarriageIndi.M_WitnessRes,
	 MarriageIndi.M_Remarks
	FROM Marriage, MarriageIndi
	WHERE MarriageIndi.M_Role = 'M';

This command sits there for many minutes and then fails with the above error.