VM is wrongly resetting auto_increment after order deletion giving ref conflicts

[Jens Kirk]

Hi VirtueMart Team :-)

We have encountered a problem - actually 2 problems.

Our payment gateway uses the value from virtuemart_ordre_id as the forward counting unique number (integer) for transaction references. Using virtuemart_order_id as the reference for payments is great for both the shop owners and the bookkeepers because they can figure out the order (from the number) without having to look it up in the order management in VirtueMart.

BUT... VirtueMart is ressetting the auto_increment value for virtuemart_order_id after deleting of orders. It is resetting it back to the lastest existing order_id + 1. This action gives conflicts with our payment gateway which gives the error "not unique order number" because the next buyer is "trying" to reuse a reference for an older transaction.

In other words; because VM is resetting the auto_increment value to the lastest existing order it actually reusing order ID's giving reference conflicts in our payment system.

Can we - in a later version of VirtueMart - get an option not to reset the auto_increment value for virtuemart_order_id after deleting orders?

In our case we will not set it to "Don't not reset after order deletion". Other shop owners that user order_number (random string) as the transaction reference do not need to disable the reset of the auto_increment value after order deletion.

But I think that the bug is even bigger than this because I can see actions from deleted order in the history overview from existing orders (given the old virtuemart_order_id integer). So the history is merged by the old and deleted order and the new and not deleted order.

It seems to me that the resetting of the auto_increment is bugging. How can we disable it at PHP level for now?

Thank you for your great job :-)

[Milbo]

You should use the oder_number! not the order_id,.. vm isnt doing anything, it is the db.

the virtuemart_order_id is to use only internally for example, within the db, NOT for other issues.

"It seems to me that the resetting of the auto_increment is bugging. "
Again mysql does that. The autoincremented id is not meant for this purposes.

[AH]

There was a bug relating to auto increment and MYSQL posted on a forum

http://bugs.mysql.com/bug.php?id=727

I agree with Milbo - you should be using the order_number

The virtuemart_order_id is an internal database field

[Jens Kirk]

Hi Milbo

Thank you :-)

We like to have a real numbers for our orders. The randomly generated order numbers are not liked by our bookkeepers, the people working with the webshop nor the customers. Everyone much more like to say "please see order 1234" instead of "please see order ..... 3jfd34f4g422".

I think I have a fix.

Before an order or orders are deleted the max(virtuemart_order_id) value could be saved into an integer and this integer could be updated into the auto_increment value for virtuemart_order_id after the deletion of orders. In this case it will not re-use old order ID's.

I will see if I can fix this myself :-)

Again thank you for your answer and keep up the good work :-)

[Jens Kirk]

Hi again Milbo :-)

I see that you in your lastest version of VM use the order number structure "R2015081700001" instead of a random one. This is better but can you make and extra order number starting from 1 and going up like the auto increment does but not going back if the highest order number is deleted?

Or can you improve VM so the auto increment is set back to the (former) higest number after the lastest order is deleted?

This improvement is done by doing this after deletion of the last order:

ALTER TABLE #__virtuemart_orders AUTO_INCREMENT=[FORMER HIGHEST ORDER ID + 1]

Both solutions would do the trick :-)

[Milbo]

Hi again Milbo :-)

I see that you in your lastest version of VM use the order number structure "R2015081700001" instead of a random one.

since vm2.0.0! and we do not use the autoincrement of the db and you should never delete an order! Set it on cancelled. If you wanna cancel an order on a real cashdesk you must rebook it. You cannot delete it, same here, you should not delete it.

This improvement is done by doing this after deletion of the last order:

ALTER TABLE #__virtuemart_orders AUTO_INCREMENT=[FORMER HIGHEST ORDER ID + 1]

Try it, it wont work. We do not use the auto increment of the db!

[Jens Kirk]

Why is there an "order delete" button when it gives problems using it?

[Milbo]

The order delete button is for people who want to delete an order as it was never created. Usually used by agencies, before the shop is given to shopowners.

In the moment the shop starts, you are in most countries not allowed to delete an invoice by law. We dont want to disable a feature, because it could be illegal. You are responsible for your store yourself.

[Jens Kirk]

We use an external invoice system - www.e-conomic.com - for our invoices. So we can delete the orders.

Can you make some kind of warning (JS alert) when deleting the last order via the GUI? Because it is only deleting the last order that is giving problems for your webshop owners.

I could manipulate the virtuemart_order_id counter "up to" the last order ID that was deleted by changing the auto_increment value :-)

[Milbo]

In special the last order should not be a problem. Because we use Count (rows), so if you delete the last order, it is like never been there. Only if you get new orders meanwhile you will have missing counts. But the invoicenumber works with the invoice entries, not the order entries. so,.. as long you do not invoices with already generated invoices => you have no problem.

[Jens Kirk]

Okay thank you again :-)

The invoice number logic have jumped a lot around in the past years. Can't you make some modes for webshops owners to choose from? At least give a mode where the invoice numbering mode starts from 1 and counts up from there?

If so we could use this counting number system for transaction references and in external invoice references without problems.

I know that we can use the existing invoice number but ... it is very long and our clients and their buyers do not like it. Try saying a very long number over the phone and moreover the bank account overview only shows a few chars (meaking it impossible to recognize an order from the bank account overview). RedShop does not do this. It is starting from 1 and counts up which gives the buyers an easy way of talking about their order and the bookkeeps can easily find a payment. I think that VirtueMart version 1 actually did this but moved away from this in perhaps version 2.

Again thank you for all your work - VM is our primary shop system for our clients :-)

[Milbo]

Okay thank you again :-)

The invoice number logic have jumped a lot around in the past years.

Not really, I changed it in vm2 and since then it is the same.

Can't you make some modes for webshops owners to choose from?

http://extensions.virtuemart.net/vm-orders/order-number-plugin-detail

At least give a mode where the invoice numbering mode starts from 1 and counts up from there?

Actually, we have just an offset there.

If so we could use this counting number system for transaction references and in external invoice references without problems.

Exactly for that reason you should NOT use a simple number without any format.

I know that we can use the existing invoice number but ... it is very long and our clients and their buyers do not like it.

http://extensions.virtuemart.net/vm-orders/order-number-plugin-detail

Try saying a very long number over the phone

You did not notice the format of the invoice, eh? It is YYYYMMDDXX0Counter. Next version btw comes with an own pool of chars, without I, l, O and 0.

It is starting from 1 and counts up which gives the buyers an easy way of talking about their order and the bookkeeps can easily find a payment.

Yeah and opens fraud any door. Our order and invoice number is just a lot more sophisticated. You see an order/invoice number just as reference. I see it as key. A customer should not be able to guess an order or invoice number. Furthermore it should not reveal too much information and it should be sortable.
The offset prevents the order with ordernumber = 1 and invoice number = 1. It obscures a bit your sales volume. Additionally to that, the ordernumber and order password are the data to access an invoice, even as guest. So it is again important to have some random chars there. The invoicenumber is for the support almost like a password. When a customer calls you and knows the invoice number, you start to give support. So an unguessable order/invoice number prevents frauds.
Additionally, all pdfs are automatically sorted by date, if you sort for the filenames. So if you know and understand a bit the system and the ideas behind, then the strange numberse makes a lot more sense, or (at least I hope so).

Again thank you for all your work - VM is our primary shop system for our clients :-)

Thank you for that :-)

[Jens Kirk]

Thank you for all yours answers :-)

We do not use the order/invoice part of VirtueMart as you do. We have an external invoicing system – www.e-conomic.net - which is working automatically. And it is working so great. Both ePay and QuickPay have created integration for E-conomic with is the biggest Scandinavian online bookkeeping system – and they are expanding to the whole EU.
Every paid order is - after a successful callback from the payment provider – automatically converted to an order in E-conomic. And when the order is shipped it is upgraded to an invoice that is booked and the transaction is captured. Everything working perfectly.

A customer never sees the VM-order because we have disabled it. He/she only sees the E-conomic order (and after shipment; the E-conomic invoice). If he/she calls us up about the order/invoice, we always ask for the debtor number (with is found in the order / invoice). Therefore, we do not need to have special order numbers for security.

I see that the last past of your order number system is now a counter, but can we be sure that it stays a counter in the future? In other words; that your references between E-conomic and VirtueMart will not break down in the future. No you cannot give us this guaranty. You should be free to change the structure of the order number anytime in the future and we should be free to have an increasing non-reusing order_id reference logic for E-conomic and for your payment provider. As said everyone is happy about the order_id value. The bookkeeper is. The webshop owner is. The customer is. And therefore I - as their developer - am happy too :-D

I have make a tiny little hack into VM so the virtuemart_order_id is set back to the last order_id (as it was before the last order was deleted). The PHP/MySQL is inserted just after an VM-order is deleted. Everything is working great now and every webshop client is happy now that their customers never experience "invalid (re-used) order number" from payment provider. I will do this for all my VM clients.

In Denmark we are allowed to delete the orders as much as we like but we cannot deleted nor change booked invoices at E-conomic.net.

Both www.quickpay.net and www.epay.eu are (in their newest versions for their newest platforms) have / are starting using virtuemart_order_id as the counter for the order ref in their transactions (plus a prefix for clients with more sites on one payment gateway agreement). Both QuickPay and ePay are unaware of the danger in deleting the last order in VM. They will become aware of this if we do not fix it before their clients contacts them about a (new) problem with "invalid order number" when people try to pay.

But as said my little hack fixes this but it is only for my clients and the VM shopowner that I know.

If I send you the hack will you implement it into the official version of VM?

If you don't like the idea then could you make an option in the backend of VM where the webshop owners can choose between reusing and non-reusing order IDs? Because then all ePay and QuickPay users can fix the problem by choosing "non-reusing order IDs". You can leave the "reusing order IDs" as default.

Thank you again for all your hard work for the community :-)

[welrachid]

Hello Jens
I've developed a special integration between e-conomic & VM for "company-customers" only. When my customer asked for special incrementing order numbers instead of randomized numbers we bought this plugin:
http://extensions.virtuemart.net/vm-orders/order-number-plugin-detail it has "global counts" which means it has a single counter across all vendors.

i do NOT recommend to make your "own changes" to the database. This is a very big potential risk of losing database integrity

My recommendation is ALWAYS to restrain from making hacks to any core part of the VM itself.

If Epay/quickpay are making mistakes and using virtuemart order id you can help them (or at least epay) by forking their plugin and making the changes needed. https://github.com/ePay/virtuemart3
I've already done this to be able to make templateoverride of of post_payment page. (so that you can actually make your own "thank you for ordering..blah blah page" - find it here: https://github.com/welrachid/virtuemart3 )

With regard to re-using order_ids theres an option here to be able to do this.
http://imgur.com/FgRxHth

BTW also from DK

[Jens Kirk]

Hi welrachid

You do not get ePay and QuickPay to change back to old number system that was hard to trace. QuickPay changed to using virtuemart_order_id in the early start of this year. They will properly just advice VM-users NOT to delete the last order. I talked to the main VM-developer at QuickPay and he says that they are NOT going back to the old order number system. I will give him my little VM-hack so they can write a note about for their VM-uses. I have not contacted ePay about the issue. 90% of our clients are using QuickPay.

Both ePay and QuickPay are releasing they new platforms - QuickPay is a step in front of ePay - and both want to have simpel, logic and increasing order number as transaction references.

We have been working 4 years will a full integration extension for VM (and a few other Joomla webshops) and E-conomic. We are releasing it in the start of next year. We are running it with 10 of our clients. This little VM hack that prevents MySQL from reusing old virtuemart_order_id values is working great :-)