Description:
Manual specifies:
'If the value of auto_increment_offset is greater than that of auto_increment_increment,
the value of auto_increment_offset is ignored.'
http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increme...
But it is not completely ignored:
SET @@session.auto_increment_offset=5;
SET @@session.auto_increment_increment=3;
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL);
Returns:
mysql> select * from t1;
+----+
| id |
+----+
| 1 |
| 4 |
| 5 | /* This should have been 7 */
| 8 | /* This should have been 10 */
| 11 | /* This should have been 13 */
+----+
5 rows in set (0.00 sec)
This could 'partially' be explained by the following excerpt from the manual (highlight
added):
'Should one or both of these variables be *changed* and *then* new rows inserted into a
table containing an AUTO_INCREMENT column, the results may seem counterintuitive because
the series of AUTO_INCREMENT values is calculated without regard to any values already
present in the column, and the next value inserted is the least value in the series that
is greater than the maximum existing value in the AUTO_INCREMENT column. In other words,
the series is calculated like so: auto_increment_offset + N × auto_increment_increment'
But, notice that *in this example*, these variables *were not changed*. So likely the
manual is incorrect in two places here and should say something like this:
1. Scrape the 'If the value of auto_increment_offset is greater than that of
auto_increment_increment, the value of auto_increment_offset is ignored.' text
altogether, as the auto_increment_offset settings is not ignored.
2. Change the text above to (removing a part of the first line altogether): 'The results
of these settings may seem counter intuitive since the series of AUTO_INCREMENT values is
calculated *for each value insert* without regard to any values already present in the
column. The value inserted is the least value in the series that is greater than the
maximum existing value in the AUTO_INCREMENT column.' And, Since the formula for
calculating the actual next value is rather complex to write out (and different from the
one stated already), it will likely be best to leave it out.
However, this would still not explain the following example:
------
SET @@session.auto_increment_offset=10;
SET @@session.auto_increment_increment=9;
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL);
mysql> select * from t1;
+----+
| id |
+----+
| 3 | /* This should have been 9 */ < INCORRECT
| 10 | /* This should have been 18 (or is possibly correct, if the manual is updated) */
| 19 | /* This should have been 27 (or is possibly correct, if the manual is updated) */
| 28 | /* This should have been 36 (or is possibly correct, if the manual is updated) */
| 37 | /* This should have been 45 (or is possibly correct, if the manual is updated) */
+----+
5 rows in set (0.00 sec)
How to repeat:
set @@session.auto_increment_offset=5;
set @@session.auto_increment_increment=3;
show session variables like 'auto_increment%';
drop table if exists t1;
create table t1(`id` int AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM;
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL);
select * from t1;
------
SET @@session.auto_increment_offset=10;
SET @@session.auto_increment_increment=9;
show session variables like 'auto_increment%';
drop table if exists t1;
create table t1(`id` int AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=MyISAM;
insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL);
select * from t1;
Suggested fix:
This bug has two parts:
1. Manual/documentation issues
2. Actual faulty behavior of auto_increment_offset/auto_increment_increment settings
Both as described above.
I propose the code is looked at first, and afterward the manual can be amended
accordingly.
