Magento eCommerce: How To Reset All Test Order Information and Set Unique Prefix For Orders, Invoices, Shipments, and Credit Memos

About: How To Reset Test Orders, Dashboard Information, and Set Unique Starting Values for Orders, Invoices, Shipments, and Credit Memos

Who’s Interested: Informative to the technical gurus
What: Plug ‘n Play Database Script that runs queries to customize after store is installed and product(s) have been created
Magento Version Relevence: 1.1.6
Magento Store Setup: Single-Store Setup

A client of ours went in to test products, orders, invoices, etc. to get the feel for the overall “purchase order to shipment” process from start to finish. After creating many test users, orders, etc. - they now desire to have everything reset back to “0″ (zero).

Not only that, but they also want to separate the prefixes for the following items just for ease of dissimilarity when using the Magento ADMIN tools:

+ Orders (set prefix to begin with “1″)
+ Invoices (set prefix to begin with “2″)
+ Shipments (set prefix to begin with “3″)
+ Credit Memo’s (set prefix to begin with “4″)

Thus, we have the following script (and a special thanks to Tomislav at Inchoo for getting us started!)

Instructions:

Find “YOUR_DB_NAME” down below and change to match your database name (though leave the ticks “)

Copy and paste code into SQL manager (PhpMyAdmin or similar) and then run the query

Feel free to Create test order, invoice, shipment, and credit memo’s via the ADMIN (easier than going through order process on frontend)

Once you like the changes you see, go back and run the same SQL query

…the following will now be in effect:
+ All data pertaining to orders and search terms (Dashboard) are reset to 0 (zero)
+ Order number prefix with 0, Invoice number prefix with 2, Shipment number prefix with 3, Credit Memo number prefix with 4

Please feel free to copy and paste into your PhpMyAdmin


-- Reset Magento TEST Data

SET FOREIGN_KEY_CHECKS=0;

-- reset dashboard search queries
TRUNCATE `catalogsearch_query`;
ALTER TABLE `catalogsearch_query` AUTO_INCREMENT=1;

-- reset sales order info
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 all ID counters
TRUNCATE `eav_entity_store`;
ALTER TABLE  `eav_entity_store` AUTO_INCREMENT=1;

SET FOREIGN_KEY_CHECKS=1;

-- set appropriate prefixes for orders, invoices, shipments, credit memos
INSERT INTO  `YOUR_DB_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`='4' 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_DB_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`='18' 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_DB_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`='24' 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_DB_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`='28' and `store_id`='1';
update `eav_entity_store` set `increment_last_id`= '000000000' where `entity_type_id`='28' and `store_id`='1';

For SQL Script download: Magento DB Modifications

Let us know how you like it!

Thanks,
Lee Taylor

34 Comments

34 Comments »

Eric Clark says:

November 16th, 2008 at 8:24 pm
Great post, Lee. I’m sure many will find this useful in the Magento community!
Marty says:

November 17th, 2008 at 11:49 am
Thanks for the script Lee. I found a typo in your script.

After you “TRUNCATE `eav_entity_store`; ” you are doing an update on that table based on an ‘entity_type_id’ that does not exist. The last eight update statements do nothing.

Also, it should be noted that this script assumes a single store setup. If you have multiple stores you would need to add additional insert statements with proper store_id.

Thanks again for the script.
Joy says:

November 17th, 2008 at 12:12 pm
Why not use letters instead of numbers for the various prefixes? The table is set up to be a varchar already.
Marty says:

November 17th, 2008 at 2:14 pm
One more thing. I’ve been messing around in that part of the system today and I think you’ll definitely want to remove the “TRUNCATE `eav_entity_store`;” statement. The reason is; that table also holds counters for other parts of the system, such as customer number sequence. If you truncate it you may end up breaking other parts of Magento. Probably better to do updates only on that table.
timothyleetaylor says:

November 17th, 2008 at 4:18 pm
Hi @Marty,

In reference to your first post: you are correct in seeing that this is assumed for a single-store Magento setup. We could go in and reconfigure some of the values to be modified for multiple stores.

Also, with the TRUNCATE command, we don’t actually delete the table, but all the information in it. We then, in the last 8 commands (check lines 79, 83, 87, 91) create values inside that table to be modified. The first statement clears the table, and the second set of statements inserts the structure to be modified.

As for using the TRUNCATE on that table, you may be correct in that it may be more safe to simply update on that table. What errors have you encountered by removing the customer number sequence?

Thanks for your thoughts!
Lee
timothyleetaylor says:

November 17th, 2008 at 4:19 pm
@Joy,

The reason we used numbers was simply by client’s request. You can go ahead and insert letters as the datatype is varchar-appropriate.

Thanks for mentioning the possibility!
- Lee
Jesse says:

November 17th, 2008 at 4:33 pm
Thanks for the script Lee. In regard to the posts concerned with Truncating the table, have you experienced any problems yourself with live sites after having used this script? We plan on launching a site tomorrow and have yet to give your script a go. Would you still advise using it as is? Just hoping we won’t run into any problems along the way..

Many thanks.
timothyleetaylor says:

November 17th, 2008 at 5:54 pm
Hi Jesse,

This script should work correctly for you, as we’ve tested it on a couple sites thus far, though recently. Do you have anyone familiar with SQL code? If so, let them walk you through it and make you feel more comfortable with it.

As always when modifying database data, I would be sure to create a backup via Magento tools in the ADMIN or via your SQL interface such as PhpMyAdmin.

In regards to the concerns posted, this only involves using this script after the site has been acquired various customer accounts, thus possibly effecting the numbering sequence.

So if you do have customer accounts you’d like to keep, it may be wise to wait for an updated script that I’ll try and get onto as time allows. Otherwise, please feel free to contact us with any needs you may have regarding web development, design, or marketing - and we’d be glad to assist you!

Thanks,
Lee
Jesse says:

November 17th, 2008 at 7:01 pm
Thanks for the quick reply. I don’t need to keep any accounts so I’ll give it a shot tomorrow.
Yaani says:

November 18th, 2008 at 2:56 pm
I tried this & it seemed to delete all the test orders but I also got this error

SQL query:

INSERT INTO `mystoredatabase`.`eav_entity_store` (
`entity_store_id` ,
`entity_type_id` ,
`store_id` ,
`increment_prefix` ,
`increment_last_id`
)
VALUES (
‘3′, ‘19′, ‘1′, ‘3′, ‘000000000′
);

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`modsnroses/eav_entity_store`, CONSTRAINT `FK_eav_entity_store_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Can you tell me if there is anything else I need to do in regard to this error?

Thanks!
Yaani says:

November 18th, 2008 at 9:26 pm
I think I found the answer to my error problem above here
http://www.magentocommerce.com/wiki/restoring_a_backup_of_a_magento_database

Thanks!
Yaani says:

November 18th, 2008 at 9:39 pm
No that didn’t fix the following error

SQL query:

INSERT INTO `modsnroses`.`eav_entity_store` (
`entity_store_id` ,
`entity_type_id` ,
`store_id` ,
`increment_prefix` ,
`increment_last_id`
)
VALUES (
‘3′, ‘19′, ‘1′, ‘3′, ‘000000000′
);

MySQL said: Documentation
#1452 - Cannot add or update a child row: a foreign key constraint fails (`modsnroses/eav_entity_store`, CONSTRAINT `FK_eav_entity_store_entity_type` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Should I just try to create those last two table entries?

It seems the first two tables are created
1 11 1 1 000000000
2 16 1 2 000000000
timothyleetaylor says:

November 19th, 2008 at 2:55 pm
Hi @Yaani,

Thanks for your interest on this SQL script. Can you verify that you’re using Magento 1.1.6? The db structure is necessary for this script to be applicable. If you’re using a lower version, feel free to go into `eav_entity_type` and see what values are for orders, invoices, shipments, and credit memo’s. They may have a different value for `entity_type_id`.

I’m thinking an indifference there may be what’s causing that kind of error.

Thanks!
Lee
Yaani says:

November 19th, 2008 at 8:02 pm
It’s Magento 1.1.6

I went into the database & looked at the eav_intity_type table & it has two entries that contain the following data

1 11 1 1 000000000
2 16 1 2 000000000
yaani says:

November 21st, 2008 at 10:28 am
I was able to get rid of the mysql error by moving the line of code “SET FOREIGN_KEY_CHECKS=1;” to the end of the sql query & everything worked great! Thanks!
Kev says:

November 23rd, 2008 at 11:06 pm
Thanks! Works great for 1.1.6! I’m about to upgrade though, so I need to know if anyone has tried this with 1.1.7?
Matt Albiniak says:

November 25th, 2008 at 9:21 am
Great work, Lee. Thanks for sharing this with the community.
David says:

November 26th, 2008 at 8:29 am
I’m completely new to databases I open phpmyadmin and its like looking at the cockpit of a 747, but clearing out my test orders is something I really want to do. When I select my database in phpMyadmin and then select query paste the code with my database name in the SQL query on database XXXXX: it brings up the message: “You have to choose at least one column to display”. So, I’m lost, what am I missing?
Eric Clark says:

November 26th, 2008 at 8:40 am
Hi David, would you mind posting the exact error message from phpmyadmin? Also make sure you find/replace all instances of YOUR_DB_NAME in that query.
David says:

November 26th, 2008 at 9:12 am
Yes, I have replaced all instances of “YOUR_DB_NAME” and the error message is:
“You have to choose at least one column to display”

I know this is operator error. Here is a little more data, The screen I’m looking at is the Query tab of my database. There are 3 areas to work in. There is a box up top with the pulldowns field, and sort. A checkbox for show. A set of fields titled Criteria:. More checkboxes for ins:,Del:, And:, and Or: etc. The second box is “Use Tables”, I assume that I select all of them. The third box is “SQL query on database XXXXX: this is where I post the code.

Thanks for the help
David says:

November 26th, 2008 at 9:20 am
A little more progress, I was selecting the wrong tab. It should have been the SQL tab. But its not over yet. now the error is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘magento’ at line 1
timothyleetaylor says:

November 30th, 2008 at 12:51 am
Hi @David,

I would recommend you start by Googling “How to run SQL query in PHPMyAdmin” or something similar. Then come back once you get the basics down.

This is the best initial step for both of us before trying to troubleshoot with you I believe.

Cheers!
Lee
Boaz says:

December 7th, 2008 at 2:51 pm
Hi Lee,

The script is running (after removing the SET FOREIGN_KEY_CHECKS=1) and orders are cleared but order and invoices number don’t change: they all start with 4 (the first is #400000001).

Any ideas?

Boaz
timothyleetaylor says:

December 7th, 2008 at 5:52 pm
Hi @Boaz,

No problem, I’d be glad to help.

If you’d like, try to go through and run the last several lines starting with the “INSERT” statements. They are designed to create (if there aren’t any remaining values) and modify to increment the prefixes for orders, invoice, shipments, and credit memos.

To check manually, you can go into your db and access the `eav_entity_store` table to view. Please make sure there are incrementing values for`entity_store_id` and make sure the `entity_type_id` values are correct for your version of Magento. Lastly, make sure the prefixes are correctly stored in the `increment_prefix` column.

Though this screenshot resembles version 1.1.6, please feel free to view this screenshot as an example.

Let us know if you have any more problems!

Thanks, and all the best,
Lee
Boaz says:

December 7th, 2008 at 6:20 pm
Lee,

Thanks for your quick replay.

After posting my question I want and check the eav_entity_store table. I found the newly inserted records there and also another record with store_id=4 and increment_last_id = 400000001.
I’m not sure why my store_id was 4, maybe it’s because I played around with creating and deleting stores, but anyway I tried deleting this record and changing the store_id to 4 in the script and rerun it, but it fails too. Magento just recreated an additional record with entity_type=4, store_id=4 and increment_last_id = 400000001.
At this point I edited that record in the DB, change the increment_last_id to 000000001 and the increment_prefix to ” “. With this change I managed to get the order id starting with 0. Magento also created a record for the invoices with entity_type = 18 which I also changed manually to control the invoices numeration.

So… I managed to manually make it work but I don’t really understand how the script was suppose to make it work. Just hope that my changes are not causing any internal Magento problems (so far it seems OK).

Boaz
timothyleetaylor says:

December 7th, 2008 at 6:30 pm
Hi Boaz,

It’s important to note here that the script is meant only for a single-store setup (which you can verify via the values being “1″ in the `store_id` column). Thus, the script will only insert values for your store associated with the `store_id` value of “1″.

If you need help reading the SQL code, try this as a helpful resource: http://www.w3schools.com/sql/default.asp

The script works correctly with what you performed manually, but for a single-store setup. My apologies if I hadn’t made that clear enough for you in this post. I’ll review and add notification for that up above.

Thanks,
Lee
Boaz says:

December 7th, 2008 at 6:46 pm
Lee,

Thanks. I realized that I need to match the store_id the the id that I have (it’s a single store setup but I had several stores there before as a test which I deleted), but for some reason it didn’t work as expected even after changing the store_id in the script.
SQL syntax is very clear. I don’t fully understand the logic: why do you create entity_types with new entity_ids (11,16,19, and 23) and then try to update record with entity_ids that doesn’t exists (deleted by the previous TRUNCATE `eav_entity_store` statement)? I’m sure that there is some logic here, I just don’t know what it is.

In any case, this script was a great help. I would never dare to touch those tables without seeing how it’s done in this example.

Thanks again,
Boaz
timothyleetaylor says:

December 7th, 2008 at 6:57 pm
Hi Boaz,

So glad you found this as beneficial towards your Magento efforts. We’ll continue sharing with the community findings we see as relevant as we progress.

As for this specific script, we truncate to reset, prior to re-creating the separate entries for the necessary db values that in the `eav_entity_store` records. The prefixes are associated to a specific values that Magento uses set for `entity_type_id`. Each `entity_type_id` value relates to orders, invoices, shipments, and credit memo’s respectively.

Thanks again for the dialogue and feedback!
Lee
Shreehari says:

December 16th, 2008 at 4:49 am
Hi,
Is is possible to refresh (reset) a part of the database.
timothyleetaylor says:

December 16th, 2008 at 3:06 pm
Hi @Shreehari,

I’m not sure if I know what you mean exactly. Feel free to email us via the contact form below if you’re needing some consulting. Otherwise, try to be more specific with what you intend to happen, and we’ll do our best to help.

Thanks!
Lee
benz says:

December 18th, 2008 at 3:11 pm
Hi all,
apologies if i have skimmed and missed some info -
What I am wondering is if this will clear ALL of the orders in my Admin?

I have several test orders (cancelled), and now several real orders - I would like to remove the tests whilst keeping any new orders that have come in..

Will this script only remove cancelled orders?
thanks in advance, and great job!
Ben
timothyleetaylor says:

December 18th, 2008 at 5:40 pm
Hi @benz,

Glad to hear from you! No worries with asking the question. This script actually removes all orders in the ADMIN. It’s definitely possible to create a script that removes test orders - though we’d probably want to work with you on that specifically together. Many variations in store setup/management would probably create the need for a specific-script relating to your store setup, rather than an easy “plug ‘n play” script like the one above.

Should you desire this, please feel free to contact us via the contact form below and we’d be glad to assist you towards a solution. It shouldn’t take too much work to get something like this together for you.

All the best, and feel free to let us know how you proceed!

Thanks,
Lee
david says:

December 24th, 2008 at 11:21 am
Beautiful! Absolut beauty!

WORKS FOR 1.1.8

Thanks for shearing!
D
david says:

December 24th, 2008 at 11:25 am
Oh.. and… it cleared all the undesired data from all my stores and websites.

My configuration:

Website1 | store | storeview (ES)
Website1 | store | storeview (EN)

Website2 | store | storeview (ES)
Website2 | store | storeview (EN)

I just didn’t understand how to change the prefix from numbers to characters… I would like to do that.