Delete test orders in Magento
Posted by Tomislav Bilic under Magento @ 23rd OCT 2008

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?
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 go to MySQL admin client and run this query:
SET FOREIGN_KEY_CHECKS=0; 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`; 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; -- 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; -- Reset all ID counters TRUNCATE `eav_entity_store`; ALTER TABLE `eav_entity_store` AUTO_INCREMENT=1; SET FOREIGN_KEY_CHECKS=1;
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.
10 Responses to “Delete test orders in Magento”
Leave a Reply
Recent Posts:
- 02 Jan Contact Form in Magento
- 31 Dec Review of Zend Studio for...
- 28 Dec What is a #pragma mark?
- 22 Dec More Flexible Approach fo...
- 19 Dec Is YouTube the Future of ...
- 17 Dec CRU(EL)D: Reinventing the...
- 17 Dec Toni Anicic on Vidi Web T...
- 16 Dec 1 PSD and 2 Web Tools for...
- 12 Dec Wordpress 2.7 Is Out—an...
- 08 Dec First time working with O...
Tags:
oscommerce search Online Marketing graph iphone development project object navigation Magento theme Tools seo view marketing management class color template Google product products ajax framework tool method tutorials jquery Wordpress advertising
Recent Comments:
- Tomislav Bilic on CRU(EL)D: Reinventing the Wheel:
Hi Anshul, I guess you were speaking about this project: http://propel.phpdb.org/trac/... - Anshul on CRU(EL)D: Reinventing the Wheel:
May be using a Class generator which will generate Object class based on your DB schema can be of he... - Adam on Review of Zend Studio for Eclipse Professional Edition 6.1:
I used to use Zend Studio exclusively for all my development work. But I can't justify the price tha... - Hisham on Featured products on Magento frontpage:
Does this tutorial works with 1.2 ?... - Suomedia on What is the best shopping cart?:
Which is best really depends on the needs of the user. osCommerce has and continues to serve many us... - edulink.cz on Manual creation of Google Sitemap in Magento:
Thank you, very usefull!... - Branko Ajzele on Review of Zend Studio for Eclipse Professional Edition 6.1:
Hi Tony. Nice to hear you like the article. I still havent manage to play with Magento in it. Will... - Reddy on UPS API Quest:
Hello Ben, I need UPS API docs. I did a lot Google searches, but didn't find anywhere. So please ... - Tony on Review of Zend Studio for Eclipse Professional Edition 6.1:
Great Article! I always look forward to anything that will help me with Magento. I have been using C... - New Revolution Theme Released on Revolution 2 is launched:
Brian just launched a new theme called Streamline just the other day. Merry Christmas, it's free to...

Great post.
You save me some time with my future task.
Thanks.
Great post.
Would it be possible to delete specific orders? Say for instance, if your store had been running for a little while with the original test orders still in place and you only found the above snippet now?
Cheers,
Matt
Hello Matthew,
That was the point of this post. It is currently impossible to do it with Magento administration. You would have to go to MySQL client and delete relevant entities manually starting with sales_order* tables.
Hey Tomislav Bilic,
In the Dashboard, if u see Lifetime Sales, this show the value of urs sales yet =//
Works like a charm. Thanks for the post!
Paddy
The one attribute this code doesn’t seem to address is Shopping Cart Reports.
After running this code, if you visit backend and go to REPORTS>SHOPPING CART>PRODUCTS IN CARTS, you will see no items listed, but the counter will still list the number of test orders you’ve placed in carts.
I’d suggest the fix myself, but am unsure which table or attribute controls this value - should just be a matter of adding two lines - one to truncate, and another to auto-increment.
Correct me if I’m wrong, but don’t you want to add these tables too?
SET FOREIGN_KEY_CHECKS=0;
TRUNCATE `sales_order_tax`;
TRUNCATE `sales_flat_quote_shipping_rate`;
TRUNCATE `sales_flat_quote_payment`;
TRUNCATE `log_url_info`;
TRUNCATE `log_url`;
ALTER TABLE `sales_order_tax` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_shipping_rate` AUTO_INCREMENT=1;
ALTER TABLE `sales_flat_quote_payment` AUTO_INCREMENT=1;
ALTER TABLE `log_url_info` AUTO_INCREMENT=1;
SET FOREIGN_KEY_CHECKS=1;
Oh, and BTW, don’t worry about the PRODUCTS IN CARTS tally being wrong. It seems to be unrelated.
It looks like the wrong reporting of the total in REPORTS>SHOPPING CART>PRODUCTS IN CARTS is an issue with the current version of Magento (at least as of 1.1.6), unrelated to the above queries. I think they may have grabbed the total from the wrong table(s) in the first place. Someone could test in a fresh install with a bunch of products loaded, but no items ever added to carts.
This was pointed out in the Magento Forums, and from my research in code and DB queries, it seems to be the case.
See:
http://www.magentocommerce.com/bug-tracking/issue?issue=3899
Hey there Tomislav!
Great post here, very appreciated on behalf of the community!
We did a bit further modification by the following:
+ resetting all dashboard data
+ setting prefix’s for the following numbers: orders, invoices, shipments, and credit memo’s
Feel free to check it out here: http://eliasinteractive.com/blog/magento-ecommerce-how-to-reset-all-test-order-information-and-set-unique-prefix-for-orders-invoices-shipments-and-credit-memos/
Thanks again for your contributions!
Lee Taylor / Elias Interactive
Thanks Tomislav!!
It worked perfectly
Cheers!