MySQL Auto increment primary key increases by 10

Question

On azure I created a new MySQL Database instance. In this db I create a table using this script:

CREATE TABLE ROLES(
  ID INTEGER PRIMARY KEY AUTO_INCREMENT,
  ROLE_NAME VARCHAR(30) NOT NULL
);

Then I insert values using this script:

INSERT INTO `beezzy`.`roles` (`ROLE_NAME`) VALUES ('admin');
INSERT INTO `beezzy`.`roles` (`ROLE_NAME`) VALUES ('owner');
INSERT INTO `beezzy`.`roles` (`ROLE_NAME`) VALUES ('consultant');

after execution table contains such rows:

Why DB generates IDs like '11' and '21'? I run the same script on my local machine and everything works fine. IDs was '1', '2', '3'

Answer 1

Please run the following query.

SELECT @@auto_increment_increment

If the value is more than 1 then set it to 1 by the following query:

SET @@auto_increment_increment=1;

Note: This change is visible for the current connection only.

EDIT:

In order to set it globally so that other connections can also see the change you need to set it for global and session too.

SET @@GLOBAL.auto_increment_increment = 1;

SET @@SESSION.auto_increment_increment = 1;

So other connections can see this change now.

More:

This value will be reset if you restart your MySQL server. In order to make this change permanent you need to write this variable under [mysqld] secion in your my.cnf [for linux] or my.ini [for windows] file.

[mysqld]
auto-increment-increment = 1

Answer 2

Your autoincrement is probably 10, however this is probably by design. Azure uses ClearDB which uses an autoincrement of 10 with a reason: namely replication.

When I use auto_increment keys (or sequences) in my database, they increment by 10 with varying offsets. Why?

ClearDB uses circular replication to provide master-master MySQL support. As such, certain things such as auto_increment keys (or sequences) must be configured in order for one master not to use the same key as the other, in all cases. We do this by configuring MySQL to skip certain keys, and by enforcing MySQL to use a specific offset for each key used. The reason why we use a value of 10 instead of 2 is for future development.

You should not change the autoincrement value.

cleardb faq