0

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:

  1. Create a table like this: my_table2.
  2. Use mysqldump to dump my_table data in a file.
  3. Replace the create and the inserts to be done in "my_table2".
  4. Execute the file. my_table2 is created and each row in my_table exists in my_table2.
  5. 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.

8
  • 1
    You probably don't have enough free disk space to perform the ALTER TABLE. It makes a copy of the tablespace while the alter is running, so you need a lot of free disk space, as much as the size of the defragmented table. If you can't remove or relocate some files, or expand the disk volume, then you can't do this operation on this server anymore. – Bill Karwin Jan 28 at 14:59
  • @bill-karwin, thanks for your comment. How should I check your advice? Should I review the "tmpdir" variable in the DB server, and check its space in the OS filesystem? – yaki_nuka Jan 28 at 15:24
  • 1
    The copy table created by ALTER TABLE is created in the schema directory, adjacent to the original table. It does not use the location of tmpdir for such tables, because that might be on a different disk volume, and it needs the table on the same volume as the original table to finish the ALTER. So you should check the free disk space of your main datadir disk volume, and compare that to the size of the original table. – Bill Karwin Jan 28 at 15:28
  • @BillKarwin thank you for your comment. Executing "SHOW VARIABLES" in the server, my datadir variable is "/app/mysql/data". Executing "df -h" in the OS, shows that FS has 500GB of freespace. The table size is 16GB. Other table, with a size of 18GB, has done the ALTER TABLE ok. – yaki_nuka Jan 29 at 5:36
  • Ok, it could be that disk space is briefly exhausted because of queries that run once in a while and create temporary tables. I have seen this happen. The server gave errors about disk space but when the admin looked, it had plenty of space. By helping them look through their query logs, I found that they had multiple queries running at the same time, each creating 10GB temp tables. But a temp table would naturally be dropped as soon as those queries finish. So the disk looked like it had enough space 99% of the time. – Bill Karwin Jan 29 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.