Forum
Upgrade Errors
So I've just upgraded a site on it's own development server with it's own database from Perch 2.7.7 to 2.8.6. However upon upgrading I received the following error:
The following error occurred: CREATE TABLE IF NOT EXISTS
perch2_content_index(indexIDint(10) NOT NULL AUTO_INCREMENT,itemIDint(10) NOT NULL DEFAULT '0',regionIDint(10) NOT NULL DEFAULT '0',pageIDint(10) NOT NULL DEFAULT '0',itemRevint(10) NOT NULL DEFAULT '0',indexKeychar(64) NOT NULL DEFAULT '-',indexValuechar(255) NOT NULL DEFAULT '', PRIMARY KEY (indexID), KEYidx_key(indexKey), KEYidx_val(indexValue), KEYidx_rev(itemRev), KEYidx_item(itemID), KEYidx_keyval(indexKey,indexValue), KEYidx_regrev(regionID,itemRev) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 SQLSTATE[HY000]: General error: 144 Table './Enzymes/perch2_content_index' is marked as crashed and last (automatic?) repair failed
I've been into the database via Sequel Pro to repair the offending table however it seems that it cannot be repaired.
On trying to repair the table I receive the following message:
Repair table 'perch2_content_index' Successfully repaired table. MySQL said: Operation failed
The table is still corrupt, any ideas? I have everything backed up and can roll back if necessary.
Thanks
Jason
That's a very odd message - "Successfully repaired table. MySQL said: Operation failed" !
If you restore the backup into a different database, does it restore successfully?
Yes It is indeed! And yes I've successfully rolled back from yesterday. I may just do a fresh install of perch and then just repopulate the content which shouldn't take too long as I don't really have the time to trouble shoot the problem for too much longer, which is a shame as I'd like to know what caused it.
What sort of hosting is it on? Table corruption is not common, and is usually a sign of an overstretched server.
Rackspace Hosting, It's on its own dedicated Windows development server with the database being a cloud MySQL slice. This perch install was the only Application using that whole slice. It may be a blip at there end mind.
Hmm. Shouldn't be an issue then, really.
Ok some more info, I just did a fresh install of the latest perch on the same slice but with a new database and all tables are good. However checking the database I rolled back yesterday it looks as if that perch2_content_index was already corrupt before the upgrade I still can't repair it. And still get the odd repair message.
I have a feeling it may be a Rackspace problem I'll get in touch with them and report back if I get anything, although the slice does have monitoring checks set up and never reported resources being at breaking point.
Honestly, I'd be hard pushed to find something I could do from PHP that would corrupt a database table, so asking your host is a good idea.
So all that Rackspace recommend was to switch to InnoDB as the table engine for the database rather than MyISAM.
(InnoDB is resilient to crashes, whereas MyISAM tables are not crash-safe just incase anyone reading this later wants reference).
However InnoDB is their and MySQL's default and we haven't changed it.
Does Perch prefer to use MyISAM?
Thanks for all your help so far
J
The only case where we need MyISAM is on tables that use full text search. Otherwise InnoDB is a good, or better option.
InnoDB isn't available on all host servers, so we have to play safe and stick to MyISAM.
perch2_content_indexwould be a good candidate to switch to InnoDB, and should be safe to do so.Ok that's great! Thanks for the info & all your help Drew!
No problem!