I have the following table:

CREATE TABLE `events_active_test` (
`timestamp` TIMESTAMP NOT NULL,
`timestamp_earliest` TIMESTAMP NOT NULL,
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`source` CHAR(50) NOT NULL COLLATE 'utf8mb3_general_ci',
`repeats` SMALLINT(5) UNSIGNED NOT NULL DEFAULT '1',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `source` (`source`) USING BTREE,
INDEX `timestamp` (`timestamp`) USING BTREE,
INDEX `timestamp_earliest` (`timestamp_earliest`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB
;

Then I insert some test rows (do this 5 times):

  INSERT INTO events_active_test
   (timestamp,timestamp_earliest,source)
  VALUES
   (NOW(),NOW(),'test')
  ON DUPLICATE KEY UPDATE repeats=repeats+1,timestamp=NOW();

When I then look at the table id = 1 and repeats = 5 as expected

Now I insert a non duplicate key (test2):

  INSERT INTO events_active_test
   (timestamp,timestamp_earliest,source)
  VALUES
   (NOW(),NOW(),'test2')
  ON DUPLICATE KEY UPDATE repeats=repeats+1,timestamp=NOW();

The new row now has id = 6 and repeats = 1.

This really baffled me as 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.

In my case the majority of my INSERT's are duplicates causing massive jumps and gaps in the auto_increment numbers.

Is this correct behaviour? Also is there an overhead of having to update the tables auto_increment metadata counter unnecessarily for every failed insert?


--
Richard