Operation not allowed when innodb_forced_recovery > 0 [SqlYog] – This article will take you through the common SQL errors that you might encounter while working with mysql, sql, sqlyog. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have created a table using SQLyog. When i insert values into it, it pops up following error message:
Operation not allowed when innodb_forced_recovery > 0.
My table consist only four columns including one primary key.
Following is my create and insert queries:
CREATE TABLE `news` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`title` varchar(100) NOT NULL,
`slug` varchar(100) NOT NULL,
`descr` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
insert into `test`.`news` (`title`, `slug`, `descr`)
values ('titleOne', 'slugOne', 'descOne')
Solution :
This error is comes when MySQL is in Read only mode.
Edit file /etc/my.cnf.
And comment out following line
# innodb_force_recovery = 1
Apparently this setting causes innodb to become read-only. If you don’t have access to /etc/my.cnf on shared hosting, ask your host to fix it for you. When it’s commented out or non-existent in /etc/my.cnf, the it reverts to a default setting of 0.
This happens to me also but what i did was to change the SQL Engine during creatng the table from InnoDB to MyISAM
like in
ENGINE=innoDB to ENGINE=MyISAM
So if you have your database and want to upload it, open it with any editor and change the engine at the end of each table from innoDB to MyISAM.
this resolved the problem.
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.