0

So running MySQL Version 5.5.42-cll

My tables wherever auto_increment are refusing to reset, they're all incremented by 10 and not by 1. Tried changing it in phpmyadmin and no luck.

Tried following statement but it didn't work:

ALTER TABLE tablename AUTO_INCREMENT = 1;

Has anyone had this issue before? I can't truncate the tables as they have a lot of data, I also know that the +10 probably isn't a big deal, but I expect these tables to get large and would rather keep the numbers low and clean.

Any help would be great please

bish
  • 3,381
  • 9
  • 48
  • 69
ls009
  • 45
  • 2
  • 5
  • When you change `auto_increment`, it will affect *new* rows, but it does not change the values in existing rows. – Gordon Linoff Jun 03 '15 at 11:48
  • [auto increment increment](http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_auto_increment_increment) – Mark Baker Jun 03 '15 at 11:51
  • possible duplicate of [MySQL autoincrement column jumps by 10- why?](http://stackoverflow.com/questions/206751/mysql-autoincrement-column-jumps-by-10-why) – bish Jun 03 '15 at 11:54
  • I understand it affects new rows only.. But it's not working, it continues to increment by 10 when new rows are inserted... I know how it's supposed to work, but for some reason this server is just not playing the game – ls009 Jun 03 '15 at 12:39

3 Answers3

2

Might be the auto_increment_increment variable. Try this to reset the increment of auto_increment with:

SET @@auto_increment_increment=1;

from MySQL autoincrement column jumps by 10- why?

Community
  • 1
  • 1
Irvin Lim
  • 2,393
  • 17
  • 20
  • Tried with no luck, it's still incremented by 10 on new rows.. I don't know wtf is going on with it – ls009 Jun 03 '15 at 12:40
  • After trying that, in phpMyAdmin go to home page, Variables, type "increment" and tell us what you see for "auto increment increment". – Marc Delisle Jun 03 '15 at 12:46
  • Damn... it still says 10.. even after running that command :/ – ls009 Jun 03 '15 at 12:50
  • So, change it on the Variables panel via Edit. – Marc Delisle Jun 03 '15 at 12:52
  • After reading https://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html I should also mention I have two servers with data replication running across them, this could be the reason the command is failing? – ls009 Jun 03 '15 at 12:54
  • Not entirely sure.. But trying that command on my local database doesn't work as well, but doing it in phpMyAdmin via the Edit button works. Does the phpMyAdmin solution work for you? – Irvin Lim Jun 03 '15 at 13:00
  • I have no 'Edit' button in Server variables and settings ? – ls009 Jun 03 '15 at 13:10
  • 1
    SET GLOBAL auto_increment_increment=1; – Irvin Lim Jun 03 '15 at 13:15
  • Which phpMyAdmin version? – Marc Delisle Jun 03 '15 at 13:16
  • @MarcDelisle #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation Guessing I need to run it as root or something? – ls009 Jun 03 '15 at 13:17
1

In phpMyAdmin, log in as a privileged user, go to home page, Variables, type "increment". Then for "auto increment increment", click on Edit, set it to the value you wish (1) and Save.

Marc Delisle
  • 8,879
  • 3
  • 29
  • 29
0

Old post, but still useful to know. You should not be changing this value, most DBs have a reason for increasing by 10, and it could lead to future issues if you make this change. For more info, check out this post

korum
  • 172
  • 1
  • 14