I've met with the strange trouble with one InnoDB table.

Index creation on any column fails with error:

Incorrect key file for table 'table', try to repair it

Despite this error, this table is queryable and there's enough space on disks and RAM.

What reasons can cause this trouble?


Name some_datamart
Engine InnoDB
Version 10
Row_format Compact
Rows 364738
Avg_row_length 182
Data_length 66699264
Max_data_length 0
Index_length 0
Data_free 5242880
Auto_increment null
Update_time null
Check_time null
Collation utf8_general_ci
Checksum null
Create_options no options

  • Can you run SHOW TABLE STATUS like 'table' and SHOW CREATE TABLE `table`? That doesn't look like an InnoDB table to me, but MyISAM corruption. – jynus Jun 13 '18 at 13:40
  • @jynus Added status output, and trouble is about InnoDB table. – Nikolay Matkheev Jun 13 '18 at 13:54
  • C:\Windows\TEMP\#sqlib48.MYI is a temporary MyISAM Index file, so nothing to do (at least directly) with your real table. It could have been created by a query, though, as temporary tables on disk will be created on MyISAM format on your MySQL version. If InnoDB had corruption, the server would crash directly. Innodb stores indexes either on the common tablespaces or on a .ibd file, so there is not such a thing as a key file for InnoDB. – jynus Jun 13 '18 at 13:56
  • @jynus I thought about corruption, however database seems to be alive. Except the fact that I can't create index on a particular table – Nikolay Matkheev Jun 13 '18 at 13:59
  • 1
    @jynus you should post your coimments as an answer. – RolandoMySQLDBA Jun 16 '18 at 2:53

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.