MySQL Auto_increment going 2 by 2

Question

I installed MySQL Workbench the other day, accessed my company's database and made myself a table to work with. So far so good. The problem is, I noticed my auto_increment is incrementing 2 by 2. For example:

ID    NAME
1     Paul
3     Jack
5     Louis
7     John
...

When I do SHOW VARIABLES LIKE 'auto_inc%' I get this:

'auto_increment_increment', '2'
'auto_increment_offset', '1'

So I tried setting auto_increment_increment to 1 with:

SET @@auto_increment_increment=1

And after verifying again with SHOW VARIABLES LIKE 'auto_inc%' I confirmed it "worked" with the result:

'auto_increment_increment', '1'
'auto_increment_offset', '1'

But my ID's are still being incremented in 2 by 2.

The first time I did it, it worked well and then I closed MySQL Workbench to realize that when I opened it again, auto_increment_increment was set to 2 again. Now I'm trying to do it again, but it doesn't even seem to work anymore.

Can anyone help me with this, please?

Thanks guys.

Answer 1

The prefix that was used:

SET @@auto_increment_increment=1;

Is the same as:

SET @@SESSION.auto_increment_increment=1;

When modifying this setting, it becomes relevant only in your current session.

To make a more permanent fix try:

SET GLOBAL auto_increment_increment=1;

Is the same as:

SET @@GLOBAL.auto_increment_increment=1;

Answer 2

Both variables have global and session values. So it's very likely you only changed the session's value which was gone when you closed the MySQL Workbench.

Another caveat to pay attention to, is that

these variables control the behavior of all AUTO_INCREMENT columns in all tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or until mysqld is restarted. If the local value is set, the new value affects AUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

What also might trick you, is the way the next autoincrement value is calculated when you change the increment size. It doesn't use the last stored column value, but it is calculating the next highest value using the formula

auto_increment_offset + N × auto_increment_increment

while N is an integer, so that the new value is greater than the largest existing one.

See the auto_increment_increment documentation how it works in detail.