Removing Orders From Database

27 replies [Last post]

Mon, 11/24/2008 - 16:47

Alaska

Offline

Joined: 10/16/2007

Juice: 1433

Would like to remove all of the test orders from a Ubercart database by using the empty table command in phpMyAdmin. Are the following tables the correct ones for this application? Or are there some that were missed? (Uber 1.6 - Drupal 5.12)

1) uc_orders
2) uc_order_comments
3) uc_order_admin_comments
4) uc_order_log
5) uc_order_products
6) sequences - change uc_orders_order_id back to 0

And backup the database for insurance.

Mon, 11/24/2008 - 16:52

#1 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

Re: Removing Orders From Database

Really anything prefixed by uc_order... so don't forget uc_order_line_items.

EDIT: However, don't clean out uc_order_statuses. Sorry, Bartezz!

Wed, 02/11/2009 - 09:38

#2 View page

Bartezz

Offline

Joined: 04/18/2008

Juice: 104

Re: Re: Removing Orders From Database

"Really anything prefixed by uc_order... so don't forget uc_order_line_items...."

Except for uc_order_statuses learned that the hard way now

Wed, 02/11/2009 - 10:06

#3 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

Re: Re: Re: Removing Orders From Database

Oh no! I didn't even think about that. Sorry for the trouble.

Thu, 02/12/2009 - 10:46

#4 View page

Bartezz

Offline

Joined: 04/18/2008

Juice: 104

Re: Re: Re: Re: Removing Orders From Database

No sweat!

Mon, 11/24/2008 - 20:20

#5 View page

Alaska

Offline

Joined: 10/16/2007

Juice: 1433

UC Order

Ryan:

Thanks for the hint. Would have forgotten that one. Will add these to the list.

Mon, 11/24/2008 - 20:24

#6 View page

thill

Offline

Joined: 01/25/2008

Juice: 815

Re: UC Order

Just a side note, we often bump that order sequence up instead of back down to 0. Customers don't like seeing they are order number 2 usually.

Mon, 11/24/2008 - 21:50

#7 View page

Alaska

Offline

Joined: 10/16/2007

Juice: 1433

Order Starting Point

You are most correct. Did change it to 1500 as a test.

As a side question, let's say that there was a need to start over at 900. There are orders numbered from 1500 through 1527 as test orders already in the database. Since you can not start at a lower number what would be the best procedure to follow?

It would be my guess that test orders 1500 - 1527 would each have to be removed individually from each of the appropriate database tables. A most time consuming effort. Or, if a new installation just empty all of the correct tables and start with a clean orders pallet.

But, what if the client wanted to retain orders 1500 - 1527 and still start over at 900? Would assume that is not possible and the correct answer to the client is that 'this can not be done'. i.e. not sure what would happen if orders reached 1499. Would the db then start the next order at 1527 and skip the in place range? Or would Uber freeze up?

Mon, 11/24/2008 - 22:37

#8 View page

thill

Offline

Joined: 01/25/2008

Juice: 815

Re: Order Starting Point

You can delete those test orders from the database, you say time consuming? i just would select the 27 orders and get rid of them, doesn't seem like it would take you more than 2 minutes unless you haven't used phpmyadmin before.

You are correct, you don't want to go back to 900 without deleting the other orders, as soon as you got to an order number already taken it will throw a php "insert into" type of error saying that row already exists.

Hope that helps clear it up.

We also do the same for the users sequence field, we don't want customers being assigned user 3, so we bump that up as well.

Mon, 11/24/2008 - 22:53

#9 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

Re: Re: Order Starting Point

If I need to delete a bunch of orders like that at once, I tend to just delete them through the order admin interface. Use a middle click or something that opens links up in a new tab in your browser and go through clicking the delete icons... then go through each tab and click the submit button. There's a permission that will let you delete orders even if they have payments and such, and this will take care of all the various tables automatically.

Tue, 11/25/2008 - 18:43

#10 View page

Alaska

Offline

Joined: 10/16/2007

Juice: 1433

Using Order Admin Interface

Ryan:

Glad to see another approach that is on the safe side. Don't mind using phpmyadmin, but one can really mess up a db if not done correctly. Will give your system a test try. That way everything will be 100% correct and there is no chance of errors.

Update

Go to admin/store/orders to delete orders. Using Firefox select the center icon under Actions. It is the pencil and if moused over will indicate 'edit order 76'.

Select the order to be deleted and hold down the Ctrl key. Do the same for each additional order that is to be deleted. Firefox will open up a new tab for each selection. Open each tab and select delete from the screen. Then go back and when it asks "Are you sure you want to deleted order 76?" Click delete once again.

Thanks Ryan for the tip. This will be a nice how to for customers in place of using phymyadmin.

Tue, 11/25/2008 - 00:36

#11 View page

Alaska

Offline

Joined: 10/16/2007

Juice: 1433

DB Order

Thill:

Thought that the db would do something like that but was not sure. Nice to know that it will show a insert into type of error. Best not to allow that to happen.

Nice tip on the sequence field for users. Have not thought of that but it is a grand idea. Perhaps start off users at 150 plus or so to give the illusion of a well used site.

Thanks again.

Tue, 11/25/2008 - 18:05

#12 View page

tekad

Offline

Joined: 10/21/2008

Juice: 160

Alaska wrote:Thill: Thought

Alaska wrote:

Thill:

Thought that the db would do something like that but was not sure. Nice to know that it will show a insert into type of error. Best not to allow that to happen.

Nice tip on the sequence field for users. Have not thought of that but it is a grand idea. Perhaps start off users at 150 plus or so to give the illusion of a well used site.

Thanks again.

Actually this is seems like the incorrect way to handle this. The systems should never throw an error like this to the customer when a customer is ordering. On order confirmation, the system should simply check for next avail order ID by asking for the next primary key to use in the master order table and keep going. Notify the admin on the backend side if the was a large jump in order numbers for some reason.

Wed, 11/26/2008 - 00:25

#13 View page

mikejoconnor

Offline

Joined: 08/07/2007

Juice: 536

Re: Alaska wrote:Thill: Thought

Here is some background on the why the sequences table was introduced.

http://lists.drupal.org/pipermail/development/2007-April/023602.html

The issue is when things get out of sync, you get collisions on insert. It would be really nice if we could just use autoincrement.

Commerce Guys | Ubercart Development | Ubercart Training

Wed, 11/26/2008 - 10:40

#14 View page

Lyle

Offline

Joined: 08/07/2007

Juice: 6846

Re: Re: Alaska wrote:Thill: Thought

While I understand wanting to delete your test orders, I don't see why you should roll the order ID back. The actual number shouldn't mean anything in and of itself. The important thing is that they're all unique, which is hard to ensure when you go around the system to change the "nextval" number.

Fortunately, in Drupal 6, we are using auto_increment for MySQL and serial columns for PostgreSQL. However, even though the sequences table doesn't exist anymore, you can still muck up the ID columns and change what the next one will be.

Wed, 02/11/2009 - 06:35

#15 View page

Bartezz

Offline

Joined: 04/18/2008

Juice: 104

Re: Re: Re: Alaska wrote:Thill: Thought

Rolling the order ID back might cause problems with some payment providers.
I just installed a complete clean ubercart site and connected it to an existing Ogone (uc_ogone.module) account.
Ogone uses the order id given by ubercart but does not allow duplicates. Since my installation was clean it started with order_id 1 which was already in use with Ogone.com and thus the payment provider wouldn't except the order... Ogone.com does not allow you to roll back the order_id there so doing only in ubercart will cause problems sometimes!

Cheers

Thu, 03/05/2009 - 04:03

#16 View page

fossle

Offline

Joined: 01/20/2009

Juice: 77

existing orders - sale won't go through

Hi,

I don't know much about database administration, but do have access to phpmyadmin. I've been running a couple stores w/Ubercart and not had any issues. I just added a new store and keep getting an "Order already exists in database" error - this is due to Linkpoint. How do I change the order number starting point when I set up a store so I don't roll back to old numbers? Or is there a way to not pass order numbers and send something else like a timestamp?

Thank you,
Kim

Thu, 03/05/2009 - 16:25

#17 View page

fossle

Offline

Joined: 01/20/2009

Juice: 77

Re: existing orders - sale won't go through

I figured out how to change the order number. There was some discussion in 8/08 about adding a prefix to orders - did anything ever come of this?

Wed, 10/14/2009 - 14:47

#18 View page

Danny_Joris

Offline

Joined: 05/09/2009

Juice: 199

Re: Re: existing orders - sale won't go through

Isn't it easier to have a custom module for that instead of manipulating the database? + it will prevent many users from clearing the wrong db tables.

This could easily be done by giving user-1 a single button to clear all the orders.

I don't know much about module building, but if you do, I think it is a very small module.

I'm about to clear my orders, but i'm still afraid that I will delete something wrong.

Thu, 02/12/2009 - 13:38

#19 View page

eoneillPPH

Offline

Joined: 12/22/2008

Juice: 55

Re: Removing Orders From Database

How about sequence uc_order_products_order_product_id for resetting?

and

How about table uc_cart_products for truncating?

Thu, 02/12/2009 - 17:51

#20 View page

Ryan

Offline

Joined: 08/07/2007

Juice: 15438

Re: Re: Removing Orders From Database

Neither one of those are essential, and the second one will actually empty out everyone's shopping cart... different from deleting orders.

Fri, 02/13/2009 - 12:05

#21 View page

eoneillPPH

Offline

Joined: 12/22/2008

Juice: 55

Re: Re: Re: Removing Orders From Database

Thanks. Of course, after testing and just before going live, it could be a good thing to empty out any stray shopping carts, so I may include that one anyway. But won't worry about the sequence value.

Thu, 02/18/2010 - 10:08

#22 View page

ozyrys

Offline

Joined: 11/13/2009

Juice: 28

order total is always 0 after truncate orders

After I truncated my orders tables my order totals are always zero, why?
here tables I've truncated:

1) uc_orders
2) uc_order_comments
3) uc_order_admin_comments
4) uc_order_log
5) uc_order_products

Maybe I forget abaut sequences? Where is it? What is it?

Thanks Oz

UC 2.2

Mon, 09/20/2010 - 17:32

#23 View page

regx@drupal.org

Offline

Joined: 10/30/2007

Juice: 98

Re: delete all orders

Or you could simply create an admin script like this:
I keep my admin scripts out of the public_html folder you might have to update the paths.
if you create an admin_tools folder in the same dir that public_html and put this in there it should work.

<?php
/**
  * Delete all orders
  */
  
chdir ('../public_html/');
require_once './includes/bootstrap.inc';
drupal_bootstrap(DRUPAL_BOOTSTRAP_FULL);
print "bootstrapped\n";
// get root
global $user;
$user->uid = 1;
$result = db_query("select * from uc_orders");
while($row = db_fetch_object($result)){
  //# uncomment below to view an order and exit
  //# usefull to write a query to only delete certain orders 
  //print_r($row);
  //exit;
  print "deleting order $row->order_id\n";
  uc_order_delete($row->order_id); // comment this out for testing
}
?>

Mon, 04/25/2011 - 23:17

#24 View page

bokki

Offline

Joined: 03/20/2011

Juice: 12

Order count still not resetting

I'm also having trouble with this...
I've firstly deleted all the orders via the ui -> 'view orders'.
Then found remnants of orders by querying the order_id column in the Db (remnants underdiscounts, shipping etc. were found)
I've then deleted those from the DB.
However the auto increment is going up on new orders as if I hadn't done anything. Did I miss something, a step, a concept?

-- the order number is very important in the way i create my invoice numbers... --

Sun, 08/21/2011 - 10:36

#25 View page

Chris Vanderwall

Offline

Joined: 08/21/2011

Juice: 7

Can't figure it out...

Hi-

I can't seem to figure things out... I've deleted records from uc_orders, uc_order_admin_comments, uc_order_line_itmes, uc_order_comments, uc_order_log, uc_order_products, and uc_order_quotes.

Unfortunately, all of my orders are still listed in my database. What I need to figure out is:
1. How to delete orders in mass quantities (I have over 70 test orders that need to be deleted)
2. I need to figure out every table that contains order information because I'm working with a development and live site configuration, and need to add tables to a non-write script so that I don't overwrite the orders on my live site (real orders) with what will eventually be my blank orders on the development site.

Thanks for any advice you can provide!

Sun, 09/04/2011 - 10:18

#26 View page

Yuri

Offline

Joined: 05/26/2010

Juice: 35

Working

I confirm that post #23 is working.

Thu, 01/05/2012 - 06:34

#27 View page

marthinal

Offline

Joined: 06/05/2010

Juice: 6

Re: Working

Yes also from php block (devel module) you can execute the script.

thanks regx@drupal.org #23

	User login
	Log in using OpenID: What is OpenID? Username or e-mail: * Password: * Remember me Log in using OpenID Cancel OpenID login Create new account Request new password

Ubercart

Navigation

User login