MySQL Forums
Forum List  »  InnoDB

Cannot Import InnoDB tables. Error: Operation not allowed when innodb_forced_recovery > 0
Posted by: Chris Sparnicht
Date: October 26, 2015 08:19AM

Wordpress 4.3.1 - CiviCRM 4.5.5
CivicCRM is on its own DB with all tables set as InnoDB. Wordpress is MyISAM.

Old host: Apache 2.2.26 - PHP: 5.3.28 - MySQL: 5.1.73
InnoDB settings on old host:
$ grep innodb /etc/my.cnf
innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.
#innodb_flush_method=O_DSYNC # Do not enable this without a good reason - it doesn't work well on new dedis
innodb_buffer_pool_size=128M # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
innodb_thread_concurrency=4 # Number of physical + virtual CPU's, be careful of adding more
innodb_flush_log_at_trx_commit=2
innodb_log_buffer_size=64M
innodb_io_capacity=100

New host: Apache 2.2.29 - PHP: 5.3.29 - MySQL: 5.6.23
InnoDB settings on new host:
$ grep innodb /etc/my.cnf
#skip-innodb
innodb_force_recovery = 1
innodb_file_per_table=1
#ignore-builtin-innodb
#innodb_use_native_aio=0
#innodb_buffer_pool_size=134217728
innodb_buffer_pool_size=8388608

Assume I'm on a shared host and that I have access to ssh on the new server, but am unable personally to change settings in new host my.cnf.
Assume I don't have ssh access on the old host, but can download data.sql via cpanel or phpMyAdmin.

When I attempt to import mysql data using any method, including cpanel import, phpMyAdmin import or even the standby:
mysql -u username -p -h localhost DATA-BASE-NAME < data.sql
I get this error or something equivalent:
Operation not allowed when innodb_forced_recovery > 0
for any DB containing tables with InnoDB.

Is there anything I can do during export via phpMyAdmin on the old host that will allow me to import data.sql without problems on the new host?

Or would it be better to ask the host to simply comment out this setting in /etc/my.cnf on the new host thus?
# innodb_force_recovery = 1

Why would a shared host situation institute this setting and leave it that way?

Why doesn't regular import work this way when the setting is not commented out?

I've asked about this in other venues. Unfortunately, not much information is available. Additionally, when searching this forum for the exact phrase "innodb_forced_recovery > 0" I get "No Results Found". I've encountered this problem enough times that I'm surprised it hasn't come up before here. Anyway, I'd be grateful for assistance.

Cheers.

Options: ReplyQuote


Subject
Views
Written By
Posted
Cannot Import InnoDB tables. Error: Operation not allowed when innodb_forced_recovery > 0
10280
October 26, 2015 08:19AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.