46

I have a couple tables in which I created an object ID as either an Int or Bigint, and in both cases, they seem to autoincrement by 10 (ie, the first insert is object ID 1, the second is object ID 11, the third is object ID 21, etc). Two questions:

  1. Why does it do that?

  2. Is that a problem?

  • It's not a problem, although it might indicate the wish to have the ability to insert values in between the others and thus relying on the id as ordering criteria. That's not good. – Vinko Vrsalovic Oct 15 '08 at 22:35
  • It could also be a problem if you worried about running out of ID too fast (if you expect LOTS of records), since you're only using 1/10 of the range you have storage for. – acrosman Oct 15 '08 at 22:52
  • 2
    I'd guess that your auto_increment_increment is set to something else than 1 because of replication. With replicated tables one cannot use 1 because of potential key collisions. – tymtam Jul 07 '11 at 04:14

5 Answers5

57

Please do not change the auto_increment_increment. ClearDB is doing this on purpose. It's explained in the documentation:

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.

Reinstate Monica
  • 2,767
  • 3
  • 31
  • 40
user1709374
  • 939
  • 8
  • 5
49

Check to see the seed value of the autoincrement isn't set to 10.

You can check by:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='the_table_you_want';

As noted elsewhere you can change by using the system variable @@set_auto_increment_increment

SET @@auto_increment_increment=1;

If you want to start the values at a number other than one you can go:

ALTER TABLE tbl AUTO_INCREMENT = 100;
JamesSugrue
  • 14,891
  • 10
  • 61
  • 93
  • Oh, I found my problem- it's how auto_increment_increment is set. thanks! –  Oct 15 '08 at 22:43
  • 1
    HQ says no dice on changing it, and MySQL docs say it can't be altered on a per-table basis. So I guess I'll have to live with an upper limit of 1.8 quintillion IDs rather than 18.4 quintillion. –  Oct 15 '08 at 23:26
  • 12
    Your first select statement seems to show what the next value in the sequence is, not how much it increments by. – Nick Rolando Jan 07 '13 at 22:45
20

Thanks @Jim Fiorato for providing the link.

To check how much the auto increment value increments by, use the following query:

SHOW VARIABLES LIKE 'auto_inc%';

+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 4     |
+--------------------------+-------+
user427969
  • 3,836
  • 6
  • 50
  • 75
8

The auto increment increment value is set in the MySQL system variables.

See here: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_auto-increment-increment

Jim Fiorato
  • 4,842
  • 4
  • 28
  • 19
1

autoincriment value can jump if using insert with IGNORE attribute in case when record was not created

insert IGNORE into my_table set column=1
kimon
  • 11
  • 1