I’m using MySQL 5.7.10
I have a table like this, with 100M rows and a size of 16GB .
CREATE TABLE `my_table` ( `id` DOUBLE NOT NULL AUTO_INCREMENT, `entity_id` DOUBLE NOT NULL, `concept_id` VARCHAR(50) NOT NULL COLLATE 'utf8_spanish_ci', `value` DOUBLE(15,6) NOT NULL, `increment` DOUBLE(10,6) NULL DEFAULT NULL, PRIMARY KEY (`id`), INDEX `IDX_concept` (`concept_id`), INDEX `IDX_entity` (`entity_id`) ) COLLATE='utf8_general_ci' ENGINE=InnoDB ROW_FORMAT=DYNAMIC AUTO_INCREMENT=118166425
Once a month, I execute:
ALTER TABLE my_table ENGINE=InnoDB;
My intention is to defrag the table, so the data are put together and the size keeps as low as possible.
This time, it failed and the failure message is: “Incorrect key file for table ‘my_table’; try to repair it”.
I have made the following steps:
- Create a table like this: my_table2.
- Use mysqldump to dump my_table data in a file.
- Replace the create and the inserts to be done in “my_table2”.
- Execute the file. my_table2 is created and each row in my_table exists in my_table2.
- Execute ALTER TABLE my_table2 ENGINE=InnoDB;
And it failed too, with the same message “Incorrect key file for table ‘my_table2’; try to repair it”.
How could I fix the error? Thank you.
EDIT 1: I have executed CHECK TABLE for both tables, and the result is status OK, for both of them.