Comments - InnoDB recovery under Maria

10 years, 7 months ago Kristian Nielsen

It sounds like your real issue here is that InnoDB recovery fails?

InnoDB recovery should always work, even if mysqld or server crashes.

Make sure you have hard drive caches configured correctly so that when the drive tells the kernel that a write is done, the data is really on the disk and not just in internal disk cache. InnoDB recovery is dependent on this.

10 years, 7 months ago Roger Irwin

This is typical of the log I get:

(Note this is from the orig MySql server)

120810 9:09:05 [Note] Plugin 'FEDERATED' is disabled. 120810 9:09:05 InnoDB: The InnoDB memory heap is disabled 120810 9:09:05 InnoDB: Mutexes and rw_locks use Windows interlocked functions 120810 9:09:05 InnoDB: Compressed tables use zlib 1.2.3 120810 9:09:12 InnoDB: Initializing buffer pool, size = 28.0M 120810 9:09:12 InnoDB: Completed initialization of buffer pool 120810 9:09:13 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 35380397 120810 9:09:13 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: Warning: database page corruption or a failed InnoDB: file read of space 0 page 60. InnoDB: Trying to recover it from the doublewrite buffer. InnoDB: Recovered the page from the doublewrite buffer. InnoDB: Doing recovery: scanned up to log sequence number 39774208 InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 198. InnoDB: You may have to recover from a backup. 120810 9:09:22 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex

<-----snip hex codes----->

InnoDB: End of page dump 120810 9:09:22 InnoDB: Page checksum 3727053759, prior-to-4.0.14-form checksum 3652553842 InnoDB: stored checksum 3727053759, prior-to-4.0.14-form stored checksum 1227594201 InnoDB: Page lsn 0 35404338, low 4 bytes of lsn at page end 38009135 InnoDB: Page number (if stored to page already) 198, InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0 InnoDB: Page may be a system page InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 198. 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: about forcing recovery.

Note that this is all running on a VirtualBox virtual machine with virtual disk. This is the production configuration, not just for testing.

Now to recover I restart with recover level =4, dump the tables, delete the innodb files, restart with recover = 0 and restore the dump.

One of my options would be to write a program which detects the situation and does the above procedure automatically!

However I am trying to achieve a better long term solution with Maria.

10 years, 7 months ago Kristian Nielsen

I would still say the correct fix here is to avoid the InnoDB corruption in the first place, not try to work around it.

This kind of thing should not have to happen no matter how systems crash. But you need to configure your setup (virtualisation software + host disk system) so that when InnoDB does fsync(), it really gets to the disk and does not lie to InnoDB about it. If you've done that and still get this, it would indicate a bug in InnoDB I think that should be fixed...

10 years, 7 months ago Roger Irwin

I think you are right here.

Virtual box does not do a real flush by default. I will do some testing on this next week.


Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.