0

After upgrading mysql from 5.5 to 5.7, a compressed innodb database seems corrupted. I followed the method in this post to create a new database/table, drop the tablespace, copy the old .ibd file, then import the tablespace with ALTER TABLE mytable IMPORT TABLESPACE but got the same error as in that post:

ERROR 1034 (HY000): Incorrect key file for table 'mytable'; try to repair it

Note that this error is not caused by insufficient disk space.

Improve this question
5
  • Please provide your rationale for using "compressed". I may attempt to convince you to abandon that little-used feature of InnoDB.
    – Rick James
    Jul 13, 2022 at 17:56
  • @RickJames I compress the database to save disk space because it is too large(over 10GB) and increasing every day.
    – peter
    Jul 14, 2022 at 0:06
  • You have a disk that won't hold 10GB?
    – Rick James
    Jul 14, 2022 at 1:01
  • Please provide SHOW CREATE TABLE; I would like to see what column(s) could benefit from compression. And I may have other tips (such as avoiding BIGINT).
    – Rick James
    Jul 14, 2022 at 1:02
  • @RickJames, CREATE TABLE mytable ( title varchar(700) NOT NULL DEFAULT '', content mediumtext NOT NULL, ctime datetime NOT NULL, PRIMARY KEY (title(255)) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
    – peter
    Jul 14, 2022 at 5:52

1 Answer 1

Reset to default
1 
PRIMARY KEY (title(255))

Ouch! Technically that says that the first 255 characters of title are unique. This is probably not what you wanted. I think that prefixing UNIQUE and PRIMARY KEY should be disallowed.

I guess that the prefixing is what is causing the problem.

5.7 will let you remove the (255) and (presumably) work correctly. Try that. (5.5 had index limitations that led to the prefixing.)

Improve this answer
2
  • I drop the primary key and import the tablespace again. Unfortunately, the error persists.
    – peter
    Jul 15, 2022 at 0:31
  • Interestingly, when importing tablespace, mysql log shows this error: [ERROR] InnoDB: Page offset doesn't match file offset: page offset: 526288, file offset: 526288 [Note] InnoDB: Discarding tablespace of table mydatabase.mytable: Data structure corruption, but page offset=file offset obviously.
    – peter
    Jul 15, 2022 at 0:45

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .