Description:
StatementRegressionTest.testGetGeneratedKeysAllCases creates a table with an auto-increment column using default storage engine, and then inserts (by mixed-mode REPLACEs) a few values into the table, expecting auto-increment values to be generated without gaps.
When default storage engine is InnoDB, and server was started with default value of innodb_autoinc_lock_mode=1, this expectation is incorrect.
The behavior is explicitly described in the manual (http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html):
<quote>
Consider a “mixed-mode insert,” where a “simple insert” specifies the auto-increment value for some (but not all) resulting rows. Such a statement will behave differently in lock modes 0, 1, and 2. For example, assume c1 is an AUTO_INCREMENT column of table t1, and that the most recent automatically generated sequence number is 100. Consider the following “mixed-mode insert” statement:
INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
With innodb_autoinc_lock_mode set to 1 (“consecutive”), the four new rows will also be:
+-----+------+
| c1 | c2 |
+-----+------+
| 1 | a |
| 101 | b |
| 5 | c |
| 102 | d |
+-----+------+
However, in this case, the next available auto-increment value will be 105, not 103 because four auto-increment values are allocated at the time the statement is processed, but only two are used.
</quote>
This is exactly what happens in the test. On a newly created table, it executes
REPLACE INTO testggk VALUES (NULL, 0)
which allocates one autoinc value, sets the next autoinc value to 2, and inserts the key 1;
then
REPLACE INTO testggk VALUES (1, 1), (NULL, 2), (NULL, 3)
which allocates three autoinc values, sets the next autoinc value to 5, and inserts keys 2 and 3.
and then
REPLACE INTO testggk VALUES (2, 4), (NULL, 5), (NULL, 6)
which inserts keys 5 and 6 (not 4 and 5 as the test expects).
MyISAM works essentially different in this regard.
The test fails with 5.5 version on all platforms because it has default storage engine InnoDB, and with 5.1 in release tests on Windows because the Windows installer sets InnoDB as a default engine if not asked otherwise.
How to repeat:
Run StatementRegressionTest.testGetGeneratedKeysAllCases or inspect the code.
Suggested fix:
1.
In createGGKTables explicitly set engine for the table to MyISAM
or
2.
Loosen the requirements on the key sequence generation.
or
3.
Start server with innodb_autoinc_lock_mode=0 (traditional mode). Unfortunately it is not a dynamic variable, so it cannot be changed on the already running server and thus does not look like a universal solution.
