Be careful with innodb_force_recovery

Recently, I needed to import a large SQL dump into a server running MariaDB 10.1. I was surprised to see the following message pop up in the middle of import:

ERROR 1036 (HY000) at line 1111: Table 'clients' is read only

These kinds of messages are typically encountered when DB files are copied directly and filesystem-level permissions are amiss. In my case though, I had a consistent SQL dump that just wouldn’t import. Attempting to do any inserts into the table using the command line client would fail with the same error message. It’s worth noting that the database was a mix of MyISAM and InnoDB tables, and the MyISAM tables imported fine.

As it turns out, the culprit was the following line in my.cnf:

innodb_force_recovery=3

This line was inherited from a previous setup. Recent versions of MySQL switch the InnoDB engine into read-only mode when this value is set to >=4. Apparently, MariaDB took this one step further and switched to read-only at the value of 3.

One thought on “Be careful with innodb_force_recovery”

  1. I think that my mySQL database is corrupt, and after consultation with Digital Ocean the help guy has advised me to run an innodb

Leave a Reply

Your email address will not be published.