Error on basket.SaveAsOrder()

Question

I'm getting the following error when i save as basket as a pruchase order (basket.SaveAsOrder()):

 

Violation of PRIMARY KEY constraint 'PK_OrderForms'. Cannot insert duplicate key in object 'dbo.OrderForms'

 

it seems to be intermittent - as i can place orders fine but one other seems to get it quite regularly.  Has anyone else seen thin before?

 

Thanks

Answer 1

We've been getting that one too. Here's a stack trace:

at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at Microsoft.CommerceServer.Internal.Orders.MtsTxHelper.ExecuteSqlCommandDataSet(String connStr, String[] commandTexts, ArrayList[] sqlParamValuesArray, Int32 timeout)
at Microsoft.CommerceServer.Runtime.Orders.PurchaseOrder.Save(Boolean deleteOrder, ITransaction trans)
at Microsoft.CommerceServer.Runtime.Orders.Basket.SaveAsOrder(ITransaction trans)
at Microsoft.CommerceServer.Runtime.Orders.Basket.SaveAsOrder()

I took a peek at the internals using Reflector and i notice that the order.orderGroupId gets assigned a NewGuid but I don't see the OrderForm getting changed anywhere....

Answer 2

Did you ever find out what was causing the issue?  Or steps to recreate the Exception?  I'm not quite sure what resolution to give the QA department.  It looks like a bug in Commerce Server to me...

Answer 3

How do you generate the tracking number ?

Answer 4

Alex: I can't reproduce it consistently but it is still happening every once in awhile.

Gael: I think OrderFormId is the primary key of that table, not the tracking number...

Answer 5

Gael in our system the tracking number is just a nvarchar column on the purchase order table (no key or constraint on it).  the error i'm getting is a violation of the PK_OrderFormId key which is on the orderform table - its just a standard rowguid in sql server.  it must be trying to insert the same guid twice but as its all internal is pretty hard to debug. 

Answer 6

Hi,

 

Has any answer been given to this thread?  I've got this error one one out of 1000 requests but now I'm worried that I'm doing something wrong.. but it does seem to be an internal problem.  here's what I know:

 

The SaveToOrder() method transfers all of the Marshaled data about the order from the OrderTemplatesAndBaskets table to the PurchaseOrder, OrderForms and LineItems tables in the <YourSiteHere>_Transactions database.  The OrderGroupID of the Basket will NOT match the one for the PurchaseOrder. New Guids are created, however there must be some hash it used to seed guid creation because what I'm noticing is that there is a PurchaseOrder and OrderForm record but the Basket was never deleted from the OrderTemplatesAndBaskets table.  Because the whole process never completed, when my user goes back to submit the basket, it sees the record in the OrderForms table and throws the PK Violation exception.

 

My questions are 2 fold:

 

If when a new Purchase Order is created, a new Guid is assigned, and no 2 guids are supposed to be the same within an organization, how can this basket attempt to create the same OrderFormID GUID each time. I would assume each time it would create a new guid and maybe duplicate the order in the system.

 

Finally, is there a scenario or something that I might not be doing that would prevent the cleanup of the basket record once the order is saved as a purchaseOrder?

 

Thanks,

 

Dave

Answer 7

I just wanted to check in and note that we are another customer that is ocassionally getting this error.

 

Anyone have any resolutions?  Unfortunately, we cannot consistently recreate it either.

 

 

Thanks

 

EMS

Answer 8

I dont know about the internal cause of this error, but in your code, Is the order creation process enclosed inside a TransactionScope block?

 

Answer 9

I can confirm that all order related business calls are within one or more transactionscope blocks all with the same settings.  If an error would have occurred, the transactionscope SHOULD have rolled back everything that it had done on the order, but since I'm seeing new records in the PurchaseOrders table AND remaining records in the ORderTemplatesAndBaskets table, I have to assume that no rollback took place, but rather some internal process failed to cleanup the basket records.  I wish I had more to tell.. i only encountered this problem once.. but would be nice to know if it's a known issue or maybe something that will bite me in the rear later. 

 

--Dave

Answer 10

Under heavy load, we got this error 73 times this morning. Does anyone have any ideas or solutions for this?

Answer 11

I would really recommend opening a support case with PSS on this issue. This does look like some sort of SQL error being bubbled up to the Commerce Server runtime during times of heavy load.

 

Jeff Lynch

MVP Windows Server System - Commerce Server

http://codebetter.com/blogs/jeff.lynch

 

 

Answer 12

in doing some research into this today, it looks like it this is the problem:

- customer places order successfully
- Basket is saved as a PurchaseOrder
- the existing Basket is NOT deleted automatically
- customer tries to place a second order (using the same basket as before)
- SaveAsOrder fails with the error about the primary key violation.

Answer 13

I posted a detailed explanation of what appears to be happening months ago with regards to this problem.  I find it troubling that noone from Microsoft is responding to the post, being that it isn't confined to a particular condition.  I, for example, got this error only once and it was in a user testing environment where only one user was on the system.  So it's not necessarily attributed to heavy load.  Anyone from Microsoft care to shed some light on this?

 

Answer 14

I was getting this error thousands of times in a VS 2008 load test. I believe the cause was that I had multiple agents logging in and checking out under the same userid/password at the same time--something with the concurrency corrupted the baskets. I say this because when I divided up the username/password dataset uniquely among each agent (bound with Unique instead of Sequential) and purged all of the stale baskets, I stopped getting the error.

To purge your stale baskets, run: C:\Program Files (x86)\Microsoft Commerce Server2007\Tools\PurgeCommerceData.exe StarterSite -b -d 0

Hope that helps someone.

Travis

Answer 15

We've also been getting this error.  I can check and see that in the purchase orders table the basketid for the last order placed by a user is the same as the ordergroupid in their current basket they are trying to place an order for.  It's not very common as we've only had two of these baskets in the last 3 weeks.

We've been deleting the baskets that have had this problem, but we'd like to prevent it from happening to begin with.  This is a production environment so we're not doing any huge test loads or anything like that. These were users placing orders as normal so it's not something we can change from that end.

Has anyone found a solution other than cleaning up the baskets after it occurs? Or even the exact cause? 

Answer 16

Hello,

Is anyone on the product team looking at this problem?

We've got the problem in production!

Answer 17

Hi,

We've also been getting this error in production! and when we tried Travis' solution to purge the baskets, it stopped getting the error and it works fine now. thank you travis and i need to ask about something else the error may happen again and again what is the best practices to prevent this from being happens or is there any API functions that we could call from time to time to clean the corrupted baskets or something like this.

Thank You,
Karim Aziz

---

http://karim-aziz.blogspot.com/

Answer 18

The error happens when SaveAsOrder fails to delete the basket after saving the data as an order.
This basket still has the same id, so trying to saveAsOrder again generates an error.

Answer 19

Occassionally, I am getting a null value returned in production after SaveAsOrder() is executed. I am not able to replicate this in debug mode in development. Any ideas please?

Answer 20

I just want to echo the frustration of nobody at Microsoft providing a valid fix for this issue.  We are experiencing this almost daily in a production system.  The basket purge method does not fix the issue if you have customers placing lots of orders in a single day.  It only takes one time for this to happen and any further orders will not submit until the issue is manually fixed.

This is obviously a bug in the SaveOrder process.  If you are using a GUID to identify something, there should be no possible way it can be reused for anything after its purpose is completed.  There should be no way a submitted order can be in the system with an OrderFormId that is still active within the site.  I don't care if I have more than one person using an account or whatever other explanations people have given.  It just shouldn't happen.  I could write an order system using int IDENTITY order form keys and not have this issue.

The problem is there is a marshalled value in the OrderTemplatesAndBaskets pointing to an OrderForm that only exists in memory and the marshalled data.  There doesn't appear to be any validation check to make sure this value is actually unique.  Some process is reusing an existing value, therefore this is a bug.  The order submission process is not cleaning up after itself. The OrderFormId is never used anywhere else in the persisted data tables in the transactions database until the order is submitted.  So until the order is submitted, who cares if it gets changed unless I'm missing something.

I can clean this up 100% of the time by manipulating the last order the customer placed to assign a new OrderFormId after the fact to all the tables that are using it.  This works okay for us since Commerce Server is not our system of record and we pass orders on to another ERP system.  However, this is not a satisfactory or safe workaround if Commerce Server is your only system.

I can think of a number of ways to fix this:

1. The OrderFormId should always be validated for any live Basket in the system to validate uniqueness against a used value in the OrderForm table for all previously submitted orders.
2. Make the OrderFormId a writeable property in the OrderForms collection of the current Basket, so I CAN FIX IT.
3. Change the SaveOrder stored procedures that are called to do a validation check and change the OrderFormId passed in to prevent this problem.  This might not work in situations like split orders where there is more than one OrderForm in the Basket.

Anyway, this really needs to be looked at.  If there is a bug in a Commerce system that results in a certain percentage of customers not being able to purchase, that's kind of a big deal.

Thanks,

Curtis

Answer 21

Thanks for reporting this issue.

Can you check this to see if it is helpful: http://cc.bingj.com/cache.aspx?q=commerce+colin+bowern+tracking+number&d=76493806704060&mkt=en-IE&setlang=en-US&w=85085d42,f8eb48c0

We saw this issue before but cannot fully repro it. It is likely a product bug here. Can you contact Microsoft Support to open a case then we can issue a private fix to verify if it fixes your problem. Once the investigation is done, we can issue an official hotfix. We cannot release fix without a customer support case opened and in this case, relating product bug, there will be no charge for MS support.

Thanks

Hao

Answer 22

The issue is not with the order tracking number, it is with the OrderFormId GUID that gets inserted into the OrderForm and other related tables in the Transactions database.  This is created by the Commerce system and isn't anything you can get at through the APIs.  Unfortunately, I think you are going to have a hard time reproducing this in the lab because it only shows up in systems with a lot of activity, multiple users, etc.  We have never been able to reproduce it in our dev or QA environments.

Do you have a fix internally for the OrderFormId issue or something else?  If you think you have a fix, we're fine with opening a ticket.  I think the point of a lot of people on this thread is that there is obviously an issue with the way this works and it should be looked at and fixed regardless if someone opens a support ticket.