The ADA MySql service will not start due to table corruption
Article ID: 185701
Updated On:
Products
Issue/Introduction
2020-02-26T16:03:05.890743Z 3345 [ERROR] InnoDB: Operating system error number 665 in a file operation.
2020-02-26T16:03:05.890743Z 3345 [ERROR] InnoDB: File .\super\classify_all_metrics.ibd: 'Windows aio' returned OS error 765. Cannot continue operation
Environment
Application Delivery Analysis 11.0.2
Cause
InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk. Since the InnoDB approach is approximately fixed time while the MyISAM time grows with the size of the data files, InnoDB offers greater availability as database sizes grow. InnoDB tables crash much less frequently than MyISAM tables, but when they do, how do we fix them?
How to recognize InnoDB tables from MyISAM tables.
MyISAM tables are comprised of <table>.frm, <table>.MYD and <table>.MYI files.
InnoDB tables are comprised of <table>.from and <table>.ibd files.
These files can be seen in the C:\CA\MySql\data\super folder.
Resolution
1. On the ADA server, edit the C:\CA\MySql\my.ini file
2. Turn on the forced recovery option, by adding the following to the [mysqld] section:
innodb_force_recovery = 1
* see note 1. below
3. Start the CA MySql service
4. Run a 'mysqlcheck super' on the super db to see exactly which tables are corrupted.
5. Export the corrupted tables.
mysqldump -defaults-file=C:\CA\MySql\my.ini super classify_all_metrics > dump.sql
6. Drop the corrupted tables.
7. Turn off forced recovery. (Remove the parameter added in setp 1 from my.ini and restart the MySql service)
8. import the exported tables from the MySql dump in step 4.
mysql --defaults-file=C:\CA\MySql\my.ini super < dump.sql
* note 1.
innodb_force_recovery is 0 by default (normal startup without forced recovery). The permissible nonzero values for innodb_force_recovery are 1 to 6. A larger value includes the functionality of lesser values. For example, a value of 3 includes all of the functionality of values 1 and 2.
If you are able to dump your tables with an innodb_force_recovery value of 3 or less, then you are relatively safe that only some data on corrupt individual pages is lost. A value of 4 or greater is considered dangerous because data files can be permanently corrupted. A value of 6 is considered drastic because database pages are left in an obsolete state, which in turn may introduce more corruption into B-trees and other database structures.
Additional Information
Forcing InnoDB Recovery:
https://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
Feedback
