MySQL Bugs: #39956: getGenerateyKeys() does not respect auto_increment

Bug #39956	getGenerateyKeys() does not respect auto_increment_increment variable
Submitted:	9 Oct 2008 16:00	Modified:	14 Oct 2008 14:21
Reporter:	Christian Schwanke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	5.1.6	OS:	Any
Assigned to:	Mark Matthews	CPU Architecture:	Any
Tags:	autoincrement, generated keys

Description:
Let be first describe our environment briefly:
- we are using a Master-Master Setup running MySQL Server 5.0 on Debian-Systems.
- all tables use InnoDB
- our Software uses the Spring-JDBC-Layer to access the database
- in order to prevent key conflicts on auto-generated keys we have configured our server with an auto_increment_increment of 2

We have optimized our application to perform batch-inserts using prepared statements. Afterwards we need to fetch all the generated keys using a GeneratedKeyHolder instance from the Spring framework.
The keys generated by the database are correctly increased by 2, eg.
row1 -> 1
row2 -> 3
row3 -> 5
...

However, the current implementation in the StatementImpl-class is generating the new IDs itself and is not respecting the auto_increment_increment value configured on the server. Therefore, the keys generated within the StatementImpl class are only incremented by 1, resulting in:
row1 -> 1
row2 -> 2
row3 -> 3
...

This is a critical problem since it effectivly kills the data integrity when we use these IDs to update foreign key columns.

How to repeat:
1. Configure your mysql-server to increment autoincrement values by 2 (in my.cnf):

[mysqld]
...
auto_increment_increment = 2
...
You can reproduce this without having to setup replication or anything - a single local mysql instance is sufficient.

2. Create a simple test table:

CREATE DATABASE `testdb`;
CREATE TABLE  `testdb`.`keytest` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `text` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

3. Make a batch insert into this table using JDBC

Connection con = DriverManager.getConnection("jdbc:mysql://localhost/testdb", "root", "");
Statement stmt = con.createStatement();
stmt.execute("INSERT INTO keytest (text) VALUES ('row1'), ('row2')", new String[]{"id", "id"});

4. Retrieve the generated keys from the StatementImpl and check the results

ResultSet rs = stmt.getGeneratedKeys();
rs.first();
Integer id1 = rs.getInt(1);
rs.next();
Integer id2 = rs.getInt(1);
Assert.assertEquals(Integer.valueOf(id1 + 2), id2);

Suggested fix:
The problem is the "getGeneratedKeysInternal()" method in class StatementImpl, beginning on line 1790.
The current implementation fetches the first auto generated value using the getLastInsertID() method and the number of affected rows using the getUpdateCount() method. The important piece of code is on line 1817:
...
row[0] = Long.toString(beginAt++).getBytes();
...
This will always increment the next key by 1, regardless of the current server setting. 

Our suggestion is as follows:
fetch the server setting for the auto_increment_increment variable and use that value to increase the beginAt-value. If no auto_increment_increment is configured, the default value should be set to 1, obviously.
The current server setting can be fetched from the server with this command:

SHOW GLOBAL VARIABLES LIKE 'auto_increment_increment%';

Instead of incrementing beginAt using the "++" operator, the server-increment value should be added instead.

For performance reasons, the value of the server-auto-increment should propably be cached for the lifetime of the connection or maybe even longer. We haven't looked into what caching strategy would be best here.

Patch/Diff for the ConnectionImpl class. The auto_increment_increment variable is added

Attachment: ConnectionImpl.patch (application/octet-stream, text), 1.06 KiB.

Added retrieval of auto_increment_increment variable and modified getGeneratedKeysInternal accordingly

Attachment: StatementImpl.patch (application/octet-stream, text), 1.18 KiB.

Hi Christian and thanks for your report.

Checking http://dev.mysql.com/doc/refman/5.1/en/c-api-prepared-statements.html
this appears more complex than it looks. It seems prior to 5.1, the server returned "normal" update counts for "ON DUPLICATE KEY UPDATE" (for example), and now it returns *2*. This confuses the JDBC driver's code for creating the result set for getGeneratedKeys(), because it also has to support statements of the form you use ("INSERT INTO ... VALUES (...), (....), (...)..."), which will also have an update count > 1.

The server also returns only the "first" generated key for a statement, the rest have to be "computed" (more like "divined" in the case of REPLACE and "ON DUPLICATE KEY UPDATE")...

Let me check on code, probably your problem can be dealt with more quickly than wholesome solution.

Verified as described by looking into latest sources. c/J pays no attention to "auto_increment_increment" variable available since 5.0.2 and 5.0.47-cluster.
This can cause serious problems to users thus such severe triage.

Will notify Mark ASAP.

Fixed for 5.1.7.

An entry was added to the 5.1.7 changelog:

Connector/J ignored the value of the MySQL Server variable auto_increment_increment.

I'm using the mysql-connector-odbc-5.1.7-winx64.msi.  I've linked to MYSQL from Microsoft Access 2007.

I'm having what appears to be the same problem; Microsoft Access is disregarding the auto_increment_increment setting and just adding 1 each time.

Greg, could you please open a new bug report (for c/ODBC) and be more specific there? Probably a good thing would be to provide ODBC traces, see our docs on how to do that.