Kryptronic Software Support Forum

I'm responsible for maintaining a few client stores that do 500-1000 orders a day, day in and day out on K9 without having ever seen or heard of anything like this.  I'm going to assume you're fully updated with 9.0.2.  Here are some random thoughts, in no particular order...

Order processing is very intensive, and the way it's handled in K9 requires updates to the orders, orderitems, and payments tables as orders are processed.  In order for one valid data set to be overwritten with a part of that same data set (because only part of it would never be deleted), it seems like two things have to be trying to process the order at the exact same time.  Assuming your database is either not optimized (you haven't added indexes for K9 tables) or running terribly slow, or has a ton of old data in it (gigs and gigs and gigs), I could see this potentially happening in a scenario as follows:

Customer sets up order in checkout as a PayPal-type transaction (or some other gateway that does a relay back to the site plus a silent post); Customer pays for order at gateway; Gateway IPN request fires to K9 immediately and starts processing; Gateway redirects customer to K9 simultaneously and customer page loads exactly at the moment the IPN request is deleting records from the orderitems table, and only pics up what is left over; Original IPN request finishes processing and saves the order; Relay request saves the data it has (subset) when it finishes processing.

The chances of this happening on a server that's not experiencing any slowness issues is probably 1 in 100,000,000,000.  The chances of this happening on a server that is experiencing serious lag and taking several seconds to process orders could be 1 in 1,000.

Thoughts on a way this could have happened.  So, were these orders using a gateway that does an IPN (instant payment notification) and a customer URL relay?  Did you create indexes on your K9 tables (instructions elsewhere on this forum, not documented yet)?  Is anything running slow?  What about mail, is that running well?  Are you on 9.0.2 (which added IPN/relay double-post prevention logic reducing the chances of this happening)?  How many orders and orderitems do you have in your database right now?  Can the database be optimized or run through a repair function?  If MySQL, is it MyISAM or InnoDB?  We've had better results all the way around with MyISAM over the years, FYI.

Let's dig into this.  You see where I'm thinking the issue may be.

Thank you for the reply and additional info.  Your order items table is large, but I manage a few sites with 500k+ rows in there and years of orders and they don't experience this.  Future shorter-term plans for K9 include an order archiving mechanism that archives orders 2+ years old (along with their associated items and payments, related credits, etc) to different tables.  So we're being proactive on this side about keeping the table sizes down, and that will result in speed increases - but you're not to the point where you'd notice that much.  It could be that those three orders came in while your database or email server was otherwise choking on something.  I think you're taking the right approach - ensure everything is optimized, repaired and indexed, and then monitor.

Simple!

Orders, order items and payments should all be archived, with credits and credit items likely candidates too.  We need to modify the orderlookup() function, reports (to pull from both sets of tables), and the table definitions for the archive tables need to be added to the install package and kept in synch with each other.  A couple of other areas need to be hit too, like order listings for the customer, and the Order Manager will need to be set up to search both sets of tables for order lookup, and will need to be able to disable updates/functionality/etc. for archived orders.  They will likely have a view, print and email options only.  Need some kind of reasonable cutoff like 2 years, or maybe order completion date plus 1 year (or return cutoff timeframe).  Needs to be seamless as far as the customer and admins are concerned.  We'd be storing data in two sets of tables to make the system more efficient, not to limit histories or abilities in any way.

It actually isn't that hard of a thing to do.  And it's getting done, because we've got clients like yourself that need this sort of optimization now to prevent issues a few years from now when data sets grow too big without pruning.

Thank you.  Ensure software is fully updated to 9.0.2 which has measures in it to prevent double-processing via gateway relays.  Other than that, we are looking into any other ways to handle this better, and if and advancements are made in that area, they will be added to an update.  Occasionally PayPal or the actual customer has Internet connectivity issues that affects these types of gateway relays, so we're always working to eliminate issues arising from those sorts of things.