8

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.

2
  • 2
    I'd be careful changing this value without fully understanding why our company's environment sets that. It's a common variable to change when running in a multi-master replication setup, for instance. Also, can we get a SHOW CREATE TABLE statement for the table you're testing with (testing on production is also a bad idea, btw) Sep 12, 2011 at 13:56
  • This deserves to be an answer I think. Or to be added in an existing one, whatever you prefer. The best action is cautious action - or no action at all - for DBAs that want to keep their job. Oct 15, 2016 at 11:01

2 Answers 2

7

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;
4
  • Huh, I thought @@ was the session attribute. From the mysql manual: "To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@" dev.mysql.com/doc/refman/5.1/en/set-option.html Sep 12, 2011 at 13:53
  • Hmm.. so it is. Restoring my first answer. Sep 12, 2011 at 14:17
  • Hey Morgan, thanks for the reply! But what if I don't want or can't do a SET GLOBAL? Can I set it only for my very specific db/table without having to use the session variables? Sep 12, 2011 at 17:23
  • If you can't SET GLOBAL, each session you don't want this behavior run SET auto_increment_increment=1; Sep 14, 2011 at 0:45
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.

2
  • That's very useful information. Thanks MicSim! Indeed I was changing only the session value. Trying to figure out how to permanently change it but only for one db or table. Any ideas? Sep 12, 2011 at 18:28
  • As the documentation says, it's not possible to set it db or table-wise. I'm sorry, I can't help you here. Maybe you are trying to achieve something with the autoincrement feature that it wasn't intended for.
    – MicSim
    Sep 12, 2011 at 20:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.