Bug #57698 StatementRegressionTest.testGetGeneratedKeysAllCases causes false negative
Submitted: 24 Oct 2010 21:25 Modified: 19 Nov 2010 14:55
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.13 OS:Any
Assigned to: CPU Architecture:Any

[24 Oct 2010 21:25] Elena Stepanova
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):


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.


This is exactly what happens in the test. On a newly created table, it executes
which allocates one autoinc value, sets the next autoinc value to 2, and inserts the key 1; 
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:
In createGGKTables explicitly set engine for the table to MyISAM


Loosen the requirements on the key sequence generation.


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.
[12 Nov 2010 19:48] Erica Moss

I can verify that explicitly creating the table with engine=myisam in... 

private void createGGKTables()

...does resolve this test failure.  If doing so isn't creating some other unforeseen side effect than it would help clear away another spurious test error.
[19 Nov 2010 14:55] Tonci Grgin
Erica, Elena, I have changed the code in my tree and it works now. However, I will hold the patch till all the failures I see in StatementRegressionTest against 5.5+ server are fixed.