On Tue, 31 Oct 2023 11:21:54 +1100
Richard Ellerbrock via discuss
it appears that when using INSERT ON DUPLICATE UPDATE with a primary auto_increment column the auto_increment is increased even if the INSERT fails and the UPDATE occurs.
Is this correct behaviour?
This seems to be the expected behaviour. See, e.g. https://mariadb.com/kb/en/auto_increment-on-insert-on-duplicate-key-update/:
It[']s part of the architecture that it[']s easier to allocate an auto_increment value before it detected that a duplicate occurs
The AUTO_INCREMENT FAQ doesn't explicitly state that values are allocated before checking validity (and then potentially discarded), but it does mention that gaps in the numbers are to be expected:
How do I renumber rows once I've deleted some in the middle?
Typically, you don't want to. Gaps are hardly ever a problem; if your application can't handle gaps in the sequence, you probably should rethink your application.
-- https://mariadb.com/kb/en/auto_increment-faq/ There is advice on the Internet that changing the AUTO_INCREMENT locking mode in InnoDB may give you different behaviour https://mariadb.com/kb/en/auto_increment-handling-in-innodb/, but that change also comes with side effects that might be undesirable. -- Best regards, Ivan