Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.3.21, 10.3.22, 10.4.12, 10.3(EOL), 10.4(EOL)
-
Ubuntu 18.04.2
Description
When a table contains a compound key where the second column is used in the partitioning rule the auto_increment gives out duplicate numbers with concurrent queries.
Steps to reproduce:
table defenition:
CREATE DATABASE `creatable`; |
CREATE TABLE `test1` ( |
`id` bigint(20) NOT NULL AUTO_INCREMENT, |
`c1` int(11) NOT NULL DEFAULT 0, |
`c2` int(11) NOT NULL, |
`c3` int(11) NOT NULL, |
`c4` int(11) NULL, |
PRIMARY KEY (`id`,`c1`), |
UNIQUE KEY `unique_key` (`c1`,`c2`,`c3`) |
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
PARTITION BY RANGE (`c1`) |
(PARTITION `partition_1` VALUES LESS THAN (2) ENGINE = InnoDB, |
PARTITION `partition_2` VALUES LESS THAN (3) ENGINE = InnoDB |
);
|
preparation:
for i in {0..1000}; do mysql creatable -e "INSERT INTO test1(c1,c2,c3) VALUES (1,1,$i),(2,2,$i)"; done |
reproduce:
for x in 1 2; do for i in {0..1000}; do mysql creatable -e "INSERT INTO test1(c1,c2,c3) VALUES ($x,$x,$i), ($x,12,$i), ($x,13,$i), ($x,14,$i), ($x,15,$i) on duplicate key update c4 = 1"; done & done |
result:
MariaDB [creatable]> select count(*) FROM (select id,count(*) from test1 group by id having count(*) > 1) x; |
+----------+ |
| count(*) | |
+----------+ |
| 61 |
|
+----------+ |
Attachments
Issue Links
- is duplicated by
-
MDEV-25022 Incorrect "Duplicate entry for key 'PRIMARY'" error with AUTO_INCREMENT and a composite PRIMARY key
- Closed