Delete test orders in Magento

You got a Magento project to develop, you created a Magento theme, you placed initial products and categories and you also placed some test orders to see if Shipping and Payment methods work as expected. Everything seems to be cool and the client wishes to launch the site. You launch it. When you enter the administration for the first time after the launch, you will see all your test orders there. You know those should be deleted. But how?
This is a new version of the same article.
If you try to delete orders in the backend, you will find out that you can only set the status to “cancelled” and the order is still there. Unfortunately, Magento doesn’t enable us to delete those via administration, so you will not see any “Delete order” button. This can be quite frustrating both to developers and the merchants. People coming from an SAP world find the inability to delete to have some merit but there should be a status that removes the sales count from the reports i.e. sales, inventory, etc.
So, what to do?
You need to create php script in Magento root folder, copy this code into script and run script.
/**
* @author Dejan Radic <dejan.radic@inchoo.net>
*/
if (version_compare(phpversion(), '5.2.0', '<')===true) {
echo '<div style="font:12px/1.35em arial, helvetica, sans-serif;"><div style="margin:0 0 25px 0; border-bottom:1px solid #ccc;"><h3 style="margin:0; font-size:1.7em; font-weight:normal; text-transform:none; text-align:left; color:#2f2f2f;">Whoops, it looks like you have an invalid PHP version.</h3></div><p>Magento supports PHP 5.2.0 or newer. <a href="http://www.magentocommerce.com/install" target="">Find out</a> how to install</a> Magento using PHP-CGI as a work-around.</p></div>';
exit;
}
error_reporting(E_ALL | E_STRICT);
ini_set('display_errors', 1);
$mageFilename = 'app/Mage.php';
if (!file_exists($mageFilename)) {
echo $mageFilename." was not found";
exit;
}
require_once $mageFilename;
Mage::app();
$executionPath = null;
/*
* determine Magento Edition
*/
if (file_exists('LICENSE_EE.txt')) {
$edition = 'EE';
}elseif (file_exists('LICENSE_PRO.html')) {
$edition = 'PE';
} else {
$edition = 'CE';
}
if(($edition=='EE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
|| ($edition=='PE' && version_compare(Mage::getVersion(), '1.11.0.0.', '<')===true)
|| ($edition=='CE' && version_compare(Mage::getVersion(), '1.6.0.0.', '<')===true)
){
$executionPath = 'old';
} else {
$executionPath = 'new';
}
$xpathEntity = 'global/models/sales_entity/entities//table';
if ($executionPath == 'old') {
$xpathResource = 'global/models/sales_mysql4/entities//table';
} else {
$xpathResource = 'global/models/sales_resource/entities//table';
}
$salesEntitiesConf = array_merge(
Mage::getSingleton('core/config')->init()->getXpath($xpathEntity),
Mage::getSingleton('core/config')->init()->getXpath($xpathResource)
);
$resource = Mage::getSingleton('core/resource');
$connection = $resource->getConnection('core_write');
/*
* If you want delete System/Order Statuses (Status and State) you
* should comments below lines (46-51)
*/
$skipTables = array (
$resource->getTableName('sales_order_status'),
$resource->getTableName('sales_order_status_state'),
$resource->getTableName('sales_order_status_label')
);
$salesEntitiesConf = array_diff($salesEntitiesConf, $skipTables);
/*
Multiple RDBMS Support in Magento CE 1.6+ / EE 1.11+
http://www.magentocommerce.com/images/uploads/RDBMS_Guide2.pdf
2.2. Adapters:
... The new Varien_DB_Adapter_Interface was added to sign a contract that all
developed adapters must execute in order to get Magento working on an actual
database. The interface describes the list of methods and constants that can be used by resource models...
Used below in the loop:
* If $executionPath == 'old'
* Varien_Db_Adapter_Pdo_Mysql::showTableStatus()
* Varien_Db_Adapter_Pdo_Mysql::truncate()
* Else
* Varien_Db_Adapter_Interface::isTableExists()
* Varien_Db_Adapter_Interface::truncateTable()
*/
while ($table = current($salesEntitiesConf) ){
$table = $resource->getTableName($table);
if ($executionPath == 'old') {
$isTableExists = $connection->showTableStatus($table);
} else {
$isTableExists = $connection->isTableExists($table);
}
if ($isTableExists) {
try {
if ($executionPath == 'old') {
$connection->truncate($table);
} else {
$connection->truncateTable($table);
}
printf('Successfully truncated the <i style="color:green;">%s</i> table.<br />', $table);
} catch(Exception $e) {
printf('Error <i style="color:red;">%s</i> occurred truncating the <i style="color:red;">%s</i> table.<br />', $e->getMessage(), $table);
}
}
next($salesEntitiesConf);
}
exit('All done...');
After you have it executed, the test orders will not be in the database any more. Keep in mind that this will delete ALL orders, in the database. So, you should execute this queries immediately after launch.
This script is tested on following Magento versions: 1.6.1.0 CE, 1.6.0.0 CE, 1.5.1.0. CE, 1.11.0.2 EE, 1.10.0.2 EE, 1.9.0.0 EE, 1.11.0.0 PE, 1.10.0.2 PE and 1.9.0.0 PE.
You can download script here.
194 comments
worked perfectly for me with, tks!
Awesome Post !
Used with Magento 1.9. Simple installation and effective solution to delete orders. Great tool!
I added also
after truncate, just for sake of completeness.
To avoid ‘1701 Cannot truncate a table referenced in a foreign key constraint’ issue in above script, you can add (to disable foreign check)
after connection object is created. And add below at end.(to enable the foreign check)
I’ve found also an easier way to delete test orders, without using PHP scripts. The steps are described in this article: https://www.mag-manager.com/useful-articles/magento-orders-management/how-to-delete-magento-test-orders/
Cheeky bastard!
work for mebut how to reset lifetime sales and all dashboard
Hello.
I really want to use that code but i have no idea how to use the code
thank you
All ok to remove all orders. I’ve maked a test with new order and invoiced it’s all right.
Magento Version 1.9.0.1 😉
Thanks.
Very useful. don’t really like to have them.
Any hint for how to delete an individual order in Magento once the Mage:: is initialized?
Sales->order data will delete but On Dashboard, Lifetime Sales and Average Order values are available
Why?
Try Below script.. its working for me..
===========
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE catalogsearch_query;
ALTER TABLE catalogsearch_query AUTO_INCREMENT=1;
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_order_item;
TRUNCATE sendfriend_log;
TRUNCATE tag;
TRUNCATE tag_relation;
TRUNCATE tag_summary;
TRUNCATE wishlist;
TRUNCATE log_quote;
TRUNCATE report_event;
TRUNCATE tag_relation;
TRUNCATE tag_summary;
TRUNCATE wishlist;
TRUNCATE log_quote;
TRUNCATE report_event;
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_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;
TRUNCATE eav_entity_store;
ALTER TABLE eav_entity_store AUTO_INCREMENT=1;
INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’1\’, \’11\’, \’1\’, \’1\’, \’000000000\’);
update eav_entity_store’ set ‘increment_prefix’= 1 where ‘entity_type_id’=\’5\’ and ‘store_id’=\’1\;
update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’4\’ and ‘store_id’=\’1\;
INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’2\’, \’16\’, \’1\’, \’2\’, \’000000000\’);
update eav_entity_store’ set ‘increment_prefix’= 2 where ‘entity_type_id’=\’6\’ and ‘store_id’=\’1\;
update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’18\’ and ‘store_id’=\’1\;
INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’3\’, \’19\’, \’1\’, \’3\’, \’000000000\’);
update eav_entity_store’ set ‘increment_prefix’= 3 where ‘entity_type_id’=\’8\’ and ‘store_id’=\’1\;
update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’24\’ and ‘store_id’=\’1\;
INSERT INTO ‘YOUR-DATABASE-NAME’.eav_entity_store’ (‘entity_store_id’ ,’entity_type_id’ ,’store_id’ ,’increment_prefix’ ,’increment_last_id’) VALUES(\’4\’, \’23\’, \’1\’, \’4\’, \’000000000\’);
update eav_entity_store’ set ‘increment_prefix’= 4 where ‘entity_type_id’=\’7\’ and ‘store_id’=\’1\;
update eav_entity_store’ set ‘increment_last_id’= \’000000000\’ where ‘entity_type_id’=\’28\’ and ‘store_id’=\’1\;
SET FOREIGN_KEY_CHECKS=1;
=============
can’t able to delete orders in magento-1.9.0.1
dont forget to truncate the table downloadable_link_purchased too.
This works with 1.8.1, but you first need to alter the code like Zaour said in an earlier comment.
It worked for me .. i ‘m using 1.7.0.2.. 🙂 🙂
Seems to cause issues with CE 1.8.1.0. Invoices are no longer created correctly following running this code.
It worked for me. Im using magento CE 1.7.0.2
Thanks.
works fine on 1.7.0.2 expets deltes the status of the orders..
Hat super funktioniert in 1.8.!
dankedankedanke!
Perfect solution! Simple and fast! Downloaded from link at the end of the post and Worked like a charm with no editings. Just uploaded (magento root folder) and executed it.
Thank you for sharing this.
Used the download and worked perfectly for me with 1.7.0.2 – much appreciated – Thks
Hi,
There is an extension that can automaticallly delete an order or range of orders at once.
http://www.magentocommerce.com/magento-connect/catalog/product/view/id/19988/
Dejan Radic, Is this code compatible with 1.7.0.2?
Hi again, Okay i just took the plunge and run the script and adding the reset order numbers and invoice numbers mentioned by Darko Goles.
But this still did not work, running magento version 1.7.0.2.. any help would be appreciated.
thanks
Hi, i’ve used your script to delete my test orders, but have slight problem, i have a live order and when i try and process the invoice for this i get the error,
Unable to save the invoice.
I looked at my orders and i just have the 1 order, but looking in the database, under eav_entity_store
I have some of my test order and the new order.. Looks like i had to reset the order numbers and the invoice numbers.. can anyone help me as how i can do this without loosing my 1 order details..
thanks.
Work for me, Darko Goles. I meet the foreign key problem all of time. You help me save much time since I don’t have to delete many tables in many times. Thanks
For anybody that have FOREIGN KEY check errors, just add this before truncating tables:
And then on the end of the file we could add:
And sometimes is neccesary to reset order numbers, invoice numbers etc. after deleting this, so before end of the file we can add:
Thank you Darko Goles, script with your fix works like a charm!
Thanks Dean,
great script.. helped me with test orders!
cheers from Zagreb
Anyone tested this with CE version 1.7.0.2?
Hello,
I use magento 1.7.0.2 and I’ve got error messages like this:
“Error SQLSTATE[42000]: Syntax error or access violation: 1701 Cannot truncate a table referenced in a foreign key constraint (`database`.`sales_flat_quote_address`, CONSTRAINT `FK_ZA_SALES_FLAT_QUOTE_ADDR_QUOTE_ID_ZA_SALES_FLAT_QUOTE_ENTT_ID` FOREIGN KEY (`quote_id`) REFERENCES `database`.`sales_flat_quote` (`entity_) occurred truncating the sales_flat_quote table.”
To solve it we need to execute “SET FOREIGN_KEY_CHECKS = 0;” before and “SET FOREIGN_KEY_CHECKS = 1;” after the truncation commands.
For this reason I’ve modified this script at lines 97 and 124 and added follow lines before and after “while” :
$connection->query(“SET FOREIGN_KEY_CHECKS = 0”);
while ($table = current($salesEntitiesConf) ){
….
}
$connection->query(“SET FOREIGN_KEY_CHECKS = 1”);
Regards
Zaour
Worked on 1.7 CE successfully.
Thank you!
Awesome script!
Just had a chance to use it and worked like a charm.
If in case the script is executed accidently in the live environment it will delete live orders, so it’s good to put the following line of code at the last of the script:
which will delete the script file once it is run.
Cheers!
Hey Dejan! I appreciate your kindness to help people getting rid of the terrible nightmare (yes nightmare) of having to erase test orders in Magento. You surely are a great programmer since you produced that great script! But I have to say I’m too lazy, you know, I allways look for an easier way for getting things done. So I didn’t use your script but I user this extension: Seamless Delete Order. Thank you anyway for the good will!
Hi,
Script works successfully. But it truncates status related also. So it will create problems in order status. @Dejan Radic, pls change the script. If any one face like this problem, you can reset the following tables from any other magento installation. 1. sales_order_status 2.sales_order_status_state 3.sales_order_status_label. thanks guys 🙂
After I decompile work some days and it is die again, so my experience is keep compile disable to keep all of extensions work well together 🙂
It ‘ the way it is IMKate, it should do like that every time you install new extension for magento
Today, I tried to decompile and then compile again but it make my site down without any error show. After I delete cache folder and then delete this code, it work well again. Any idea why?
Work well for me with deleting order in Magento 1.6.1. Thanks Dejan Radic
Great script man, it helped me alot. Great job here at inchoo.net. Keep it up.
Hm i have some errors when I receive new sales.
After using the script it always troubles with Integrity constraint violations.
Any experience with this?
You should not kill the Foreign key constraints, they are there to preserve integrity. The better idea is to run the queries in test mode and reorganize all the queries to delete the children first.