MySQL Workbench - Altering Next Auto Increment Value does not Work

Question

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.

I doubt that you can set your auto increment lower than existing values. — Mihai, Mar 26 '14 at 18:56
@MattPileggi My auto increment value is now only 1. I had 1 and 2, but the row with 2 has been deleted. — DaveTheMinion, Mar 26 '14 at 19:03

score 9 · Answer 1 · answered Aug 27 '15 at 14:11

I know this answer is a long time after the initial request, but it may help out people with the same problem.

So, you had two rows, deleted one of them and the next increment value is 3. To change this '3' to '2' and allow future incremental values to flow from '2' and not '3', please try the following.

From the 'Schemas' column (on the left):

Right-click on the table name.
Select 'Alter Table' (or press the 'spanner' icon).
Look down to the 'grey area' that has the 'Apply' and 'Revert' buttons
On the left of this 'grey area' are 6 tabs.
One of these tabs is the 'Options' tab - Select the Options tab.
In the 'General Options' section is an 'Auto Increment' box
Type '2' into the 'Auto Increment' box and press the 'Apply' button.
In the resultant box, press 'Apply' and then the 'Finish' buttons.
Return to your table and add a new row and press the Apply button.
The value of incremental column will now be '2'.

I hope this works for you.

score 2 · Accepted Answer · answered Mar 26 '14 at 18:58

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.

MySQL official alter doc

score 1 · Answer 3 · edited May 23 '17 at 12:00

1

see How to set initial value and auto increment in MySQL?

there is a comment there about spaces in-between the AUTO_INCREMENT and the "=" and the value. Remove the spaces. and try that.

edited May 23 '17 at 12:00

Community

1
1

answered Mar 26 '14 at 19:00

dboals

610
4
10

score 1 · Answer 4 · answered Jan 14 '20 at 22:21

In MySQL Workbench, - click on the table so you're editing it - at the very bottom of that box, you'll see other columns, specifically: "Columns","Indexes","Foreign Keys","Triggers","Partitioning","Options","Inserts","Privileges" - click on "Options" - here you can set the Auto Increment to what you want.

MySQL Workbench - Altering Next Auto Increment Value does not Work

4 Answers4