1

I am facing a problem with mysql auto-increment , actually it is not creating any problem. I am just confused why mysql auto-increment field getting even data. Evertytime a new entry its creating like this 2,4,6,8,10....... insteed of 1,2,3,4,5,6.......

Have you know about this or facing situation like this.

Sonu Sindhu
  • 1,752
  • 15
  • 25
  • are you using InnoDB? If so you might see gaps due to roll backs or bulk inserts: http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html – davek Jul 23 '13 at 09:37
  • 3
    Has [`auto_increment_increment`](http://dev.mysql.com/doc/en/replication-options-master.html#sysvar_auto_increment_increment) been set to 2? – eggyal Jul 23 '13 at 09:39
  • @eggyal how we will set – Sonu Sindhu Jul 23 '13 at 09:43
  • "How we will set" is incomplete sentence, mind finishing it so there's a meaning to it? – N.B. Jul 23 '13 at 10:03
  • Check this Post http://stackoverflow.com/questions/1686327/change-the-step-auto-increment-fields-increment-by – Mohamed Navas Jul 23 '13 at 10:09

2 Answers2

1

SHOW VARIABLES LIKE 'auto_inc%';

use it to check autoincrement and set it to proper values with 

SET @@auto_increment_increment=1;

It will set auto increment to 1

Robert
  • 19,800
  • 5
  • 55
  • 85
0

To show auto_increment and its offset values, should use SHOW VARIABLES LIKE 'auto_inc%'

For resetting AUTO_INCREMENT values, use :

SET @@auto_increment_increment = 1;
SET @@auto_increment_offset = 1;

To set globally you should add @@GLOBAL :

SET @@GLOBAL.auto_increment_offset = 1;
SET @@GLOBAL.auto_increment_increment = 1;
Hamed Kamrava
  • 12,359
  • 34
  • 87
  • 125