The thing you want is to fix the corrupt data that is in your current installation. The reason that you are getting Foreign key constraint errors is because the referenced item isn't available. Let's give an example:
You have a product with ID 5. The product is saved in catalog_product_entity
. Now you assign the product to a category, the information for that is saved in catalog_category_product
. In that table there is a column product_id
which has a foreign key to the catalog_product_entity table
So when a line is added to this table the product MUST exist in the catalog_product_entity
table. The advantage is that you protect the database from corruption, is does give you some nasty errors now and then, but at least it wont break your database... in theory.
In your case the database was set up incorrectly, exported with the wrong permissions sometime in the past or something like that, causing the constraint to be probably absent in your case. This causes problems because now you have all sorts of useless rows which just sit there.
Old Advise
The errors you are now getting are probably on the old rows. What I suggest you do is download a database management program which allows you to continue importing when an error occurs ignore the error an continue.
I have not tested not tested this method my self in this specific case, so thorough checking of the imported data is required. If the site is broken after the import then there are probably missing tables in the old database. In that case disable the foreign key check as described in the other comments and run the repair tool you mentioned yourself earlier.
Ps. Since it is about your database and all your data, it may not be unwise to have a magento developer who knows Magento have a look.
New Advise
I'll advise against my own case of importing the data, just had a similar problem today, I ran the following query multiple times (20 times in my case), this removes duplicates from the eav tables and solves a few problems.
DELETE `duplicated`.*
FROM `catalog_product_entity_datetime` AS `orig`
INNER JOIN `catalog_product_entity_datetime` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_product_entity_decimal` AS `orig`
INNER JOIN `catalog_product_entity_decimal` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_product_entity_int` AS `orig`
INNER JOIN `catalog_product_entity_int` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_product_entity_text` AS `orig`
INNER JOIN `catalog_product_entity_text` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_product_entity_varchar` AS `orig`
INNER JOIN `catalog_product_entity_varchar` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_category_entity_datetime` AS `orig`
INNER JOIN `catalog_category_entity_datetime` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_category_entity_decimal` AS `orig`
INNER JOIN `catalog_category_entity_decimal` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_category_entity_int` AS `orig`
INNER JOIN `catalog_category_entity_int` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_category_entity_text` AS `orig`
INNER JOIN `catalog_category_entity_text` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.*
FROM `catalog_category_entity_varchar` AS `orig`
INNER JOIN `catalog_category_entity_varchar` AS `duplicated`
ON (`orig`.`entity_id` = `duplicated`.`entity_id`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`attribute_id` = `duplicated`.`attribute_id`
AND `orig`.`value_id` > `duplicated`.`value_id`);
DELETE `duplicated`.* FROM `core_url_rewrite` AS `orig`
INNER JOIN `core_url_rewrite` AS `duplicated`
ON (
`orig`.`id_path` = `duplicated`.`id_path`
AND `orig`.`is_system` = `duplicated`.`is_system`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`url_rewrite_id` > `duplicated`.`url_rewrite_id`
)
DELETE `duplicated`.* FROM `core_url_rewrite` AS `orig`
INNER JOIN `core_url_rewrite` AS `duplicated`
ON (
`orig`.`request_path` = `duplicated`.`request_path`
AND `orig`.`store_id` = `duplicated`.`store_id`
AND `orig`.`url_rewrite_id` > `duplicated`.`url_rewrite_id`
)
When you do a mysql dump, you're not just dumping a single store but the entire Magento database. Your issue is that
require_once 'app/Mage.php';
Mage::app('default');
You're loading 'default' as the scope. This throws an exception:
/**
* Initialize currently ran store
*
* @param string $scopeCode code of default scope (website/store_group/store code)
* @param string $scopeType type of default scope (website/group/store)
* @return unknown_type
*/
protected function _initCurrentStore($scopeCode, $scopeType)
{
Varien_Profiler::start('mage::app::init::stores');
$this->_initStores();
Varien_Profiler::stop('mage::app::init::stores');
if (empty($scopeCode) && !is_null($this->_website)) {
$scopeCode = $this->_website->getCode();
$scopeType = 'website';
}
switch ($scopeType) {
case 'store':
$this->_currentStore = $scopeCode;
break;
case 'group':
$this->_currentStore = $this->_getStoreByGroup($scopeCode);
break;
case 'website':
$this->_currentStore = $this->_getStoreByWebsite($scopeCode);
break;
default:
$this->throwStoreException();
}
Just leave it blank as in:
require_once 'app/Mage.php';
Mage::app();
Best Answer
I'm the first to advocate doing something native to Magento if it can do it, but this is an instance where native is not best.
The built in backup facility in Magento should be avoided at all costs. It pays no heed to table level locks, and will almost certainly bring your store down during its painstakingly long process.
MySQL backups should be carried out via MySQL itself, not via a PHP library.
For your daily backups, mysqldump is perfect. There's a wrapper for this called automysqlbackup - which is well tested and reliable. This would be a good fit for you.
Hourly backups, whilst certainly desirable to lower recovery point objective, will come with inherent problems. Percona make an excellent tool for taking rapid backups, point in time snapshots and incremental binlog snapshots. The package is XtraBackup. It's free but very complex to configure. It snapshots huge databases very quickly, but at the penalty of the output file being an unprocessed format (not an easily restorable .sql file).
Some run MySQL slaves for the sole propose of executing backups, but again, this is not without it's challenges. I would strongly advise attempting to do this, this risk of data loss, or performance bottlenecking through misconfiguration is far too high.
Realistically, backups are the responsibility of your hosting provider (not a store owner/developer) - it would be advised to leave it to them, because a misconfigured backup script will cause you two very real problems,
If you want a starter script, n98 MageRun (https://github.com/netz98/n98-magerun) has a nice dump facility in it, and we've wrote a DB dump/restore script (see http://s.onas.si/qnwl)