AbleCommerce Shopping Cart Software

Posted: **Mon Jun 08, 2009 2:41 pm**

I'm working on some trigger code that is causing an error during checkout. In tracking down my error, I see that checkout attempted the SQL statement a hundred times before giving up. This definitely makes the app log verbose.

Is this a setting that can be changed somewhere? If it can be changed, should it or are there good reasons why 100 times is necessary or preferred?

The only other post that seemed relevant said checkout failed three times before aborting. That is more like what I expected.

Here is the error message in case it's pertinent:

Code: Select all

ERROR 2009-06-08 16:06:47,203 19995453ms Object                 a                  - An error occurred during the checkout process.  The checkout has failed 100 times and will NOT BE attempted again. UserId: 1 / Me@MyComputer.com Customer: Abe Adams / Me@MyComputer.com / 333-333-3333
System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "ac_OrderItems_ac_OrderItemInputs_FK1". The conflict occurred in database "MyStoreDev", table "dbo.ac_OrderItems", column 'OrderItemId'.
The statement has been terminated.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at CommerceBuilder.Data.Database.ExecuteScalar(DbCommand command)
   at CommerceBuilder.Orders.OrderItemInput.Save()
   at CommerceBuilder.Orders.a.b(Basket A_0, Order A_1, Dictionary`2 A_2)
   at CommerceBuilder.Orders.Basket.Checkout(CheckoutRequest checkoutRequest, Boolean validate, Boolean triggerEvents)

Posted: **Mon Jun 08, 2009 4:15 pm**

This is for purposes of locking and retry failover. If two people attempt checkout at the same time, and a sql deadlock occurs, AbleCommerce rolls back and attempts the checkout again. It will attempt this up to 100 times before giving up. Our goal was to be highly reliable under load and not drop orders.

Under normal circimstances this works. But the customization you made is triggering the checkout failure.

You could alter the 100 value. I think there is an undocumented web.config setting but I would have to look it up. Are you interested? It won't change the fact that you have to fix whatever is breaking your checkout.

Posted: **Mon Jun 08, 2009 4:22 pm**

Thank you Logan. I just wanted the information since 100 seemed high but I don't plan to change it. I have to take another tack on the trigger code anyway due to foreign key issues and the use of @@IDENTITY in AC Code instead of ScopeIdentity(). Thanks.

Posted: **Mon Jun 08, 2009 4:29 pm**

ZLA wrote:Thank you Logan. I just wanted the information since 100 seemed high but I don't plan to change it. I have to take another tack on the trigger code anyway due to foreign key issues and the use of @@IDENTITY in AC Code instead of ScopeIdentity(). Thanks.

Honestly I never thought the issue would come up. I think in load testing the worst I ever measured was three or four retries. But I was testing specifically for deadlocks. After we proofed the transaction code I never gave the retry number a second thought.

Posted: **Mon Jun 08, 2009 4:43 pm**

ZLA wrote:use of @@IDENTITY in AC Code instead of ScopeIdentity(). Thanks.

This is nagging at me. I could swear there was a reason for this, but now I can't identify it. I am going to log a bug to research it further. It seems Scope Identity would be more appropriate for our purpose.

Posted: **Mon Jun 08, 2009 4:45 pm**

Once I found out about ScopeIdentity, I started using it instead of @@IDENTITY. But I have to say I did not go back through my existing code base and update all the old references.

Posted: **Mon Jun 08, 2009 4:48 pm**

ZLA wrote:Once I found out about ScopeIdentity, I started using it instead of @@IDENTITY. But I have to say I did not go back through my existing code base and update all the old references.

At first I thought it might be related to our expected compatibility with MySQL. But that isn't the case. Then I thought maybe it wasn't supported by SQL 2000, but that isn't the case either. I know I looked at this issue. I wish I could remember why I chose @@IDENTITY. But it was probably two years ago... memory fuzzy.

Posted: **Mon Jun 08, 2009 4:53 pm**

Perhaps for clustering or something like that? I think @@Identity is across connections etc. So perhaps one of the limitations of ScopeIdentity wouldn't work in that scenario. Or maybe replication issues? I can't think of anything obvious, though.

Posted: **Mon Jun 08, 2009 4:57 pm**

ZLA wrote:Perhaps for clustering or something like that? I think @@Identity is across connections etc. So perhaps one of the limitations of ScopeIdentity wouldn't work in that scenario. Or maybe replication issues? I can't think of anything obvious, though.

I don't think any of those apply. We always select the identity as part of a compound statement, right after the insert, on the same connection. I can't see why Scope_Identity wouldn't be more perfect. In any case, the bug is logged and it will get looked at further.

Posted: **Wed Jun 17, 2009 6:42 am**

Similar error this morning. Error log recorded three errors in a row, 10:27:50 then 10:28:05 then 10:28:08 PM. All the same error message and debug data.

Code: Select all

An error occurred during the checkout process. The checkout has failed 100 times and will NOT BE attempted again. UserId: 61144 / customer@verizon.net Customer: XXXXXXX XXXX/ customer@verizon.net /

Code: Select all

Cannot insert the value NULL into column 'TotalCharges', table 'practic_ac7master.practic_sqluser.ac_Orders'; column does not allow nulls. UPDATE fails. The statement has been terminated.

Eventually an order was completed for the customer with a timestamp pf 10:08. If it helps, the payment method was PayPal. Who knows if that was same payment method used during the checkout problem.

--Alan

Posted: **Wed Jun 17, 2009 9:46 am**

FWIW, since the 7.0.2 upgrade the trigger I had on my ac_Orders breaks my checkout. It throws a foreign key constraint on ac_shipments.

My theory is the trigger is causing some sort of error. Since SQL 2005 automatically performs a rollback on the transaction if a SQL trigger errors, the foreign key constraint kicks in during the order save process because a shipment is trying to be written for an order that (now) doesn't exist.

For the life of me I cannot figure out why the trigger fails. When I execute the trigger code as a standalone script, it runs flawlessly every time. All I'm doing is inserting a record into a custom table. Complete unexpected behavior and was never a problem in previous AC7 versions.

All began when 7.0.2 upgrade was installed.........

Posted: **Wed Jun 17, 2009 10:06 am**

Alan,
You might want to edit your post and take out the person's email address.

Posted: **Wed Jul 01, 2009 2:53 pm**

I just got the error on a 7.0.2 site.
at CommerceBuilder.Orders.Basket.Checkout(CheckoutRequest checkoutRequest, Boolean validate, Boolean triggerEvents)
WARN 2009-06-24 19:27:44,624 78733204ms Object a - Error running SQL: UPDATE ac_Orders SET TotalCharges = (SELECT SUM(Quantity * Price) As TotalCharges FROM ac_OrderItems WHERE OrderId = @orderId), ProductSubtotal = @productSubtotal WHERE OrderId = @orderId
System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'TotalCharges', table 'xxxstore.dbo.ac_Orders'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at CommerceBuilder.Data.Database.ExecuteNonQuery(DbCommand command)
INFO 2009-06-24 19:27:44,624 78733204ms Object a - An error occurred during the checkout process. The checkout will be attempted again. UserId: 224996 /
Weird, looks out is started out with PaypalExpress checkout, and look how the order number changed

One.jpg

Two.jpg

I think I'll post a bug.

Posted: **Thu Oct 22, 2009 7:48 am**

Is there a fix (patch) available for this bug? On our site, it's happening for some users checking out with PayPal Express. We have PayPal authorization mode set to "sale" and handlefailedpayments on the receipt page set to true.

Is this the fix:
http://bugs.ablecommerce.com/show_bug.cgi?id=8351

Thanks!

Posted: **Fri Oct 23, 2009 7:36 am**

I just saw this error in our log as well. We are not under heavy load at the time of the error.

Code: Select all

 	10/23/2009 10:06:00 AM	Error	An error occurred during the checkout process. The checkout has failed 100 times and will NOT BE attempted again. UserId: 25XX421 / xxxxxx@comcast.net Customer: john xxxxxx/ xxxxxx@comcast.net / 610xxx7361	Cannot insert the value NULL into column 'TotalCharges', table 'FireFold.dbo.ac_Orders'; column does not allow nulls. UPDATE fails. The statement has been terminated.

Looking at the customer's order: The order was placed at 9:30AM, but this error did not occur until 36 minutes later. It appears that payment has gone through, and the order will complete. I thought this was strange, it seems like it is caused from paypal communicating with the site once the person logs into their PP account and releases payment.
Thought this might help.

Posted: **Tue Oct 11, 2011 12:24 am**

I m facign two errors ,and Now its sucks me a loy.
1.
An error occurred during the checkout process. The checkout has failed 100 times and will NOT BE attempted again.
......
Cannot insert the value NULL into column 'TotalCharges', table '....dbo.ac_Orders'; column does not allow nulls. UPDATE fails. The statement has been terminated.

2.
n error has occured at ...../Checkout/Receipt.aspx?OrderNumber=...&OrderId=..... Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Please Help so that I can solve it.

Thanks & Regards
Vikas

AbleCommerce Shopping Cart Software

Checkout Failed 100 times

Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times

Re: Checkout Failed 100 times