CODEWITHSUNDEEP
mysqlspring-jdbc
user404345
user404345

Reputation:

Mysql on duplicate key update with primary key and unique key

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?

Upvotes: 6

Views: 5136

Answers (2)

Moussa
Moussa

Reputation: 4154

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.

Upvotes: 0

elabs
elabs

Reputation: 138

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.

Upvotes: 10

Related Questions