I already wrote about this issue but as I is third team I’m helping customers to resolve this “frm corruption” issue it is the time to return to it again.
During MySQL 5.0 release cycle the change was made so now MySQL does not stop if Innodb storage engine failed to initialize but starts properly… just having Innodb tables unavailable. I honestly do not know any case when this behavior change helps… of course you silently want to get good portion of your database unavailable right ? But I guess this change was made for reasons quite far from improving user experience.
It is worth to note MySQL 5.0 introduces STRICT option which will makes error control more strict, what is often expected by the Enterprise users – so instead of cutting the string lengths or converging NULLs to zeros MySQL can be tuned to abort such statements. In this, in my opinion much more serious case there is no option to only start MySQL if all enabled storage engines could be initialized. It should be pretty trivial to add one I think.
Note if we use typical comparison of Storage Engines to File Systems – you have an option to specify which of file systems are mandatory so system will not complete boot up without having these systems clearly mounted. The reason is same – you do not want to deal with all kind of gotchas which may happen when system is partially functional in such crazy way. It is much better to have it safely down so monitoring will scream and let you to get out quickly and fix things.
But it gets worse. Innodb failed initialization is not only very hidden – you will not see it unless you look in MySQL server Error Logs but the error message accessing Innodb tables is also very obfuscated:
error : Incorrect information in file: './prod/user.frm'
How this supposed to tell you Innodb (or for that sake any other Storage Engine) failed to initialize ?
This error suppose to mean .frm file is corrupted, and indeed you still can get this error message in such case, but now it is used for completely different reason.
Why not to have error message “Storage Engine is Not Available” or something similar ?
Users are confused by this error message and think it is indeed .frm corruption – trying to restore .frm from backup or other servers and being extremely frustrated as it does not work.
Of course if something looks wrong with your server you should first take a look at MySQL Server error logs but few people do it or spend a time examining error messages accurately enough.
Again if you made your made on this behavior, please at least add option such as –strict-start which would require server to start without serious errors in order to start listening on the sockets.
Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.
Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.