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;


22 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
  11. 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
  12. 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
  13. Magento is helpful in building best e-commerce websites with great security and appearance. Thanks for sharing this great piece of information!!!

    best ecommerce website developers |outsource magento ecommerce services india

    ReplyDelete
  14. Your post is just outstanding! thanks for such a post,its really going great and great work.Website Development Bangalore | Web Designing Company Bangalore

    ReplyDelete
  15. Magento is one of the most popular eCommerce platform, customizable shopping cart platforms available. If you have chosen Magento as your eCommerce platform (WebSite), Acelerar can offer you end-to-end support with our efficient and streamlined Magento Bulk Product Upload Services at the most economical rates and 24x7 Support. Our Experts have a wealth of experience working on Magento Product Upload Services on eCommerce platform.

    ReplyDelete
  16. 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
  17. 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