Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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) –  Derek Downey Sep 12 '11 at 13:56

2 Answers 2

up vote 6 down vote accepted

The @@ prefix will modify settings in a session scope. Try:

SET GLOBAL auto_increment_increment=1;
share|improve this answer
    
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 –  Derek Downey Sep 12 '11 at 13:53
    
Hmm.. so it is. Restoring my first answer. –  Morgan Tocker Sep 12 '11 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? –  Bernardo Oliveira Sep 12 '11 at 17:23
    
If you can't SET GLOBAL, each session you don't want this behavior run SET auto_increment_increment=1; –  Morgan Tocker Sep 14 '11 at 0:45

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.

share|improve this answer
    
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? –  Bernardo Oliveira Sep 12 '11 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 '11 at 20:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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