Mysql – InnoDB “corruption”

corruptioninnodbMySQL

I use:

5.1.63-0ubuntu0.11.10.1
Apache/2.2.20 (Ubuntu)
MySQL client version: 5.1.63

The system is continuously displaying the following error during reading data from a MySQL database:

InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 387794.

After rebooting the system, I can regain access to the database and read
the "problematic" data without any problem for a few minutes or hours until
the same problem re-appears (for different data read attempts).

Any idea on the source of the problem?

I have checked the HDD with S.M.A.R.T utility and I could not find any problem.
I deactivate AppArmor without any result.
I also tried SQL_NO_CACHE option in the query without any result

120918 13:07:06  InnoDB: Page checksum 3504102879, prior-to-4.0.14-form checksum 1216630776
InnoDB: stored checksum 2100325079, prior-to-4.0.14-form stored checksum 1216630776
InnoDB: Page lsn 2 1168743501, low 4 bytes of lsn at page end 1168743501
InnoDB: Page number (if stored to page already) 387794,
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 175
InnoDB: (index "PRIMARY" of table "OII"."dataset_cs_1")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 387794.
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 http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
120918 13:07:07 [Note] Plugin 'FEDERATED' is disabled.
120918 13:07:07  InnoDB: Initializing buffer pool, size = 64.0M
120918 13:07:07  InnoDB: Completed initialization of buffer pool
InnoDB: Log scan progressed past the checkpoint lsn 2 2380293142
120918 13:07:07  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...
InnoDB: Doing recovery: scanned up to log sequence number 2 2380293162
120918 13:07:07  InnoDB: Started; log sequence number 2 2380293162
120918 13:07:07 [Note] Event Scheduler: Loaded 0 events
120918 13:07:07 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.63-0ubuntu0.11.10.1'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)

Best Answer

Rebooting temporarily clears the problem, and the error moves around... this strongly points to defective system RAM causing corruption of the OS cache.

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.

You likely already know this part, but with Ubuntu, on startup, select "Memory test (memtest86+)" to verify. If you don't normally see the GRUB boot menu, you should be able to hold left-shift as it first starts up.

Related Solutions

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

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

Backing up all the databases
Stopping MySQL
Delete the ibdata and iblog files
Starting MySQL again
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.

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

It is sort of yes and no. Why would I say both?

There are still some transactional data embedded in the old ib_logfiles in conjunction with the ibdata1 file. (See pictorial representation)

What you should have done is this:

mysql -u... -p... -ANe"SET GLOBAL innodb_fast_shutdown = 0"
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

If you did not know of disabling innodb_fast_shutdown, ther you steps should have been

/etc/init.d/mysql.server stop
/etc/init.d/mysql.server start
/etc/init.d/mysql.server stop
mv ib_logfile0 ib_logfile0.OLD
mv ib_logfile0 ib_logfile0.OLD
/etc/init.d/mysql.server start

Doing either of these coordinate the purging of all uncommitted writes to tables and indexes. Just looking at the error log, I think you dodged a bullet in this instance. In future, please follow this protocol.

If you are not sure either way, you could put back the old log files, set the old size of innodb_log_file_size, restart mysql. Then, start the process over again as I prescribed above.

Best Answer

Related Solutions

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

MySQL InnoDB logfiles resized – review thesql-err.log for potential problems

Related Question