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