Magento Blog - Reset test data on magento store

Reset test data on magento store

Saturday, November 7, 2009 5:01:47 PM America/Los_Angeles

Heres a quick query to purge all test data except customers.

SET FOREIGN_KEY_CHECKS=0;

TRUNCATE `catalogsearch_query`;

ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;

TRUNCATE `sales_order`;

TRUNCATE `sales_order_datetime`;

TRUNCATE `sales_order_decimal`;

TRUNCATE `sales_order_entity`;

TRUNCATE `sales_order_entity_datetime`;

TRUNCATE `sales_order_entity_decimal`;

TRUNCATE `sales_order_entity_int`;

TRUNCATE `sales_order_entity_text`;

TRUNCATE `sales_order_entity_varchar`;

TRUNCATE `sales_order_int`;

TRUNCATE `sales_order_text`;

TRUNCATE `sales_order_varchar`;

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

TRUNCATE `sendfriend_log`;

TRUNCATE `tag`;

TRUNCATE `tag_relation`;

TRUNCATE `tag_summary`;

TRUNCATE `wishlist`;

TRUNCATE `log_quote`;

TRUNCATE `report_event`;

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

ALTER TABLE `log_customer` AUTO_INCREMENT=1;

ALTER TABLE `log_quote` AUTO_INCREMENT=1;

ALTER TABLE `log_summary` AUTO_INCREMENT=1;

ALTER TABLE `log_summary_type` AUTO_INCREMENT=1;

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

ALTER TABLE `sales_order` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_datetime` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_decimal` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_entity` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_entity_datetime` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_entity_decimal` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_entity_int` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_entity_text` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_entity_varchar` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_int` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_text` AUTO_INCREMENT=1;

ALTER TABLE `sales_order_varchar` 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_order_item` 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;

3 Comments

Shaner

posted on Saturday, November 7, 2009 5:39:38 PM America/Los_Angeles

If you have to update the order numbers once you clear your test data use a mysqldump of YOURDBNAME eav_entity_store you will get something like the following INSERT INTO `eav_entity_store` VALUES (1,11,0,'1','1105'),(2,1,0,'0','0008'),(3,19,0,'1','1016'),(4,16,0,'1','1003'),(7,11,5,'5','5001'),(8,19,5,'5','5006'),(9,11,1,'1','1004'),(10,19,1,'1','1064'),(11,16,1,'1','1008'),(12,19,4,'4','4002'),(13,19,13,'13','13001'),(14,19,8,'8','8002'),(15,19,10,'10','10002'),(16,19,21,'21','21001'),(17,19,6,'6','6001'); add a truncate table eav_entity_store; update the values for what you want your order numbers to look like INSERT INTO `eav_entity_store` VALUES (1,11,0,'1','1105'),(2,1,0,'0','0008'),(3,19,0,'1','1016'),(4,16,0,'1','1003'),(7,11,5,'5','5001'),(8,19,5,'5','5006'),(9,11,1,'1','1004'),(10,19,1,'1','1064'),(11,16,1,'1','1008'),(12,19,4,'4','4002'),(13,19,13,'13','13001'),(14,19,8,'8','8002'),(15,19,10,'10','10002'),(16,19,21,'21','21001'),(17,19,6,'6','6001'); run the query truncate table eav_entity_store; INSERT INTO `eav_entity_store` VALUES (1,11,0,'1','1105'),(2,1,0,'0','0008'),(3,19,0,'1','1016'),(4,16,0,'1','1003'),(7,11,5,'5','5001'),(8,19,5,'5','5006'),(9,11,1,'1','1004'),(10,19,1,'1','1064'),(11,16,1,'1','1008'),(12,19,4,'4','4002'),(13,19,13,'13','13001'),(14,19,8,'8','8002'),(15,19,10,'10','10002'),(16,19,21,'21','21001'),(17,19,6,'6','6001'); -- Makes shorter order numbers update eav_entity_type set increment_pad_length = 4; --- Note if you have not placed an order on the site it wont be in there yet if you want to know what each data set if you will need to review the information in eav_entity_type

Shaner

posted on Friday, November 20, 2009 11:11:46 AM America/Los_Angeles

So to reset Magento customer data and Magento customer addresses from testing your magento checkout or importing data when moving to magento. you can use this 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` ;

Sean

posted on Wednesday, November 25, 2009 2:04:33 PM America/Los_Angeles

you missed some tables, the above order truncate statements should be updated with the following

TRUNCATE `sales_flat_order_item`;
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_order`;
TRUNCATE `sales_order_datetime`;
TRUNCATE `sales_order_decimal`;
TRUNCATE `sales_order_entity`;
TRUNCATE `sales_order_entity_datetime`;
TRUNCATE `sales_order_entity_decimal`;
TRUNCATE `sales_order_entity_int`;
TRUNCATE `sales_order_entity_text`;
TRUNCATE `sales_order_entity_varchar`;
TRUNCATE `sales_order_int`;
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_order_text`;
TRUNCATE `sales_order_varchar`;

Reset test data on magento store

Saturday, November 7, 2009 5:01:47 PM America/Los_Angeles

3 Comments

Shaner

Shaner

Sean

Submit Comment