CODEWITHSUNDEEP
mysqlauto-increment
user427969
user427969

Reputation: 3896

cannot change auto_increment_increment value in mysql

I have a database whose one table started auto incrementing the values by 10. I never set that up explicitly but for some reason it is doing that. I tried to reset the auto_increment_increment value by 1 using following:

SET @@auto_increment_increment=1;

But it doesn't change! I tried to do it using command line but still it doesn't do it.

One more thing, on my local machine auto_increment works fine (it is set to 1), but this is the problem on my server. When I used mysql on command line, i logged in as a "root" user.

Can somebody please suggest what could be wrong with my database? Thanks for help.

Upvotes: 4

Views: 3089

Answers (2)

user427969
user427969

Reputation: 3896

Thanks WoLpH for all your help.

I finally found out what was causing this problem! The value of auto_increment_increment was altered in .\xampp\mysql\bin\my.ini file. That is why, even when i tried to replace it using the following query, it didn't change:

SET @@auto_increment_increment=1;

Hope this helps someone.

Upvotes: 3

Wolph
Wolph

Reputation: 80011

Are you sure that it's the auto increment value that's the problem here? If you insert a row in a transaction and rollback, it will also increment and you will never get the id back.

If your code is somehow inserting many rows and rolling them all back, than you'll see this behaviour too.

Upvotes: 0

Related Questions