MySql : InnoDB_Force_recovery = 1 leads to table in read only

mysql database mariadb database-performance my.cnf
C Morell picture C Morell · Jan 17, 2017 · Viewed 8.5k times · Source

I have an application which runs under MariaDB 10.1.18 and I had problems of data corruption. So, in my.cnf I change the parameter innoDB_force_recovery = 1 to ignore them. But then, I can not make insert in tables. But I thought that insert are forbidden when innoDB_force_recovery = 4. Is it possible to make insert when innoDB_force_recovery = 1 ? Best regards. Christophe

Answer

user149341 picture user149341 · Jan 17, 2017

Setting innodb_force_recovery=1 does not fix data corruption! It ignores corruption to allow you to back up your data before rebuilding the database.

Only set this variable to a value greater than 0 in an emergency situation, so that you can start InnoDB and dump your tables. As a safety measure, InnoDB prevents INSERT, UPDATE, or DELETE operations when innodb_force_recovery is greater than 0.

So, no. You cannot insert data while innodb_force_recovery is active. Use mysqldump to create a backup of all data, then delete the MySQL data files and use your backup to restore it.

Related questions

Cast int to varchar

I have below query and need to cast id to varchar Schema create table t9 (id int, name varchar (55)); insert into t9( id, name)values(2, 'bob'); What I tried select CAST(id as VARCHAR(50)) as col1 from t9; select CONVERT(…

Read More
xtradb vs innodb

I was asked to check Mariadb as Centos does not provider MySQL 5.5 for the moment. I have read that xtradb servers as a drop in for innodb. What are the advantages of using one or the other because if they …

Read More
MariaDB crash: Table doesn't exist in engine && can't recover tables

I'm running: MariaDB 10.0.5 Ubuntu 12.04 LTS Both up to the latest patch level as per last Friday I've gotten into quite a pickle with a previous issue: http://stackoverflow.com/questions/19910996/mysql-connect-headers-and-client-library-minor-version-mismatch-library10000 This has resulted in massive corruption because I copied &…

Read More