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
-
-
- Closed
-
Activity
Yes, `waste of auto-increment counter` refers to the gap , but decrementing next_insert_id prevents such a gap at least on bulk load including duplicate keys (see an example below). However, if we stop decrementing next_insert_id, such a gap occurs even with MyISAM at least on bulk load including duplicate keys (see an example below).
On the other hand, I agree that causing gaps in the auto-increment column is trivial issue compared with generating duplicated values.
test
--source include/have_partition.inc
|
|
|
let $mysqld_datadir= `select @@datadir`; |
--write_file $mysqld_datadir/test/load.data
|
1
|
1
|
2
|
EOF
|
|
|
create or replace table t (pk int auto_increment, x int, primary key(pk), unique key(x)) engine=myisam |
with system versioning partition by system_time interval 2 day |
(partition p1 history, partition pn current); |
load data infile 'load.data' ignore into table t (x); |
--remove_file $mysqld_datadir/test/load.data
|
select * from t; |
drop table t; |
result
create or replace table t (pk int auto_increment, x int, primary key(pk), unique key(x)) engine=myisam |
with system versioning partition by system_time interval 2 day |
(partition p1 history, partition pn current); |
load data infile 'load.data' ignore into table t (x); |
Warnings:
|
Warning 1062 Duplicate entry '1' for key 'x' |
select * from t; |
pk x
|
1 1
|
3 2
|
drop table t; |
(sorry for repeated edit)
I've created a draft pull request, for the ease of discussion, along with the former easy way. https://github.com/MariaDB/server/pull/1692
I think I could finally find a plausible way to fix the present bug after much deliberation.
This bug is introduced by 9cba59749e1b5a39a9e3a0a5b8bd762507245f9, which fixed MDEV-17333. We can almost revert the commit while preventing recurrence of MDEV-17333 by updating next_auto_inc_val in ha_partition::write_row() only when handler::ha_write_row() returns no error. The point is to prevent next_auto_inc_val from being incremented rather than to restore it after increment.
Hello,
If "waste of auto-increment counter on duplicate key errors" means gap in the sequence this is a well known and documented behaviour at least for InnoDB engine so it is much less of a problem than generating duplicate values.