1

In our website, magento order increment id is not working as expected. We are getting difference of 3 between every two orders. Like for example,we have order 000005167 and next order we are getting as 000005170.

And when we checked from DB to know the next autoincrement value,it is showing as follows:

MariaDB [pkdxthkinb4aa_stg]> SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = "pkdxthkinb4aa_stg" AND TABLE_NAME = "sequence_order_1";

+----------------+

| AUTO_INCREMENT |

+----------------+

| 5173 |

+----------------+

While we should get 5171 as the last value in sequence_order_1 table is 5170.

Can you please advice me on how can I fix this

Thanks in advance

1 Answer 1

0

After checking some articles I figured out, there is no other way to make increment by 3 than this:

Magento stores order auto increment id in sequence_order_1 or sequence_order_2 table, based on stores. So it uses only 1 increment id by default. Unless someone change auto_increment_increment value of database like:

SET @@auto_increment_increment=3;

Try this command and see if your auto increment value is fixed:

SET @@auto_increment_increment=1;

Note: If you are getting this issue on order increment id and it is occurring due to auto increment value, then it would be occurring on all tables, because it's a global variable

Another possible cause

auto-increment-increment may be set to 3 in /etc/my.cnf file. After update, issue will resolve

Try both scenario one by one

I hope it will help

3
  • SET @@auto_increment_increment=1; --> I have tried this but it is showing like 0 rows affected and i have checked the values like SHOW VARIABLES LIKE 'auto_inc%'; but it is still is showing 3 for auto_increment_increment
    – S.Venky
    Aug 19, 2019 at 12:38
  • Ok Have you checked the value of auto-increment-increment in my.cnf? Aug 19, 2019 at 12:43
  • NO we are in cloud environment and we don't have access for it to view
    – S.Venky
    Aug 19, 2019 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.