Mysql on duplicate key update with primary key and unique key

Question

I have a table with an auto incremented primary key and also a unique key:

CREATE TABLE `product` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`canonical_url` varchar(750) CHARACTER SET latin1 NOT NULL,
...
PRIMARY KEY (`id`),
UNIQUE KEY `canonical_url_idx` (`canonical_url`)

Im using the on duplicate key feature to update records if the canonical_url already exists:

"INSERT INTO product(id, canonical_url, name VALUES(?, ? ?) ON DUPLICATE KEY UPDATE name=VALUES(name), id=LAST_INSERT_ID(id)"

KeyHolder productKeyHolder = new GeneratedKeyHolder();
jdbcTemplate.update(conn -> {
  PreparedStatement ps = conn.prepareStatement(productSql, new String[] {"id"});
  ps.setInt(1, id);
  ps.setString(2, canonicalUrl);
  ps.setString(3, name);
}, productKeyHolder);

final int productId = productKeyHolder.getKey().intValue();

The problem is that I'm getting this error:

The getKey method should only be used when a single key is returned. The current key entry contains multiple keys: [{GENERATED_KEY=594}, {GENERATED_KEY=595}]

Does anyone know what is causing this?

Hadst

fyi, I just ran into the same issue using spring-boot-2.0.5.RELEASE – Counterstatement

Answer 1

I just ran into this myself. According to the documentation here:

https://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, and 2 if an existing row is updated.

So when your query executes, if a new record is inserted it's ID is returned. If the record already exists then the existing record is updated. If no update is needed because the values all match then an ID is returned and the number of rows modified is 0. However, if the record is updated, the ID is returned and the number of rows modified is 2. The keyholder is assuming two rows have been modified (even though only one has been) and is incorrectly returning the ID plus the next sequential ID (i.e. the ID plus 1).

To work around this I just checked the count in the getKeys before attempting to call getKey. If there is more than one value in getKeys I won't call getKey.

Inflow

Thanks! I ended up doing something similar but using JDBC directly. In my case I wanted to get the ID even during an update. With JDBC I can still get the ID even if two rows are returned but spring's template blows up :( – Hadst

Answer 2

Assuming that in case of duplicate the temporarily inserted row will have a bigger id, here is a work around :

public static int getGeneratedKeyOnDuplicate(KeyHolder keyHolder)
{
    int id = 0;
    List<Map<String, Object>> keyList = keyHolder.getKeyList();
    if (keyList.size() == 1 && keyHolder.getKey() != null)
        id = keyHolder.getKey().intValue();
    else if (keyList.size() > 1)
    {
        id = keyList.stream()
                .map((Map<String, Object> key) -> ((Number) key.get("GENERATED_KEY")).intValue())
                .min(Comparator.comparing(Integer::valueOf))
                .get();
    }
    return id;
}

But please aware that keyHolder.getKey() is instance of java.math.BigInteger so I am not sure how this will work with id that has a very big value.

Dervish