Here is a description of strange behavior. I have looked in the manual and in the mailing lists and noted that a similar issue (of corrupted key files) has been discussed for earlier versions of Mysql but no solution was posted. So I hope I am correct that this is a bug (otherwise my apology for posting this)!

Description: I get the following error:

General error: Incorrect key file for table: 'XSP_NODE'. Try to repair it

This occurs in the following scenario: 1- I create a table using the following script: CREATE TABLE XSP_NODE( DOCUMENT_ID INT NOT NULL, ORDINAL INT NOT NULL, NODE_SCHEMA_ID INT NOT NULL, STRING_DATA VARCHAR(255), NUMERIC_DATA NUMERIC(28,9), IDREF_DATA INT, SCHEMAREF_DATA INT, BINARY_DATA INT, PRIMARY KEY (DOCUMENT_ID,ORDINAL), INDEX IDX_XSP_NODE_3 (NUMERIC_DATA,NODE_SCHEMA_ID), INDEX IDX_XSP_NODE_4 (IDREF_DATA,NODE_SCHEMA_ID), INDEX IDX_XSP_NODE_5 (STRING_DATA(255),NODE_SCHEMA_ID))

2- I insert a few thousand records in the table (in one batch after eachother) 3- Then in the same batch program I try to delete a number of records with:

DELETE FROM XSP_NODE WHERE DOCUMENT_ID=6 4- Then the above error is thrown

I have tried different scenarios and the error does not occur when I change the last Index (IDX_XSP_NODE_5) to (note the change of the length of the part of the string_data field from 255 to 254): INDEX IDX_XSP_NODE_5 (STRING_DATA(254),NODE_SCHEMA_ID))

If the error is caused by the constraint on the index length (max. 256 bytes) then the error message should be more descriptive (or better, it should not even be possible to specify an index of >256 bytes).

Note that I have also tried the above scenario with the following table definition: CREATE TABLE XSP_NODE( DOCUMENT_ID INT NOT NULL, ORDINAL INT NOT NULL, NODE_SCHEMA_ID INT NOT NULL, STRING_DATA VARCHAR(255), NUMERIC_DATA NUMERIC(28,9), IDREF_DATA INT, SCHEMAREF_DATA INT, BINARY_DATA INT, PRIMARY KEY (DOCUMENT_ID,ORDINAL), INDEX IDX_XSP_NODE_3 (NUMERIC_DATA,NODE_SCHEMA_ID), INDEX IDX_XSP_NODE_4 (IDREF_DATA,NODE_SCHEMA_ID), INDEX IDX_XSP_NODE_5 (NODE_SCHEMA_ID), INDEX IDX_XSP_NODE_6 (STRING_DATA(255)))

Here the last index is not larger than 256 bytes (or do I miss something here?), and still I get the same error as above. If I change the last index to: INDEX IDX_XSP_NODE_6 (STRING_DATA(254)) Then the error does not occur.

How-To-Repeat: See the scenario above

Fix: I can work around this problem by using only the first 254 characters.

I tried to attach the output from mysqladmin variables extended-status processlist but the mysql email daemon does not accept messages > 30Kb.

Running mysqldump --no-data for the table above gives the following error: can't get create table for table 'xsp_node' (can't open file: 'xsp_node.MYD'. (errno: 145))