0

I have mysql 5.6 production database. All the tables id are auto incremented by 10 and some tables use ISAM engine and some use InnoDB. I want to change auto increment to 1 to all the tables without any downtime. What is best way to resolve this issue?

avanthika
  • 1
  • http://stackoverflow.com/questions/206751/mysql-autoincrement-column-jumps-by-10-why – Darshan Mehta Mar 01 '16 at 11:56
  • thank you. i have followed the link did change the auto increment to 1. when i do SHOW VARIABLES LIKE 'auto_inc%'; i can see the value changed which is good but when i insert new row. still refer to old auto increment. Do i need to restart mysql server to apply the changes i made – avanthika Mar 01 '16 at 13:30
  • http://stackoverflow.com/questions/11580153/auto-increment-does-not-change :) – Darshan Mehta Mar 01 '16 at 15:54
  • I have tried alter the table with auto increment, the command successful. But when i insert the new rows still the auto increment is not working with the latest rows. I am not expecting to change the old records id but atleast for the new one – avanthika Mar 03 '16 at 09:33
  • Looks like auto increment increment is a global variable. If we change it, it will be applicable to all the tables. Here's how to change it : http://stackoverflow.com/questions/14373582/how-to-auto-increment-by-2-for-a-perticular-table-in-mysql – Darshan Mehta Mar 03 '16 at 09:52
  • 'auto_increment_increment =1' 'auto_increment_offset','1' is clearly showing it has changed when i do SHOW VARIABLES LIKE 'auto_inc%'. But it is not effecting the tables when i make new insert to the tables. – avanthika Mar 03 '16 at 10:34
  • finally i found solution change auto increment in config file and restart the server. it worked. – avanthika Mar 03 '16 at 13:52

0 Answers0