On Tue, 31 Oct 2023 11:21:54 +1100 Richard Ellerbrock via discuss <discuss@lists.mariadb.org> wrote:
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