Am 31.10.23 01:21 schrieb Richard Ellerbrock via discuss:

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

_______________________________________________ discuss mailing list -- discuss@lists.mariadb.org To unsubscribe send an email to discuss-leave@lists.mariadb.org

Hello Richard, I too have observed this many times. In a very simple environment. The primary key is defined as auto_increment. I insert a record (manually), but I miss that a field in the record contains some illegal value. The INSERT fails but the auto_increment value is still increased by 1. I don't know if that's "correct" or a bug. I just haven't taken the time to study documentations on this topic.

Norbert