MySQL Forums
Forum List  »  General

Major problem with InnoDB, having to use innodb_force_recovery
Posted by: Jason Carlton
Date: November 22, 2021 01:25PM

I've been having a major problem ever since updating to the latest version of MySQL, I'm hoping you all can help.

I moved to a new server and updated everything in December 2020. Everything was good until May, when I just woke up to an inexplicable crash! MySQL was totally unresponsive. My server provider worked on it all day, and finally got it to come back online by adding this to my.cnf:

innodb_force_recovery=4

Unfortunately, though, I ended up losing about 75% of my data and had to restore from my December backup :-O

At the time I had an InnoDB table in my main database named "error_log". I don't know how it became corrupted, but every table after it (first letter of the name being "f" or greater) just disappeared.

I also discovered that all of the InnoDB tables in my clients' databases were now read-only (which I guess came from innodb_force_recovery).

I rebuilt EVERY table (literally hundreds) to MyISAM and deleted all of the InnoDB tables. The only ones I didn't touch were the "mysql" and "sys" databases, but neither of them show anything corrupted.

But still, if I remove innodb_force_recovery then the database is unresponsive.

Then, in August WHM/cPanel tried to do an update that appeared to cause another problem! The server provider changed innodb_force_recovery from 4 to 5, and then it became responsive again. Best that I can tell, this crash was because the InnoDB tables in "mysql" and "sys" are now read-only, and cPanel tried to write to them.

Neither cPanel nor my server provider can find a reason for this problem, but if I remove innodb_force_recovery then the database is unresponsive. I keep reading that it's a terrible idea to leave that in there permanently, but I'm at a loss on how to find the actual problem!

Any suggestions?

Options: ReplyQuote


Subject
Written By
Posted
Major problem with InnoDB, having to use innodb_force_recovery
November 22, 2021 01:25PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.