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.
(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; dofor 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]> selectcount(*) FROM (select id,count(*) from test1 groupby id havingcount(*) > 1) x;
+----------+
| count(*) |
+----------+
| 61 |
+----------+
Attachments
Issue Links
is duplicated by
MDEV-25022Incorrect "Duplicate entry for key 'PRIMARY'" error with AUTO_INCREMENT and a composite PRIMARY key
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.
Alexander Keremidarski
added a comment - 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.
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
createorreplacetable t (pk int auto_increment, x int, primarykey(pk), uniquekey(x)) engine=myisam
with system versioning partition by system_time interval 2 day
(partition p1 history, partition pn current);
load data infile 'load.data'ignoreintotable t (x);
--remove_file $mysqld_datadir/test/load.data
select * from t;
droptable t;
result
createorreplacetable t (pk int auto_increment, x int, primarykey(pk), uniquekey(x)) engine=myisam
with system versioning partition by system_time interval 2 day
(partition p1 history, partition pn current);
load data infile 'load.data'ignoreintotable t (x);
Warnings:
Warning 1062 Duplicate entry '1'forkey'x'
select * from t;
pk x
1 1
3 2
droptable t;
(sorry for repeated edit)
Nayuta Yanagisawa (Inactive)
added a comment - - edited 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)
Nayuta Yanagisawa (Inactive)
added a comment - 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.
Nayuta Yanagisawa (Inactive)
added a comment - 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.
https://github.com/MariaDB/server/pull/1696
Bug
Blocker
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.