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 -- [email protected]
To unsubscribe send an email to [email protected]
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
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
