Follow by Email

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;


15 comments:

  1. Great article! I think this article will support and complement your point
    Florida Web Development

    ReplyDelete
  2. Thanks for sharing this Information, Got to learn new things from your Blog on Magento.
    Ref link : http://thecreatingexperts.com/magento-training-in-chennai/

    ReplyDelete
  3. Nice artilce. I learned a lot from this.
    Keep up the good work :)

    offshore magento development

    ReplyDelete
  4. I absolutely admired every bit of it and i additionally accept you book apparent to analysis out fresh things in your site.
    Bangalore Web Designing Company, Web Development Company Bangalore

    ReplyDelete
  5. It was a great information and Its really worth reading it. The author did an mind blowing work by describing each and every concept in detail. Thanks for such an informative post. Please keep up your good work.
    Marine Colleges in Chennai, Nautical Science Colleges In Chennai

    ReplyDelete
  6. A very well-written post. I read and liked the post and have also bookmarked you. All the best for future endeavors. Getting some solution regarding.
    UI Design Company Bangalore, Web Application Development Services in Bangalore

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

    ReplyDelete
  8. Why To Approach Magento Web Development Company? Ask me Benefits of Working With Magento Web Development Magento, formed on open-source platform, is a Best feature-rich e-commerce technology with the help of which online merchants can control over the content, look and functionality of their e-commerce store. In order to give a similar kind of environment and experience to users on desktops, laptops, tablets and Smartphones an individual website is designed by Magento developers. Source-https://www.acmewebtechnology.com/blog/benefits-of-working-with-magento-web-development-service-provider

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

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

    ReplyDelete