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 Read | Phantom read. |
|---|---|---|---|
| READ UNCOMMITTED | Possible | Possible | Possible |
| READ COMMITTED | Not Possible | Possible | Possible |
| REPEATABLE READ | Not Possible | Not Possible | Possible |
| SERIALIZABLE | Not Possible | Not Possible | Not 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://dev.mysql.com/doc/refman/8.0/en/innodb-recovery.html
☛ MinervaDB contacts – Sales & General Inquiries
| Business Function | Contact |
|---|---|
| ☎ 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 / Consulting | contact@minervadb.com |
| 📨 MinervaDB Email - Support | support@minervadb.com |
| 📨 MinervaDB Email -Remote DBA | remotedba@minervadb.com |
| 📨 Shiv Iyer Email - Founder and Principal | shiv@minervadb.com |
| 🏠 CORPORATE ADDRESS: CALIFORNIA | MinervaDB Inc. 440 N BARRANCA AVE #9718 COVINA, CA 91723 |
| 🏠 CORPORATE ADDRESS: DELAWARE | MinervaDB Inc., PO Box 2093 PHILADELPHIA PIKE #3339 CLAYMONT, DE 19703 |
| 🏠 CORPORATE ADDRESS: HOUSTON | MinervaDB Inc., 1321 Upland Dr. PMB 19322, Houston, TX, 77043, US |