Fix MySQL work when innoDB tables break

Hello!

I (maybe, like you) is a website developer, and for me not to lose all my achievements I need SVN. And since I’m not working alone, I’m still at least sharing a common database. A few years ago, we purchased the Synology DS-101 NAS server ( Tom`s Guide or Nix ), set up a storage there, turned on the database (though, MySQL4). He served us faithfully for several years, survived the arrival of drunken electricians (when we were first hooked up to 380V, and then realized it almost lost everything), but then ... a few weeks ago the base did not want to boot. I had to fix it.

All would be nothing if this case did not happen again ...


For the first time, a new firmware was urgently uploaded to the server, allowing access via telnet. Do not forget, Linux is very badly curtailed there - Linux version 2.4.22-uc0 - installation of something is done via telnet, and via SSH the system requires a root, under which it does not allow. After a couple of hours, a problem was discovered - it is the mysqld daemon that does not start. Trying to manually start mysqld, got out the error, it actually was in the logs, as follows:
')
100209 14:04:59 mysqld started
100209 14:05:00 [Warning] Setting lower_case_table_names=2 because file system for /volume1/@database/mysql/ is case insensitive
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
100209 14:05:00 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex (... ~ 16...)InnoDB: End of page dump
100209 14:05:04 InnoDB: Page checksum 2825884538, prior-to-4.0.14-form checksum 2024336829
InnoDB: stored checksum 1598293605, prior-to-4.0.14-form stored checksum 2024336829
InnoDB: Page lsn 0 2531807, low 4 bytes of lsn at page end 2531807
InnoDB: Page number (if stored to page already) 5,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 5.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
100209 14:05:04 mysqld ended

It turns out that I could not start mysqld without fixing the broken tables, and could not fix the tables without starting the daemon.
Climbing on the Internet, there were two ways to solve this problem:
First and Official - dev.mysql.com
The second and less official - mysqlperformanceblog.com

The first solution to the problem involves running the daemon like this:

mysqld --innodb_force_recovery=4

thus, we let the server know that:

Prevent insert buffer merge operations. If they would cause a crash, do not do them. Do not calculate table statistics.
( . , . )

If it is shorter, then to dump the tables and redistribute the disk space under the innodb tables.
With this parameter, the server is started, but the CHECK TABLE shows OK. OPTIMIZE TABLE for innodb-tables is not supported by the engine.

You need to somehow refer to the innodb tables (for example, create an INSERT query) - mysqld will redistribute the space. After redistributing the space, we can start the myslqd daemon in normal mode, i.e. just restarting the NAS server (this is in our case). I can not say that any data is inaccessible - I lost it without errors and problems. Perhaps, in this case, we still lose something - let the specialists correct me.

The second option (from the Mayeskulperformansblog) is to start the daemon with innodb_force_recovery = 1, creating <new_table> with the MyISAM engine and trying to drive all the records from the table with innodb there. Briefly describes it this way:

CREATE TABLE <new_table> LIKE <crashed_table>;
INSERT INTO <new_table> SELECT * FROM <crashed_table>;

And rename it.

If you need to find exactly the rows on which it is chopped, use the multiple inserts method (INSERT IGNORE) in the new table with a limit. There everything is painted in detail.

I hope this little article will help someone in a similar situation, because for the first time it took me a long time to fix it. I had time - you may not have it. I also urge MySQL specialists to join in the discussion of this issue - why innodb is falling.