Automatic REPAIR TABLE tablename after MySQL error 127

[markjaquith]

Whenever a MySQL error 127 crops up, the solution (for me) has always been to repair the table... and it always works. Should we detect these errors and attempt to repair automatically?

[westi]

+1 sounds like a good idea.

Maybe a page like the upgrade page in the admin area which is shown on detection and allows the admin to run the repair.

[Nazgul]

+1 as a plugin (if that's possible when the DB throws a 127?)
-1 as part of the core.

Wordpress is a blogging tool, not a sysadmin tool and therefore (by default) shouldn't pretend to be one.

Also, the 127 error happened/happens for a reason. You should find out why instead of just fixing the symptoms by doing a REPAIR TABLE.

[Otto42]

You could do this with a plugin. You'll need to extend the $wpdb class to do it. I have not tested any of this, of course...

class my_wpdb extends wpdb {
	// override the print_error function
	function print_error($str = '') {
		if (mysql_errno($this->dbh) == 127 ) {
			// do REPAIR TABLE or whatever
		}
		else { 
			parent::print_error($str);
		}
	}

	// copy constructor to handle the annoying missing table variables problem
	function my_wpdb($old_wpdb) {
		// casting an object to an array returns the vars in the object
		// in the array.. so $vars['posts']='wp_posts'. Handy, no?
		$vars = (array)$old_wpdb;
		extract($vars, EXTR_OVERWRITE);
	}
}
// create a new object for the database
$my_wpdb = new my_wpdb($wpdb);

// assign it back to the main database
$wpdb = $my_wpdb;

That should work, I think. Should work in a plugin too. But of course, it won't work if you get the 127 error before the plugins get loaded.

[Otto42]

Well, okay, so that extract($vars, EXTR_OVERWRITE); is wrong. Still, you could do it with a loop or something. Just need to make it get all of the variables set in the new object.

[ffemtcj]

No Patch. Moved to 2.7

[mrmist]

Is this going anywhere? There seems to be 50/50 support for plugin or core functionality.

The user-friendly part of me suggests that it might be a good idea to attempt a repair. The sysadmin in me says that user-level code should not be fiddling with sysadmin functions.

At the very least this should be a configurable option if it's brought in. (So that it could be turned off). I'd say off by default, but that would defeat the point in pretty much every case (I.E where a user isn't techy enough to do it themselves.)

[vladimir_kolesnikov]

Replying to mrmist:

The user-friendly part of me suggests that it might be a good idea to attempt a repair.

And if REPAIR for some reason fails (one of that rare cases when after MySQL crash index file gets corrupted and you need to issue REPAIR TABLE xxx USE_FRM)? need a way to prevent infinite REPAIR loop.

Another issue that worries me is that what happens if a table crashed under heavy load (e.g., misconfigured MySQL died on signal 11)? With this scenario we are going to try to REPAIR the table but if MySQL dies (again) while REPAIR is in progress, bad things may happen.

Last, but not least, MySQL manual (​http://dev.mysql.com/doc/refman/5.0/en/repair-table.html) says this:

Caution: It is best to make a backup of a table before performing a table repair operation; under some circumstances the operation might cause data loss. Possible causes include but are not limited to filesystem errors.

and

If the server dies during a REPAIR TABLE operation, it is essential after restarting it that you immediately execute another REPAIR TABLE statement for the table before performing any other operations on it. In the worst case, you might have a new clean index file without information about the data file, and then the next operation you perform could overwrite the data file. This is an unlikely but possible scenario.

where a user isn't techy enough to do it themselves.

If the user is not techy enough, IMO he should ask his hoster's support to fix the error. Quoting the MySQL manual again,

If your tables become corrupted often, you should try to find the reason for it, to eliminate the need to use REPAIR TABLE

Need to fix the problem, not cure symptoms.

I would suggest not to try to do sysadmin's job. Data recovery is not a pleasant thing.

[mrmist]

I did say I was 50/50 on this....

There's been no traction on this all the way through the 2.7 release. I'd say it's going nowhere.

[DD32]

I originally thought it was a good idea, But if anything, I think it should remain as a plugin, Simply for the fact of all the things that could possibly go wrong to make matters worse. suggestion: wontfix

[DD32]

I'm going to close as WontFix, It seems that the potential to corrupt the database is too large.