Jump to content
Phrkd

Duplicate Order ID Assigned, Order Not Created But Payment Taken

Recommended Posts

Hello, I had an issue yesterday where a duplicate order id was given to an order placed by a customer. The order was not created/stored in cubecart but the payment was taken from the customer. I managed to find the order details in the system error log and noticed at the bottom was this message: - Duplicate entry 'FNS00012' for key 'custom_oid'

Why would this happen and how can I prevent it from happening in the future?

Many thanks in advance.

Share this post


Link to post
Share on other sites

Hi, I'm running 6.2.8, thanks for the link. All other orders have gone through fine prior to this one and after so I'm not understanding why it was trying to reuse an order number that was already assigned to an order...

Share this post


Link to post
Share on other sites

The values in the custom_oid column in the CubeCart_order_summary table must be unique, or hold a null designation. Because the database engine saw that there already exists a table row with this value, the insertion of the new data that would create a new row failed.

However, if the order still gets processed after having failed to insert that row, the CubeCart programmers will need to look into that and develop a proper course of action on failure.

CubeCart uses the formatting declared in admin, Store Settings, Features tab. The settings are exposed when the Order Number Format choice is "Incremental" when selected from the drop-down chooser.

I can imagine that if there is an order being currently processed through checkout when a second order gets ready to checkout, there could be collision.

So, was there another order being placed at very nearly the same time?

Share this post


Link to post
Share on other sites

Short answer is no. The order number that cubecart tried to assign was already assigned to a completed order that was made a few days prior.

Share this post


Link to post
Share on other sites

Ok, good to know.

I have been studying the situation and I think I will be concurring with an observation posted in the Github (https://github.com/cubecart/v6/issues/2439).

My next questions about your situation are:

1. Do your orders (specifically these two being discussed) collect taxes?
2. In the pattern for your Incremental Order Number, do you have a value for "Increment Increase"? (Badly named as this is not an incremental increase -- 100, 200, 300, etc, but rather a starting number -- 100, 101, 102.)
3. (Tricky, so be careful) Using an external database utility (such as phpMyAdmin), take note of the highest number found in the 'id' column of CubeCart_order_summary, take note of the highest number found in the 'id' column of CubeCart_order_taxes, take note of the "Increment Increase" value mentioned above. Are the two 'id' values close to each other? If not, would the difference between the two be close to the value of the Increment Increase?

Share this post


Link to post
Share on other sites

1. Yes both orders collect taxes.

2. Starting number for the incremental increase was FNS00001.

3. Highest number in the 'id' column is 30 in CubeCart_order_summary and the highest number in the 'id' column of CubeCart_order_tax is 11

Also my cart_order_id is showing as the default cubecart order id format (200128-163717-3354), whereas my cubecart dashboard shows my orders using my incremental order id (FNS00012). (FNS00012 is the order number that cubecart tried to assign, even though it was previously assigned to an order that had been completed several days earlier.)

   
Edited by Phrkd

Share this post


Link to post
Share on other sites

Hmmm....

You say the Incremental Increase is FSN00001? But is this actually the Preview of the whole sequence: prefix (FSN), padding (5 zeroes), and starting number (1)?

Edited by bsmither

Share this post


Link to post
Share on other sites

I think I'm misunderstanding you bsmither, sorry.

Share this post


Link to post
Share on other sites

You said, "Starting number for the incremental increase was FNS00001."

I just read it another way and no longer confused.

 

Edited by bsmither

Share this post


Link to post
Share on other sites

.

It's just happened again. Duplicate entry 'FNS00014' for key 'custom_oid' is in the error log. I've had two orders go through before this that have worked fine..

I have set the order id to the cubecart default to work round this as there should be less chance of an identical order number being used. I would like to know what is causing this issue though.

Edited by Phrkd

Share this post


Link to post
Share on other sites

The two that went through... Were their order numbers FNS000013 and FNS000014?

Share this post


Link to post
Share on other sites

The two that went through OK were 11 and 13, then the next order tried to use 14 which was already assigned to a completed order.

Something seems buggy somewhere along the line, perhaps it'd be worth me grabbing a months CC support for someone to have a look?

Edited by Phrkd

Share this post


Link to post
Share on other sites

What happened to 12? And if 14 was already used, why then did 11 and 13 come later in time? Am I misunderstanding the chronological order of these orders?

Share this post


Link to post
Share on other sites

You and me both. It would appear that cubecart is trying to reuse order numbers that were assigned to completed orders.

12 was what I'd originally opened this post for, someone ordered and cubecart tried to assign an order number, in this case 12, that was already assigned to a completed order that is listed under Orders in the cubecart dashboard.

I've had two other orders go out that have worked fine and used recycled order numbers. (Am I right in assuming that cubecart will recycle order ids if said order id is not associated with a completed order? So where a customer may have populated a basket but not completed the order?)

Something has been off with the way the incremental orders from the start but I'm unable to fathom where or why this occurs...

 

Share this post


Link to post
Share on other sites

The Incremental Order Numbers are constructed as follows (as best I can determine):

A database feature called a TRIGGER is installed in the CubeCart database:

CREATE TRIGGER `custom_oid`
BEFORE INSERT
ON `CubeCart_order_summary` FOR EACH ROW
SET NEW.custom_oid = CONCAT(prefix, LPAD(LAST_INSERT_ID() + 1000, 5, 0), suffix)

Breaking this down shows the SET part running just before a record is actually INSERTed into the CubeCart_order_summary table. The result of the CONCAT function are held in a temporary table-like structure called NEW.

I have serious concerns about the use of LAST_INSERT_ID(). The database engine will note the value used to populate an auto-incrementing column on insertion, not actually keeping separate which table that insertion happened. (The NEW and OLD temporary structures do not count, but does keep separate the 'session' of client-server conversation. This prevents two or more simultaneous conversations from cross-contamination.)

So, my theory is this: the LAST_INSERT_ID() value is that of the last insertion of any table (I will analyze a trace to see what may have caused that.) in that database for that session.

It could very well be the case that beta testing showed no problems because the last value used after an insertion somewhere else just happened to line up exactly with the value that made sense for the custom order number that was expected. (Which is why I asked about the 'closeness' of ID values in the two tables.)

So, it is not the case that CubeCart (actually, the database engine) will re-use a value for an auto-incremented column. That would cause chaos at a massive scale. (However, the database engine will accept an explicitly provided value for the column - if the database is configured to allow that. Doing that is incredibly dangerous.)

I will be conducting experiments to test other triggers, such as AFTER INSERT which might make the LAST_INSERT_ID 100% reliable.

Share this post


Link to post
Share on other sites

If there's anything I can do to assist in your investigation please let me know.

Share this post


Link to post
Share on other sites

My issue related to my state's Sales Tax and the COID has not caused any duplicate order numbers. But if I can be of help in any way, I'd be glad to try.

Share this post


Link to post
Share on other sites

DB, if you have the time, take note of an order with a custom order ID that is outside the normal (preponderant) progression. From what you said, that would be the latest order that had tax applied.

Considering that the custom format includes the integer value in the auto-incrementing column for that row (rather, it is supposed to), take note of the integer part of the order's custom ID value. That would probably be an integer 7xx.

Now, for the time-consuming part...

Using an external database utility, look at every table in your database and try to identify if any table has, as the last record added, and thus the highest value in the `id` column (the auto-incrementing column), a value very close to the integer noted above.

As an example of what you might find, the CubeCart_order_tax table could have the highest value in the `id` column of 762. Compare that with the last suspect custom order number which could be 2019-PC-0762, focusing on the integer part: 762.

The above is just an example (could be any table), and there could be a difference in the value of the integer depending if you have specified a "Starting Number" in the Incremental Order Number Formatting details in admin.

If this examination shows that there is a table that satisfies what we are looking for, it will validate my suspicion of the trigger being incorrect by using the LAST_INSERT_ID() function.

Share this post


Link to post
Share on other sites

I used 442 for Increment Increase to get the CC custom order numbers to resemble what I previously was using with SFWS's Sequential Order Numbers. Just for the record, I scrubbed my database every which way I could think of to get rid of any trace of his plugin before I changed to the upgrade that had COID in it. Last time the outlier number came up was 2019-PC-0754. But this was on Dec. 15, with lots of orders since then.

So could you give me an idea of what number I should be searching for? Do I need to wait for the next Alabama delivery to try to find this? (We don't get very many orders delivered to Alabama.)

Share this post


Link to post
Share on other sites

Take note of the (traditional) cart_order_id number for 2019-PC-0754.

Look to see if there is any table that has an (auto-increment) `id` column that has a value of 312 (which is 754-442) in any of its rows. (As you say, that row may be a bit of away from the last row.)

 

 

 

Edited by bsmither

Share this post


Link to post
Share on other sites

As a side note, all the customer sees when this error occurs is a '404 page not found' error code that appears in page immediately after payment is taken and the customer is returned to the shop. The only reason we know an order has been made and paid for is from the payment gateways payment confirmation email we receive. CubeCart doesn't  appear to give/have any indication that money has been taken or an order has been attempted apart from the error log showing a duplicate order_id has been assigned.

Share this post


Link to post
Share on other sites

Traditional Order# 191215-054313-1929

Found:

customer_id for Andrew G found in two tables

product_id for product 3866

id from cookie consent session aec49189a8b2d7a2cebbe44114952d5b

filemanager file_id for BabyAnimals08042009xxx809.JPG

geo_zone id for Middlesbrough

image_index product_id

product_id in inventory

ID in manufacturers

subscriber_id

order_history_id

order_notes order_id for 2012-PC-1903

order_summary  for 110327-225820-4075

id for searchst 70190

seo_urls id for aurora-small-green-cream-chenille-beanie-tree-frog

I hope somewhere in all this is a clue for you @bsmither !!

 

 

 

Edited by Dirty Butter

Share this post


Link to post
Share on other sites

404 Page? It would be interesting to see what was being asked for.

Of the tables listed, these would have had something inserted into them when shopping cart details were being converted to an order:

customer_id for Andrew G
Was this order for Andrew G? (Updating-Inserting order count.)

id from cookie consent session aec49189a8b2d7a2cebbe44114952d5b
Possible but not likely - would have happened long before constructing the order.

product_id in inventory
product_id for product 3866
What's the difference between these two instances? Is one the CubeCart_order_inventory table?

subscriber_id
Possible if customer checked the box to subscribe when checking out.

order_history_id
Good candidate. Do the standard cart_order_id numbers match?

order_notes order_id for 2012-PC-1903
This is a 2012 order. Not likely.?

order_summary  for 110327-225820-4075
This is where the problem occurs. The `id` will not be relevant for an order made in 2011.

Nothing found in CubeCart_order_tax? No record having an `id` value of 312?

 

Edited by bsmither

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...