Clearing Magento after testing

Question

I am currently doing a lot of tests in Magento so I can get familiar with it. Unfortunately the database is extremely cluttered with my tests. I wish to clear up the database so I can start over again. This means doing the following:

Clearing all products
Clearing all attributes
Clearing all attribute sets
Clearing all categories
Clearing all orders, shipments, transactions etc.

I have found a post on the Magento forums for clearing the products, which is the following SQL:

SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE TABLE `catalog_product_bundle_option`;
TRUNCATE TABLE `catalog_product_bundle_option_value`;
TRUNCATE TABLE `catalog_product_bundle_selection`;
TRUNCATE TABLE `catalog_product_entity_datetime`;
TRUNCATE TABLE `catalog_product_entity_decimal`;
TRUNCATE TABLE `catalog_product_entity_gallery`;
TRUNCATE TABLE `catalog_product_entity_int`;
TRUNCATE TABLE `catalog_product_entity_media_gallery`;
TRUNCATE TABLE `catalog_product_entity_media_gallery_value`;
TRUNCATE TABLE `catalog_product_entity_text`;
TRUNCATE TABLE `catalog_product_entity_tier_price`;
TRUNCATE TABLE `catalog_product_entity_varchar`;
TRUNCATE TABLE `catalog_product_flat_1`;
TRUNCATE TABLE `catalog_product_link`;
TRUNCATE TABLE `catalog_product_link_attribute`;
TRUNCATE TABLE `catalog_product_link_attribute_decimal`;
TRUNCATE TABLE `catalog_product_link_attribute_int`;
TRUNCATE TABLE `catalog_product_link_attribute_varchar`;
TRUNCATE TABLE `catalog_product_link_type`;
TRUNCATE TABLE `catalog_product_option`;
TRUNCATE TABLE `catalog_product_option_price`;
TRUNCATE TABLE `catalog_product_option_title`;
TRUNCATE TABLE `catalog_product_option_type_price`;
TRUNCATE TABLE `catalog_product_option_type_title`;
TRUNCATE TABLE `catalog_product_option_type_value`;
TRUNCATE TABLE `catalog_product_super_attribute_label`;
TRUNCATE TABLE `catalog_product_super_attribute_pricing`;
TRUNCATE TABLE `catalog_product_super_attribute`;
TRUNCATE TABLE `catalog_product_super_link`;
TRUNCATE TABLE `catalog_product_enabled_index`;
TRUNCATE TABLE `catalog_product_website`;
TRUNCATE TABLE `catalog_product_relation`;
TRUNCATE TABLE `catalog_category_product_index`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `cataloginventory_stock_item`;
TRUNCATE TABLE `cataloginventory_stock_status`;
TRUNCATE TABLE `cataloginventory_stock_status_idx`;
TRUNCATE TABLE `cataloginventory_stock`;
TRUNCATE TABLE `core_url_rewrite`;
INSERT  INTO `catalog_product_link_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
INSERT  INTO `catalog_product_link_attribute`(`product_link_attribute_id`,`link_type_id`,`product_link_attribute_code`,`data_type`) VALUES (1,2,'qty','decimal'),(2,1,'position','int'),(3,4,'position','int'),(4,5,'position','int'),(6,1,'qty','decimal'),(7,3,'position','int'),(8,3,'qty','decimal');
INSERT  INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
TRUNCATE TABLE `catalog_product_entity`;
SET FOREIGN_KEY_CHECKS = 1;

I would need the same for the others mentioned. Unfortunately I don't know which tables are holding all the different data, otherwise I would've done it by myself. I also don't want to clear the wrong tables by accident. I hope you can help me!

Which data from your testing database do you still need? Did you consider exporting that data (for example your configuration core_config_data) and just start over again?
Also have a look at github.com/netz98/n98-magerun#stripped-database-dump - this allows you to export the database without orders etc. and then you can re-import.
@Alex I'm not sure what to keep because I got this from a third party that made the base of the shop. I need to continue developing on it myself, but I'm not entirely sure what I can just throw away and what not. Also, I'll look in to the link you gave.

Mufaddal · Accepted Answer · 2013-05-27 10:45:13Z

    ***********for categories********************
TRUNCATE TABLE `catalog_category_entity`;
TRUNCATE TABLE `catalog_category_entity_datetime`;
TRUNCATE TABLE `catalog_category_entity_decimal`;
TRUNCATE TABLE `catalog_category_entity_int`;
TRUNCATE TABLE `catalog_category_entity_text`;
TRUNCATE TABLE `catalog_category_entity_varchar`;
TRUNCATE TABLE `catalog_category_product`;
TRUNCATE TABLE `catalog_category_product_index`;

INSERT  INTO `catalog_category_entity`(`entity_id`,`entity_type_id`,`attribute_set_id`,`parent_id`,`created_at`,`updated_at`,`path`,`POSITION`,`level`,`children_count`) VALUES (1,3,0,0,'0000-00-00 00:00:00','2009-02-20 00:25:34','1',1,0,1),(2,3,3,0,'2009-02-20 00:25:34','2009-02-20 00:25:34','1/2',1,1,0);
INSERT  INTO `catalog_category_entity_int`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,32,0,2,1),(2,3,32,1,2,1);
INSERT  INTO `catalog_category_entity_varchar`(`value_id`,`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES (1,3,31,0,1,'Root Catalog'),(2,3,33,0,1,'root-catalog'),(3,3,31,0,2,'Default Category'),(4,3,39,0,2,'PRODUCTS'),(5,3,33,0,2,'default-category');

    *****************for customers*****************


    SET FOREIGN_KEY_CHECKS=0;
-- reset customers
TRUNCATE customer_address_entity;
TRUNCATE customer_address_entity_datetime;
TRUNCATE customer_address_entity_decimal;
TRUNCATE customer_address_entity_int;
TRUNCATE customer_address_entity_text;
TRUNCATE customer_address_entity_varchar;
TRUNCATE customer_entity;
TRUNCATE customer_entity_datetime;
TRUNCATE customer_entity_decimal;
TRUNCATE customer_entity_int;
TRUNCATE customer_entity_text;
TRUNCATE customer_entity_varchar;
TRUNCATE log_customer;
TRUNCATE log_visitor;
TRUNCATE log_visitor_info;

ALTER TABLE customer_address_entity AUTO_INCREMENT=1;
ALTER TABLE customer_address_entity_datetime AUTO_INCREMENT=1;
ALTER TABLE customer_address_entity_decimal AUTO_INCREMENT=1;
ALTER TABLE customer_address_entity_int AUTO_INCREMENT=1;
ALTER TABLE customer_address_entity_text AUTO_INCREMENT=1;
ALTER TABLE customer_address_entity_varchar AUTO_INCREMENT=1;
ALTER TABLE customer_entity AUTO_INCREMENT=1;
ALTER TABLE customer_entity_datetime AUTO_INCREMENT=1;
ALTER TABLE customer_entity_decimal AUTO_INCREMENT=1;
ALTER TABLE customer_entity_int AUTO_INCREMENT=1;
ALTER TABLE customer_entity_text AUTO_INCREMENT=1;
ALTER TABLE customer_entity_varchar AUTO_INCREMENT=1;
ALTER TABLE log_customer AUTO_INCREMENT=1;
ALTER TABLE log_visitor AUTO_INCREMENT=1;
ALTER TABLE log_visitor_info AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;

For Orders

    SET FOREIGN_KEY_CHECKS=0; 
TRUNCATE `sales_flat_creditmemo`; 
TRUNCATE `sales_flat_creditmemo_comment`; 
TRUNCATE `sales_flat_creditmemo_grid`; 
TRUNCATE `sales_flat_creditmemo_item`; 
TRUNCATE `sales_flat_invoice`; 
TRUNCATE `sales_flat_invoice_comment`; 
TRUNCATE `sales_flat_invoice_grid`; 
TRUNCATE `sales_flat_invoice_item`; 
TRUNCATE `sales_flat_order`; 
TRUNCATE `sales_flat_order_address`; 
TRUNCATE `sales_flat_order_grid`; 
TRUNCATE `sales_flat_order_item`; 
TRUNCATE `sales_flat_order_payment`; 
TRUNCATE `sales_flat_order_status_history`; 
TRUNCATE `sales_flat_quote`; 
TRUNCATE `sales_flat_quote_address`; 
TRUNCATE `sales_flat_quote_address_item`; 
TRUNCATE `sales_flat_quote_item`; 
TRUNCATE `sales_flat_quote_item_option`; 
TRUNCATE `sales_flat_quote_payment`; 
TRUNCATE `sales_flat_quote_shipping_rate`; 
TRUNCATE `sales_flat_shipment`; 
TRUNCATE `sales_flat_shipment_comment`; 
TRUNCATE `sales_flat_shipment_grid`; 
TRUNCATE `sales_flat_shipment_item`; 
TRUNCATE `sales_flat_shipment_track`; 
TRUNCATE `sales_invoiced_aggregated`; 
TRUNCATE `sales_invoiced_aggregated_order`; 
TRUNCATE `sales_order_aggregated_created`; 
TRUNCATE `sendfriend_log`; 
TRUNCATE `tag`; 
TRUNCATE `tag_relation`; 
TRUNCATE `tag_summary`; 
TRUNCATE `wishlist`; 
TRUNCATE `log_quote`; 
TRUNCATE `report_event`; 
ALTER TABLE `sales_flat_creditmemo` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_creditmemo_comment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_creditmemo_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_creditmemo_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice_comment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_invoice_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_address` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_payment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_order_status_history` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_address` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_address_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_item_option` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_comment` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_grid` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_item` AUTO_INCREMENT=1; 
ALTER TABLE `sales_flat_shipment_track` AUTO_INCREMENT=1; 
ALTER TABLE `sales_invoiced_aggregated` AUTO_INCREMENT=1; 
ALTER TABLE `sales_invoiced_aggregated_order` AUTO_INCREMENT=1; 
ALTER TABLE `sales_order_aggregated_created` AUTO_INCREMENT=1; 
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1; 
ALTER TABLE `tag` AUTO_INCREMENT=1; 
ALTER TABLE `tag_relation` AUTO_INCREMENT=1; 
ALTER TABLE `tag_summary` AUTO_INCREMENT=1; 
ALTER TABLE `wishlist` AUTO_INCREMENT=1; 
ALTER TABLE `log_quote` AUTO_INCREMENT=1; 
ALTER TABLE `report_event` AUTO_INCREMENT=1; 
SET FOREIGN_KEY_CHECKS=1;

You can use this function to delete attribute sets.

 $resource = Mage::getSingleton('core/resource');
$db_read = $resource->getConnection('core_read');

$attribute_sets = $db_read->fetchCol("SELECT attribute_set_id FROM " . $resource->getTableName("eav_attribute_set") . " WHERE attribute_set_id<> 4 AND entity_type_id=4");
foreach ($attribute_sets as $attribute_set_id) {
    try {
        Mage::getModel("eav/entity_attribute_set")->load($attribute_set_id)->delete();
    } catch (Exception $e) {
        echo $e->getMessage() . "\n";
    }
}

And look for this table to remove attribute

catalog_eav_attribute
eav_attribute
eav_attribute_set

Check eav_attribute column is_user_defined so you can get some idea.

This is exactly what I am looking for! Do you also have it for the attributes and attribute sets?
No i have no sql for attribute and attribute sets, you can manually delete your attribute and attribute sets if there are no much entry
Unfortunately there are a lot of them. Anyway, thank you for these, it will certainly help! I'll just have to find them for the attributes by myself i guess.
very useful answer for me when i will going to live any project

Erfan · Answer 2 · 2013-05-14 08:41:25Z

You can just empty the whole database and Magento will run all its install/upgrade/data scripts again on the next pageload. This will "factory reset" Magento and will destroy all your changes.

A good habit is to create a module that contains all your basic store settings inside install/upgrade/data scripts. This has the benefit that these settings are stored in version control and all the different websites you have for the store (production, staging, user acceptance testing, etc.) will be kept up to date.

If there are multiple persons working on a site, we even create a new module for every setting group so the versions don't conflict when we merge code.

In your case, I would create a module that setups all the settings you want to keep. If you wipe the database, Magento will be completely clean AND including the settings you want!

You can have a look at the install/upgrade scripts of core Magento to see how Magento installs it's initial data/settings.

This is a good idea, there is only one problem: If you have installed extensions you might run into the problem, that they are not tested this way, so they throw errors, because the install scripts of the modules run before certain other modules and dependencies are not fulfilled. But I like this method too.
Can you explain that? If you are inserting a setting based on a third-party module in your own module, just declare the third-party module as a dependency (in your app/etc/module.xml) and your modules install/upgradescripts will run after the third-party modules'.
yes, but if the third party module doesn't do that or inserts data in install instead of data scripts you might get problems because of the order. In most cases, the developers assume, that magento is already installed ;-)
One other potential problem is that you may want to maintain configuration settings and/or CMS content. So that would have to be backed up and re-imported after clearing the database entirely.
@davidalger the point of these setup modules are to keep the configuration settings and/or CMS content so you dont have to re-import them. i.e. you can add CMS pages in your data-scripts so they get inserted again automatically after you cleared the db.

sonassi · Answer 3 · 2013-05-14 10:50:54Z

up vote 1 down vote

If you don't need anything at all - just empty the whole DB and do a clean re-install.

Running mass-truncates always worry me with any potential lasting effects of broken key relationships.

Fast DB purging

Using this script,

cd /path/to/my/magento/store
wget -O mage-dbpurge.sh sys.sonassi.com/mage-dbpurge.sh
chmod +x mage-dbpurge.sh
./mage-dbpurge.sh

Are you 100% sure you want to purge $DBNAME? [y/N]: y
Are you 110% sure you want to purge $DBNAME? [y/N]: y

MYSQL DB PURGE COMPLETE

That will completely empty and remove every single table in the database. Do not use it if you need to retain anything.

answered May 14 at 10:50

sonassi
4,2901525

	Most of these really get sketchy. Starting with shutting off the foreign key dependencies. Isn't a relational database supposed to delete everything automatically if you for instance, delete the key product, key sales order, etc. while foreign key dependencies are active as all the records in all the referenced tables delete with the top level item? What if you miss a table name in that truncation list doing it with foreign key relationships shut off? The MagentoCommerce site is resplendent with oddball error messages that appear after these mass clears. – Fiasco Labs May 27 at 14:36
	Well, looking at his script, it will indeed drop every table. As for disabling foreign key checks, you need that so you don't have to figure out the proper order to drop all of the tables. – Lee Saferite May 28 at 13:13
	@LeeSaferite - I think that FiascoLabs was referring to the other solutions offering truncate-only type solutions versus the total obliteration I suggested. – sonassi May 28 at 17:10
	Exactly what I meant! – Fiasco Labs May 29 at 2:43

Zyava · Answer 4 · 2013-05-15 08:26:05Z

up vote 1 down vote

You should have separate db for tests. See magento test framework. All ideas to clear db after tests are wrong.

answered May 15 at 8:26

Zyava
4217

asked	24 days ago
viewed	212 times
active	11 days ago

Clearing Magento after testing

4 Answers

Fast DB purging

Your Answer

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

Clearing Magento after testing

4 Answers

Fast DB purging

Your Answer

Sign up or log in

Post as a guest

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

Related