Search code examples
auto-increment

How to create product id when your db auto_increment increases by 10?

I have this weird problem, Azure MySQL database increments by 10 instead of 1 every time I insert a row in it.

So the primary key on every table in that database goes like this - 1,11,21,31,41,51,61,71,81,91,101, and so on.. it was supposed to be 1,2,3,4,5,6,... etc..

I think it is a database specific problem since I have the same code running on localhost and Azure DB. This problem is only on the Azure DB. So I figured out the problem is with clear DB using this 10 increment as mentioned in:

http://www.cleardb.com/developers/help/faq#general_16

So now basically what I want is to make the url look good, with the product id in the URL increment by 1. How are others solving this problem?

Is it by adding a 1 to the end of the product number that you get from the URL and querying the DB or by creating a new column called product id?

Solution

  • You can change auto-increment step back to 1 using:

    SET @@auto_increment_increment=1;

    I would not do it however - it may screw up your Azure/ClearDB installation if you are using replication.

    In general, you should not be fixed up on how exactly primary keys are allocated. Even with auto_increment_increment=1 there is no 100% guarantee that keys will be allocated without any gaps - typical reason for gaps are aborted transactions.