mySQL auto increment increasing by 10 (ClearDB & Node)

Question

I have a simple table in ClearDB:

CREATE TABLE `users` (

`id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(100) DEFAULT NULL,
`message` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)

) ENGINE=INNODB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

I'm using Node to insert data into the table via:

var post = {username: response.user.name, message: message.text};

           connection.query("INSERT INTO users SET ?", post, function(err, rows, fields) {

                 if (err) {
                       console.log('error: ', err);
                       throw err;
                 }

           });

However whenever I insert my id field increases by 10 rather than 1 (and it started off with 12:

id username message

12 test test

22 test test

32 test test

42 test test

Any idea why this is happening?

Thanks!

Answer 1

It is ClearDB's strategy. Here is the explanation from ClearDB's website.

You can't change this auto_increment step when you are using ClearDB.

---

This is the explanation from the link above.

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.

Answer 2

I had the same problem. After some digging I found that I can change the auto_increment

Check first what value it is

SELECT @@auto_increment_increment

Then change it

SET @@auto_increment_increment=1;

Answer 3

This seems to be because of AUTO_INCREMENT field
remove AUTO_INCREMENT=11