Database page corruption on disk or a failed file read – Mysql
We got stuck into a situation last week and problem seemed unrecoverable. Here I am trying to state the problem and then will describe about the solution which we applied to save us.
We realized in the mid-night, that our server is not functioning and the reason was the main application which is a web application was unable to take connection from DB which is MySql. When we checked the logs we found that data file was somehow corrupted and now Mysql was not able to recover. It was trying to start the server and was failing every time i.e. mysqld was in tight loop of restarting itself.
Our biggest worry was losing the data as it happened mid-night and we did not have backup of data for the whole day of processing.
The final blow: After few minutes, we realized that backup script was not running properly and we did not have data backup of last whole week. This was really critical as we could not afford to lose our data.
091022 21:42:42 InnoDB: Page checksum 4132372618, prior-to-4.0.14-form checksum 4163252346 InnoDB: stored checksum 1471849560, prior-to-4.0.14-form stored checksum 4163252346 InnoDB: Page lsn 1 646987912, low 4 bytes of lsn at page end 646987912 InnoDB: Page number (if stored to page already) 23017, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0 InnoDB: Page may be an index page where index id is 0 194 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 23017. 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 InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html InnoDB: about forcing recovery. InnoDB: Ending processing because of a corrupt database page. Number of processes running now: 0 091022 21:42:42 mysqld restarted 091022 21:42:42 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 091022 21:42:42 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 1 663857676. InnoDB: Doing recovery: scanned up to log sequence number 1 663857676 091022 21:42:42 InnoDB: Started; log sequence number 1 663857676 091022 21:42:42 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.0.45' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 23017. InnoDB: You may have to recover from a backup. 091022 21:42:43 InnoDB: Page dump in ascii and hex (16384 bytes): dump
Things which we tried but failed to recover data:
1. Tried to move data files and tried on another machine/Mysql instance.
2. Tried to change few parameters of Mysql
3. Tried to run mysqlcheck
Finally we got the solution in the log only which we were ignoring, probably because of the situation.
http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
Changed /etc/my.cnf and added following parameter below [mysqld]
[mysqld]
innodb_force_recovery = 1
1 means (SRV_FORCE_IGNORE_CORRUPT)
Let the server run even if it detects a corrupt page. Try to make SELECT * FROM tbl_name jump over corrupt index records and pages, which helps in dumping tables.
This allowed mysqld to start successfully and let us take the dump of data. As soon as we get the data we re-installed mysql and restored the data using backup.
Do remember to take regular backup of your data and keep checking your backup script if it is working fine or not ?
Most Commented Posts
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
[...] from the last time learnings of page file corruption (previous post) we knew how to start the server in recovery mode by changing the /etc/my.cnf file, we started it [...]