I am trying to change what the next auto increment value will be for a given table using MySQL workbench. The next auto increment value is currently set to 3, and I am trying to make it 2. Whenever I try to apply the changes, the workbench runs the following code.
ALTER TABLE `mysql_schema`.`mysql_table`
AUTO_INCREMENT = 2 ;
After running this code however, the change is not applied, and the next auto increment value is still three. What's the problem? Why is the auto increment value not changing? I have tried to manually execute the code, but it did not work either.
You cannot reset the counter to a value less than or equal to any that have already been used.
For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one.
For InnoDB, if the value is less than the current maximum value in the column, no error occurs and the current sequence value is not changed.