Why is Mysqlcheck on 5.1.41 still showing as Innodb table corrupted after a table dump and restore?

Question

I had a very large corrupt innodb table. mysqld would crash whenever I did a check table on it. I did a innodb-force-recovery=1 and did a dump of the table. No errors showed up.

I dropped the table and attempted to restore, it completed fine. I did another mysqlcheck on the table, part way through Mysqd crashed with this error:

Version: '5.1.41-3ubuntu12.10'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306       
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 266102.
InnoDB: You may have to recover from a backup.
120203 23:56:24  InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex bc702d5400040f760004
....
...
120203 23:56:24  InnoDB: Page checksum 1745088809, prior-to-4.0.14-form checksum   2260539380
InnoDB: stored checksum 3161468244, prior-to-4.0.14-form stored checksum 2260539380
InnoDB: Page lsn 230 3433899790, low 4 bytes of lsn at page end 3433899790
InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 266102.

I thought dumping a table successfully then restoring from it should clear the corruption? why is it still corrupted and I cannot restore it?

This is on an Ubuntu 10.04 release mysql 5.1.41.

This is on a VPS host so a memory check would not work. Is there anything else that could be the problem?

Any help would be appreciated.

Answer 1

The error seems to indicate a problem with the primary key on the table mycompany.fact.

InnoDB: Page number (if stored to page already) 266102,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 994
InnoDB: Page may be an index page where index id is 0 4668
InnoDB: (index "PRIMARY" of table "mycompany"."Fact")

You can open the dump file in any text editor and remove the primary key constraint from the CREATE TABLE schema definition. Then once you've restored your database, you can always add the primary key back in. You would also need to deactivate Foreign Key Checks in your backup file.

-- at the beginning of the file
SET foreign_key_checks = 0;

-- at the end of the file
SET foreign_key_checks = 1;

This may get your database restored, but if there is a problem with the data in the column you're using as the primary key, then you may see other errors.

If that didn't work, I would also try

1. Backing up all the databases
2. Stopping MySQL
3. Delete the ibdata and iblog files
4. Starting MySQL again
5. Restore all my databases.

If there is a problem with your InnoDB files, this may be a way to solve it.

The problem could also be deeper and running a fsck on the filesystem that stores your InnoDB files may find corruption on the disk. Rebooting your computer is one way to easily cause an fsck on all partitions.