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.

peter
  • 93
  • 13
  • 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 '22 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 '22 at 00:06
  • You have a disk that won't hold 10GB? – Rick James Jul 14 '22 at 01: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 '22 at 01: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 '22 at 05:52

1 Answers1

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.)

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • I drop the primary key and import the tablespace again. Unfortunately, the error persists. – peter Jul 15 '22 at 00: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 '22 at 00:45