InnoDB Recovery Modes 



The XtraDB/InnoDB recovery mode is a mode used for recovering from emergency situations. You should ensure you have a backup of your database before making changes in case you need to restore it. The innodb_force_recovery server system variable sets the recovery mode. A mode of 0 is normal use, while the higher the mode, the more stringent the restrictions. Higher modes incorporate all limitations of the lower modes.

The recovery mode should never be set to a value other than zero except in an emergency situation.

Generally, it is best to start with a recovery mode of 1, and increase in single increments if needs be. With a recovery mode < 4, only corrupted pages should be lost. With 4, secondary indexes could be corrupted. With 5, results could be inconsistent and secondary indexes could be corrupted (even if they were not with 4). A value of 6 leaves pages in an obsolete state, which might cause more corruption.

Until MariaDB 10.2.7, mode 0 was the only mode permitting changes to the data. From MariaDB 10.2.7, write transactions are permitted with mode 3 or less.

To recover the tables, you can execute SELECTs to dump data, and DROP TABLE (when write transactions are permitted) to remove corrupted tables.

The following modes are available:

Isolation Level Dirty Read Non-Repeatable ReadPhantom read.
READ UNCOMMITTEDPossiblePossible Possible
READ COMMITTEDNot PossiblePossiblePossible
REPEATABLE READNot PossibleNot PossiblePossible
SERIALIZABLENot Possible Not PossibleNot Possible

Note also that XtraDB (<= MariaDB 10.2.6) by default will crash the server when it detects corrupted data in a single-table tablespace. This behaviour can be changed – see the innodb_corrupt_table_action system variable.

Fixing Things

Try to set innodb_force_recovery to 1 and start mariadb. If that fails, try a value of “2”. If a value of 2 works, then there is a chance the only corruption you have experienced is within the innodb “undo logs”. If that gets mariadb started, you should be able to dump your database with mysqldump. You can verify any other issues with any tables by running “mysqlcheck –all-databases”.

If you were able to successfully dump your databases, or had previously known good backups, drop your database(s) from my mariadb command line like “DROP yourdatabase”. Stop mariadb. Go to /var/lib/mysql (or where ever your mysql data directory is located) and “rm -i ib*”. Start mariadb, create the database(s) you dropped (CREATE yourdatabase), and then import your most recent dumps: “mysql < mydatabasedump.sql”

References 

https://mariadb.com/kb/

https://dev.mysql.com/doc/refman/8.0/en/innodb-recovery.html

☛ MinervaDB contacts – Sales & General Inquiries

Business FunctionContact
☎ CONTACT GLOBAL SALES (24*7)📞 (844) 588-7287 (USA)
📞 (415) 212-6625 (USA)
📞 (778) 770-5251 (Canada)
☎ TOLL FREE PHONE (24*7)📞 (844) 588-7287
🚩 MINERVADB FAX+1 (209) 314-2364
📨 MinervaDB Email - General / Sales / Consultingcontact@minervadb.com
📨 MinervaDB Email - Supportsupport@minervadb.com
📨 MinervaDB Email -Remote DBAremotedba@minervadb.com
📨 Shiv Iyer Email - Founder and Principalshiv@minervadb.com
🏠 CORPORATE ADDRESS: CALIFORNIAMinervaDB Inc.,
340 S LEMON AVE #9718
WALNUT 91789 CA, US
🏠 CORPORATE ADDRESS: DELAWAREMinervaDB Inc.,
PO Box 2093 PHILADELPHIA PIKE #3339
CLAYMONT, DE 19703
🏠 CORPORATE ADDRESS: HOUSTONMinervaDB Inc., 1321 Upland Dr. PMB 19322, Houston,
TX, 77043, US