0

I'm transferring a bunch of domains a new host. The site names remain the same, and for the sites I've already migrated, no manipulation was necessary in the database. It just works once name-servers are pointed at the new box. That's great.

For some sites, however, during import, mysql throws this error:

Operation not allowed when innodb_forced_recovery > 0

This is true whether I'm importing the sql from commandline, phpMyAdmin or cpanel import. (Well, cpanel import isn't really that communicative about errors.)

I've got a lot of sites to transfer. It doesn't happen with all databases.

This is my set up on the existing host: Apache 2.2.29 - PHP: 5.3.16 - MySQL: 5.6.23

This is my setup on the new host: Apache 2.2.29 - PHP: 5.3.29 - MySQL: 5.6.23

The data work fine in their current hosts. They don't always import well into the new host. I've tried optimizing and repairing tables, but some tables won't repair or optimize. It's usually these databases with these kinds of tables that cause problems.

I can export a database easily. And nothing looks odd in the sql. It's only when I get to the import side that I start seeing the above error.

What is causing this, and what is a best practice for transferring data that doesn't want to import because of this error?

| improve this question | |
  • I doubt this is specific to WP. I would inquire with the host you are importing to. Hosts usually offer imports as a service (or even for free), they are best positioned to answer why might it have issue with import into their environment. – Rarst Oct 23 '15 at 15:41
  • I suspect you're right. I realize now that some plugins change their particular tables to innodb from myisam. Additionally, CiviCRM plugin requires its database to be entirely innodb. I might need to bring this elsewhere. Thanks. – Chris Oct 23 '15 at 15:46
  • Actually, I'm having this problem even when it doesn't involve CiviCRM. If it's a wordpress plugin, isn't this actually "on" topic? The problem exists with other wordpress plugins... Not just CiviCRM. For instance, Ninja Forms plugin does this as well. – Chris Oct 23 '15 at 16:24
  • Hmhm... I will reopen, but I am still not sure this makes sense in scope. DB is usually kind of thing of its own in WP. – Rarst Oct 23 '15 at 16:42
  • +1 for t being off-topic. This might even be a problem with the export. You need to ask people with mysql knowledge, this is probably the wrong place for that. – Mark Kaplun Oct 23 '15 at 16:51
1

Commenting out the line 

innodb_force_recovery = 1

in /etc/my.cnf thus:

# innodb_force_recovery = 1

made the mysql innodb tables in the database accessible. Apparently this setting causes innodb to become read-only. I hope this helps someone in the future. 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.

For future reference, I hope this will help someone else who is using wordpress plugins that require innodb flavored tables. I know there was some concern that this may not have been the appropriate place for this question, but I suspect as more plugins and perhaps even WP starts using InnoDB, others may be glad for the crossover knowledge posted here.

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.