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?

fyi, I just ran into the same issue using spring-boot-2.0.5.RELEASE — membersound
– membersound, Commented Sep 18, 2018 at 12:10

elabs · Accepted Answer · 2015-05-20 20:40:34Z

10

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.

edited May 20, 2015 at 20:40

answered May 20, 2015 at 20:31

elabs

1381 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user404345 Over a year ago

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 :(

Moussa · Accepted Answer · 2022-12-08 09:39:45Z

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.

Collectives™ on Stack Overflow

Mysql on duplicate key update with primary key and unique key

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related