MySQL Bugs: #64220: auto_increment_increment should be independently settable for every database

Bug #64220	auto_increment_increment should be independently settable for every database
Submitted:	3 Feb 2012 13:32	Modified:	8 Jul 2012 10:01
Reporter:	Andreas Delleske	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	5.1.49	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment

Description:
I have set up a cycling replication chain. In order to accomplish this, I had to set auto_increment_offset for every machine and auto_increment_increment to 10 in order to avoid collisions when creating new datasets on different machines (where auto increment is on).

I am only replicating one database of several in the mysql installation. However, as soon as I changed auto_increment_increment value to 10, ALL the auto increment steps are jumping in steps of 10.

This "consumes" not only more numbers than necessary but confuses many installers that assume the increment is always 1, e.g. phpBB.

How to repeat:
Set auto_increment_increment having selected one database and you will see that auto_increment_increment changes for ALL databases on that server. I think this is unwanted, especialle when only one database is in the replication.

Suggested fix:
suto_increment_increment (and auto_increment_offset) should be configurable per database, (if not per table) - not globally per mysql server.

Makes sense as a feature request.

Let me elaborate after 4 months where nothing happened:

This bug IS critical for any webhoster or other persons that have to host more than one database and may need replication only for some, but not all databases.

Many software packages rely on new datasets being in sequence like 1,2,3.. and do not work if the sequence of ID goes 1,11,21,31...

One could say that we might first change all other software projects, I'll object: Raising the stepping with no apparent reson for databases without replication in an unexpected behaviour and therefore bad.

Thanks for taking care. I've not much hope however for I have submitted another request that has been considered good but has not been tackled for several years.

With still some hope..
Andreas