Tuesday, December 8, 2015

How to clear Magento Database after testing

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

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 `log_quote`;

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 `log_quote` AUTO_INCREMENT=1;

#########################################
# DOWNLOADABLE PURCHASED
#########################################
TRUNCATE `downloadable_link_purchased`;
TRUNCATE `downloadable_link_purchased_item`;

ALTER TABLE `downloadable_link_purchased` AUTO_INCREMENT=1;
ALTER TABLE `downloadable_link_purchased_item` AUTO_INCREMENT=1;

#########################################
# RESET ID COUNTERS
#########################################
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;


################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################



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;
TRUNCATE `tag`;
TRUNCATE `tag_relation`;
TRUNCATE `tag_summary`;
TRUNCATE `tag_properties`;           
TRUNCATE `wishlist`;
TRUNCATE `log_customer`;


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;
ALTER TABLE `tag` AUTO_INCREMENT=1;
ALTER TABLE `tag_relation` AUTO_INCREMENT=1;
ALTER TABLE `tag_summary` AUTO_INCREMENT=1;
ALTER TABLE `tag_properties` AUTO_INCREMENT=1;
ALTER TABLE `wishlist` AUTO_INCREMENT=1;
ALTER TABLE `log_customer` AUTO_INCREMENT=1;



################ For product################

TRUNCATE  `catalog_product_bundle_option`;
TRUNCATE  `catalog_product_bundle_option_value`;
TRUNCATE  `catalog_product_bundle_selection`;
TRUNCATE  `catalog_product_entity_datetime`;
TRUNCATE  `catalog_product_entity_decimal`;
TRUNCATE  `catalog_product_entity_gallery`;
TRUNCATE  `catalog_product_entity_int`;
TRUNCATE  `catalog_product_entity_media_gallery`;
TRUNCATE  `catalog_product_entity_media_gallery_value`;
TRUNCATE  `catalog_product_entity_text`;
TRUNCATE  `catalog_product_entity_tier_price`;
TRUNCATE  `catalog_product_entity_varchar`;
TRUNCATE  `catalog_product_flat_1`;
TRUNCATE  `catalog_product_link`;
TRUNCATE  `catalog_product_link_attribute`;
TRUNCATE  `catalog_product_link_attribute_decimal`;
TRUNCATE  `catalog_product_link_attribute_int`;
TRUNCATE  `catalog_product_link_attribute_varchar`;
TRUNCATE  `catalog_product_link_type`;
TRUNCATE  `catalog_product_option`;
TRUNCATE  `catalog_product_option_price`;
TRUNCATE  `catalog_product_option_title`;
TRUNCATE  `catalog_product_option_type_price`;
TRUNCATE  `catalog_product_option_type_title`;
TRUNCATE  `catalog_product_option_type_value`;
TRUNCATE  `catalog_product_super_attribute_label`;
TRUNCATE  `catalog_product_super_attribute_pricing`;
TRUNCATE  `catalog_product_super_attribute`;
TRUNCATE  `catalog_product_super_link`;
TRUNCATE  `catalog_product_enabled_index`;
TRUNCATE  `catalog_product_website`;
TRUNCATE  `catalog_product_relation`;
TRUNCATE  `catalog_category_product_index`;
TRUNCATE  `catalog_category_product`;
TRUNCATE  `cataloginventory_stock_item`;
TRUNCATE  `cataloginventory_stock_status`;
TRUNCATE  `cataloginventory_stock_status_idx`;
TRUNCATE  `cataloginventory_stock`;
TRUNCATE  `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`;


##############################
# ADDITIONAL LOGS
##############################
TRUNCATE `log_url`;
TRUNCATE `log_url_info`;
TRUNCATE `log_visitor`;
TRUNCATE `log_visitor_info`;
TRUNCATE `report_event`;
TRUNCATE `report_viewed_product_index`;
TRUNCATE `sendfriend_log`;
TRUNCATE `log_summary`

ALTER TABLE `log_url` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor` AUTO_INCREMENT=1;
ALTER TABLE `log_visitor_info` AUTO_INCREMENT=1;
ALTER TABLE `report_event` AUTO_INCREMENT=1;
ALTER TABLE `report_viewed_product_index` AUTO_INCREMENT=1;
ALTER TABLE `sendfriend_log` AUTO_INCREMENT=1;
ALTER TABLE `log_summary` AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS = 1;


19 comments:

  1. Thank you for this post,This is exactly what Developer looking for!
    https://www.facebook.com/AppJetty

    ReplyDelete
  2. Thanks for sharing, i used this and got socceed, but here i found more about Clean Up Magento Database Log

    ReplyDelete
  3. Nice tips to clear magento database! Thanks for sharing! Magento Company Australia

    ReplyDelete
  4. I'm truly enjoying the design and layout of your blog. It's a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a designer to create your theme? Outstanding work!hospedagem magento

    ReplyDelete
  5. Great post however , I was wanting to know if you could write a litte more on this topic? I'd be very thankful if you could elaborate a little bit more. Appreciate it! hospedagem magento

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. India Data Entry Services is a trusted Professional Magento Product Data Entry Services provider to e-commerce companies and platforms. Be it e-commerce product cataloguing or Product Upload Services, since our inception in 2010, we are striving to get all best o cater global organizations. The company is strategically positioned to support big or small e-Commerce Stores working on various domains such as fashion apparels, crockery items, furniture, accessories, shoes.

    ReplyDelete
  8. Get the Best Magento Product upload Services From Us With the drastic facelift that the retail sector has seen in the recent past, the internet gas become the hub for most of the small, medium as well as large scale businesses that are involved in the same.The ecommerce has seen a monumental growth prospective over the last couple of years and the trend is expected to remain the same for the foreseeable future as well. In this regard, what concerns the businesses and the online retail outlet is the data entry aspect. Tech data entry India provides a number of products and services to cater to the Magento Data Entry Services.

    ReplyDelete
  9. This blog post is very interesting and useful for us clear to know how to clear Magento database after testing. I really appreciate this article, totally contain the true stuff of information. Thank you very much for sharing this vital information with us. Fantasy cricket app development

    ReplyDelete
  10. Towing Elite is offering car towing service in Portland and other areas of Oregon. Contact us for business towing, container moving, flatbed towing, air hauling and more.
    Towing Company in Portland
    Flatbed Towing near Mt St Helens
    Towing Company in Tigard
    Towing Services near Me
    Cargo Transport in Vancouver
    Tow Truck Company near Cougar

    ReplyDelete