MySQL has a handy feature, that allows you to turn an INSERT into an UPDATE if a unique or primary key duplication is detected:

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

A common usage pattern for this is “lazy initialization“ of a row in a database, which is exactly what my team was using it for yesterday to solve a problem in the backend for version 2.0 of the MySQL Enterprise Monitor. However, we ran into an issue where Hibernate would throw an exception complaining that when the INSERT was turned into an UPDATE, it couldn‘t retrieve the generated primary key value (we are using auto increments on this particular table, as it‘s not a high insertion-rate table).

To understand why this happens, you have to know a little bit about how Statement.getGeneratedKeys() works with MySQL‘s JDBC driver. When an auto increment value is generated by MySQL, that value is returned on the wire as part of the message back to the client that contains the update count, warnings, etc. When an application asks for the generated keys from MySQL‘s JDBC driver, it takes the value returned on the wire and crafts a “synthetic“ result set to represent it.

In the case where “ON DUPLICATE KEY UPDATE“ is in play, it turns out that the value returned on the wire, by default is indeterminate, which we would‘ve learned from reading our own manual more carefully:

“If a table contains an AUTO_INCREMENT column and INSERT … UPDATE inserts a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. If the statement updates a row instead, LAST_INSERT_ID() is not meaningful.”

It turns out, the answer to this is to add a little assignment to the UPDATE clause:

INSERT INTO table (a,b,c) VALUES (1,2,3)
ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)

With that little “id=LAST_INSERT_ID(id)” bit added to the INSERT statement, the server now returns the already existing auto increment value back to the client, and Hibernate is happy again.