Magento Database Recovery

Magento

We recently made some alterations to our Magento dev server and buggered up the database in such a bad way we couldn’t even log into the admin. The easiest way to recover was to restore from last nights backup. Turns out it wasn’t so easy.

I removed the buggered MySQL db and restored last nights dump. As I was importing the DB I was getting this error:

ERROR 1005 (HY000): Can't create table 'Table.frm' (errno: 150)

I knew that none of the files had changed so it must have been a database issue. After some digging I added this to the sql file:


SET FOREIGN_KEY_CHECKS = 0; -- TOP of .sql
SET FOREIGN_KEY_CHECKS = 1; -- BOTTOM of .sql

The import worked fine this time round. When I browsed to the site I got this php error:

"PHP Fatal error: Call to a member function extend() on a non-object in /var/www/html/website_thebigtoybook/privatesale/app/code/core/Mage/Core/Model/Mysql4/Config.php on line 115"

I’m pretty pissed off at this point. Thankfully I was able to track down a fix pretty quickly (with some help) and added the following to the top of the .sql file:

SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;

I of course had to delete the database, then re-import with this new setting. It worked like a charm and we were back in business. If I had more time I’d look into exactly what these commands do (although I do have some idea…related to indicies and INNODB tables) but I’m going away for a few days and just need to get it done…so more homework on this will be done another day.