Last active
September 18, 2016 18:17
-
-
Save versedi/c0b414fdf46423f4e8f6 to your computer and use it in GitHub Desktop.
Magento MySQL snippets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
##Clean all Customer before going Live | |
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; | |
##CLEAN DB LOGS | |
SET foreign_key_checks = 0; | |
TRUNCATE dataflow_batch_export; | |
TRUNCATE dataflow_batch_import; | |
TRUNCATE log_customer; | |
TRUNCATE log_quote; | |
TRUNCATE log_summary; | |
TRUNCATE log_summary_type; | |
TRUNCATE log_url; | |
TRUNCATE log_url_info; | |
TRUNCATE log_visitor; | |
TRUNCATE log_visitor_info; | |
TRUNCATE log_visitor_online; | |
TRUNCATE report_viewed_product_index; | |
TRUNCATE report_compared_product_index; | |
TRUNCATE report_event; | |
TRUNCATE index_event; | |
TRUNCATE catalog_compare_item; | |
SET foreign_key_checks = 1; | |
#Clean All products | |
SET FOREIGN_KEY_CHECKS=0; | |
-- This is to delete all products | |
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_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`; | |
TRUNCATE `catalog_product_super_attribute_label`; | |
TRUNCATE `catalog_product_super_attribute_pricing`; | |
TRUNCATE `catalog_product_super_link`; | |
TRUNCATE `catalog_product_enabled_index`; | |
TRUNCATE `catalog_product_website`; | |
TRUNCATE `catalog_product_entity`; | |
TRUNCATE `cataloginventory_stock`; | |
TRUNCATE `cataloginventory_stock_item`; | |
TRUNCATE `cataloginventory_stock_status_idx`; | |
TRUNCATE `cataloginventory_stock_status_tmp`; | |
TRUNCATE `cataloginventory_stock_status`; | |
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 `catalogsearch_query`; | |
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1; | |
TRUNCATE `catalogsearch_fulltext`; | |
ALTER TABLE `catalogsearch_fulltext` AUTO_INCREMENT=1; | |
ALTER TABLE `catalog_product_entity` AUTO_INCREMENT=1; | |
SET FOREIGN_KEY_CHECKS=1; | |
# Clean All categories and insert default ones. | |
SET FOREIGN_KEY_CHECKS = 0; | |
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'); | |
SET FOREIGN_KEY_CHECKS = 1; | |
#CLEAN 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_payment_transaction`; | |
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_payment_transaction` 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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment