Magento is a powerful eCommerce system. Housing 333+ tables in the database and over 14,000 files, it’s a big beast that often times is very difficult to work with. One thing I noticed since I started working with it is that it’s very difficult to reset the database without wiping out key data. While setting it up, we often will create test products and test categories that we later do not need. The same with testing the checkout system to make sure it works. When you’re done with all the testing, you’re left with a lot of extra data that is pretty much useless.
Another issue I found was the Order Numbers. By default Magento starts at 10000000001 (or similar – I forget exactly), which is rather unsightly, and oftentimes clashes with existing order numbers.
Due to a recent project, I knew how to delete all the products and categories. After a bit of Googling, I was able to find the SQL statements to clear out test orders, along with deleting all the products and what not. I found this over at WABISM.COM (Magento: Clear All Orders and Customers). In addition I found the code to reset the order numbers (in addition to the length) at Mactimize (Change length of order numbers in Magento). After combining these into a single script, I decided to take it a step further.
Again, in a recent project, there were many times I needed to clear the products, but not the categories, or the groups but not the users. Using the single script that clear all test data didn’t help as well here, so I ended up with multiple scripts. This became annoying so I ended up creating a single script that will handle all this based off variables defined in the script. What I ended up with is the script listed below. The script generates a few Stored Procedures to be able to process MySQL IF Statements which are deleted when the script is done with them. IF statements are different from the inline IF() function that is ran on queries and as a result can only be ran within Stored Procedures and Functions.
I then use SET to declare variables that I can easily change to how the script processes. I can easily clear categories and not products, or I can clear orders and not customers, etc. In addition to this, I can reset the Magento order number without any hassle.
What makes it really nice though I don’t have to worry about it the table prefix. Magento is sometimes installed using a table prefix. Often times you would have to update every SQL Statement with the prefix. With this script, I can clear the test data without having to do that. I’ve built in the @TABLE_PREFIX variable to handle changing prefixes.
All you have to do is save it as a SQL Dump File (IE: Clear.sql) and then ‘Import’ it into the database. In SQLYog this is called ‘Restore from SQL Dump…’
Make sure to back up your DB before running the Script!!
I’ve not had the chance to test this on every scenario, so I can’t say if I’ve got glitches or not.
To The Story
-- Control Variables --
-- -Change These To Change The Script Execution- --
SET @TABLE_PREFIX = '';
SET @CLEAR_PRODUCTS = FALSE;
SET @CLEAR_CATEGORIES = FALSE;
SET @CLEAR_CUSTOMERS = FALSE;
SET @CLEAR_GROUPS = FALSE;
SET @CLEAR_ORDERS = FALSE;
SET @STARTING_ORDER = 1;
SET @ORDER_LENGTH = 10;
SET @ORDER_PREFIX = 1;
SET @DEFAULT_GROUP = 1;
/*
==============================================
====================NOTES=====================
This script uses procedures to run. It
creates the procedures due to the IF
statements not being able to be ran outside of
a procedure or function (They are not inline
statements). Once the procedure has been ran
passing the nessicary variable from above,
the procedure is removed. A check is also done
to remove the procedure if already exists.
==
Delimiter Is Used To Change The EOL Symbol(s - Normally Semi-Colon [;])
so the Create Procedures Are Entered as a single Query
==============================================
*/
-- Magento Clear Entries --
-- Removing Checks --
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8;
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0;
DROP PROCEDURE IF EXISTS clearTable;
DELIMITER //
CREATE PROCEDURE clearTable(strTable VARCHAR(100))
BEGIN
CALL runQuery('TRUNCATE TABLE', strTable, '');
END //
DELIMITER ;
DROP PROCEDURE IF EXISTS runQuery;
DELIMITER //
CREATE PROCEDURE runQuery(strPrefix VARCHAR(100), strTable VARCHAR(100), strSuffix VARCHAR(500))
BEGIN
SET @QUERY = CONCAT(strPrefix, ' `', strTable, '` ', strSuffix, ';');
PREPARE QRY FROM @QUERY;
EXECUTE QRY;
END //
DELIMITER ;
-- Reset Products --
DROP PROCEDURE IF EXISTS clearProducts; -- Error Protection
DELIMITER //
CREATE PROCEDURE clearProducts(blnClear BOOLEAN)
BEGIN
IF blnClear = TRUE THEN
-- Clear Product Tables --
CALL clearTable('catalog_product_bundle_option');
CALL clearTable('catalog_product_bundle_option_value');
CALL clearTable('catalog_product_bundle_selection');
CALL clearTable('catalog_product_entity_datetime');
CALL clearTable('catalog_product_entity_decimal');
CALL clearTable('catalog_product_entity_gallery');
CALL clearTable('catalog_product_entity_int');
CALL clearTable('catalog_product_entity_media_gallery');
CALL clearTable('catalog_product_entity_media_gallery_value');
CALL clearTable('catalog_product_entity_text');
CALL clearTable('catalog_product_entity_tier_price');
CALL clearTable('catalog_product_entity_varchar');
CALL clearTable('catalog_product_link');
CALL clearTable('catalog_product_link_attribute');
CALL clearTable('catalog_product_link_attribute_decimal');
CALL clearTable('catalog_product_link_attribute_int');
CALL clearTable('catalog_product_link_attribute_varchar');
CALL clearTable('catalog_product_link_type');
CALL clearTable('catalog_product_option');
CALL clearTable('catalog_product_option_price');
CALL clearTable('catalog_product_option_title');
CALL clearTable('catalog_product_option_type_price');
CALL clearTable('catalog_product_option_type_title');
CALL clearTable('catalog_product_option_type_value');
CALL clearTable('catalog_product_super_attribute');
CALL clearTable('catalog_product_super_attribute_label');
CALL clearTable('catalog_product_super_attribute_pricing');
CALL clearTable('catalog_product_super_link');
CALL clearTable('catalog_product_enabled_index');
CALL clearTable('catalog_product_website');
CALL clearTable('catalog_product_entity');
-- Clearing Inventory --
CALL clearTable('cataloginventory_stock');
CALL clearTable('cataloginventory_stock_item');
CALL clearTable('cataloginventory_stock_status');
-- Add Magento Required Fields --
CALL runQuery('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_type`(`link_type_id`,`code`) VALUES (1,'relation'),(2,'bundle'),(3,'super'),(4,'up_sell'),(5,'cross_sell');
CALL runQuery('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 `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');
CALL runQuery('INSERT INTO', 'cataloginventory_stock', '(`stock_id`,`stock_name`) VALUES (1,''Default'')');
-- INSERT INTO `cataloginventory_stock`(`stock_id`,`stock_name`) VALUES (1,'Default');
END IF;
END //
DELIMITER ;
CALL clearProducts(@CLEAR_PRODUCTS);
DROP PROCEDURE clearProducts; -- No Longer Needed
-- Reseting Categories --
DROP PROCEDURE IF EXISTS clearCategories; -- Error Protection
DELIMITER //
CREATE PROCEDURE clearCategories(blnClear BOOLEAN)
BEGIN
IF blnClear = TRUE THEN
-- Clear Category Tables --
CALL clearTable('catalog_category_entity');
CALL clearTable('catalog_category_entity_datetime');
CALL clearTable('catalog_category_entity_decimal');
CALL clearTable('catalog_category_entity_int');
CALL clearTable('catalog_category_entity_text');
CALL clearTable('catalog_category_entity_varchar');
CALL clearTable('catalog_category_product');
CALL clearTable('catalog_category_product_index');
-- Inserting Default Category --
CALL runQuery('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)");
END IF;
END //
DELIMITER ;
CALL clearCategories(TRUE);
DROP PROCEDURE clearCategories; -- No Longer Needed
-- Reset Customer Data --
DROP PROCEDURE IF EXISTS clearCustomers; -- Error Protection
DELIMITER //
CREATE PROCEDURE clearCustomers(blnClear BOOLEAN, blnGroups BOOLEAN)
BEGIN
IF blnClear = TRUE THEN
-- Clear Customer Tables --
CALL clearTable('customer_address_entity');
CALL clearTable('customer_address_entity_datetime');
CALL clearTable('customer_address_entity_decimal');
CALL clearTable('customer_address_entity_int');
CALL clearTable('customer_address_entity_text');
CALL clearTable('customer_address_entity_varchar');
CALL clearTable('customer_entity');
CALL clearTable('customer_entity_datetime');
CALL clearTable('customer_entity_decimal');
CALL clearTable('customer_entity_int');
CALL clearTable('customer_entity_text');
CALL clearTable('customer_entity_varchar');
-- Clear Logs --
CALL clearTable('log_customer');
CALL clearTable('log_visitor');
CALL clearTable('log_visitor_info');
ELSEIF blnGroups = TRUE THEN
-- Reset Group IDs If Groups Are Getting Cleared --
CALL runQuery('UPDATE', 'customer_entity', ' SET group_id = @DEFAULT_GROUP');
-- UPDATE customer_entity SET group_id = 0;
END IF;
END //
DELIMITER ;
CALL clearCustomers(@CLEAR_CUSTOMERS, @CLEAR_GROUPS);
DROP PROCEDURE clearCustomers; -- No Longer Needed
-- Clearing Group Data --
DROP PROCEDURE IF EXISTS clearGroups; -- Error Protection
DELIMITER //
CREATE PROCEDURE clearGroups(blnClear BOOLEAN)
BEGIN
IF blnClear = TRUE THEN
-- Clear Group Table --
CALL clearTable('customer_group');
-- Insert Default Groups --
CALL runQuery('INSERT INTO', 'customer_group', '(`customer_group_code`, `tax_class_id`) VALUES (''Member'', 3)');
CALL runQuery('INSERT INTO', 'customer_group', '(`customer_group_code`, `tax_class_id`) VALUES (''NOT LOGGED IN'', 3)');
--RESET 'NOT LOGGED IN' To 0 ID
CALL runQuery('UPDATE', 'customer_group', 'SET customer_group_id = 0 WHERE customer_group_id = 2');
END IF;
END //
DELIMITER ;
CALL clearGroups(@CLEAR_GROUPS);
DROP PROCEDURE clearGroups; -- No Longer Needed
-- Clearing Order Data --
DROP PROCEDURE IF EXISTS clearOrders; -- Error Protection
DELIMITER //
CREATE PROCEDURE clearOrders(blnClear BOOLEAN, blnCustomers BOOLEAN)
BEGIN
IF blnClear = TRUE THEN
CALL clearTable('sales_flat_order');
CALL clearTable('sales_flat_order_address');
CALL clearTable('sales_flat_order_grid');
CALL clearTable('sales_flat_order_item');
CALL clearTable('sales_flat_order_status_history');
CALL clearTable('sales_flat_quote');
CALL clearTable('sales_flat_quote_address');
CALL clearTable('sales_flat_quote_address_item');
CALL clearTable('sales_flat_quote_item');
CALL clearTable('sales_flat_quote_item_option');
CALL clearTable('sales_flat_order_payment');
CALL clearTable('sales_flat_quote_payment');
CALL clearTable('sales_flat_shipment');
CALL clearTable('sales_flat_shipment_item');
CALL clearTable('sales_flat_shipment_grid');
CALL clearTable('sales_flat_invoice');
CALL clearTable('sales_flat_invoice_grid');
CALL clearTable('sales_flat_invoice_item');
CALL clearTable('sendfriend_log');
CALL clearTable('tag');
CALL clearTable('tag_relation');
CALL clearTable('tag_summary');
CALL clearTable('wishlist');
CALL clearTable('log_quote');
CALL clearTable('report_event');
CALL runQuery('UPDATE', 'eav_entity_type', 'SET `increment_pad_length` = (@ORDER_LENGTH - 1) WHERE `entity_type_code` = ''order'' LIMIT 1');
-- UPDATE `eav_entity_type` SET `increment_pad_length` = (@ORDER_LENGTH - 1) WHERE `entity_type_code` = 'order' LIMIT 1 ;
CALL runQuery('UPDATE', 'eav_entity_store', 'SET `increment_last_id` = @ORDER_START, `increment_prefix` = @ORDER_PREFIX WHERE `entity_store_id` = 1 LIMIT 1');
-- UPDATE `eav_entity_store` SET `increment_last_id` = @ORDER_START, `increment_prefix` = @ORDER_PREFIX WHERE `entity_store_id` = 1 LIMIT 1 ;
ELSEIF blnCustomers = TRUE THEN
CALL runQuery('UPDATE', 'sales_flat_order', 'SET `customer_id` = (NULL)');
END IF;
END //
DELIMITER ;
CALL clearOrders(@CLEAR_ORDERS, @CLEAR_CUSTOMERS);
DROP PROCEDURE clearOrders; -- Don't Need It Anymore
DROP PROCEDURE clearTable;
DROP PROCEDURE runQuery;
--Restoring Checks--
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET SQL_NOTES=@OLD_SQL_NOTES;