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
I do not think that anything changed in this area in InnoDB between 10.2 and 10.3. The persistent AUTO_INCREMENT (MDEV-6076) was in 10.2.4 already.
But, I do see many changes to partitioning, in particular:
git log -Gnext origin/10.2..origin/10.3 sql/ha_partition.cc
|
includes Adding support for auto_increment in the partition engine, which would be my first suspect. I hope that Kentoku can debug this.
The buggy behavior reported in the following blog post (in Japanese) seems to relate to the present bug. https://techblog.gmo-ap.jp/2020/05/26/mariadb_auto_increment/
The bug happens with concurrent INSERTs so MyISAM and Aria are "immune", but RocksDB is affected too hence the bug seems to be with partitioning engine and not InnoDB specific.
Reproduced in mariadb-10.3.19 but not in mariadb-10.3.18. The bug seems to be introduced by c9cba59749e1b5a39a9e3a0a5b8bd762507245f9. I will look into this further.
In my understanding, `part_share->next_auto_inc_val` is literally shared stuff, and thus we should not decrement it without careful synchronization. The current implementation of restore_auto_increment() seems to do a sort of blind write.
An easy way to fixing the bug without reoccurring MDEV-17333 is to stop decrementing both `next_insert_id` and `next_auto_inc_val`. However, this results in a waste of auto-increment counter on duplicate key errors.
Another possible fix would be to decrement `next_insert_id` only and calculate `first_val`, which determines auto_inc_interval_for_cur_row.minimum(), based on reserved values as InnoDB handler does. Currently, the calculation of `first_val` is based on next_auto_inc_val.
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
|
|
|
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.
Thanks a lot! Reproduced as described on 10.3-10.5, no failure on 10.2:
./mtr --start-and-exit --mysqld=--default-storage-engine=innodb --mysqld=--innodb --mysqld=--partition/10.3/client/mysql --defaults-file=/10.3/mysql-test/var/my.cnf --database=test -e "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 );"for i in {0..1000}; do /10.3/client/mysql --defaults-file=/10.3/mysql-test/var/my.cnf --database=test -e "INSERT INTO test.test1(c1,c2,c3) VALUES (1,1,$i),(2,2,$i);"; donefor x in 1 2; do for i in {101..1000}; do /10.3/client/mysql --defaults-file=/10.3/mysql-test/var/my.cnf --database=test -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/10.3/client/mysql --defaults-file=/10.3/mysql-test/var/my.cnf --database=test -e "select count(*) FROM (select id,count(*) from test1 group by id having count(*) > 1) x;"test: /git/10.3/mysql-test$ /git/10.3/client/mysql --defaults-file=/git/10.3/mysql-test/var/my.cnf --database=test -e "select count(*) FROM (select id,count(*) from test1 group by id having count(*) > 1) x;"+----------+| count(*) |+----------+| 597 |+----------+test:/git/10.2/mysql-test$ /git/10.2/client/mysql --defaults-file=/git/10.2/mysql-test/var/my.cnf --database=test -e "select count(*) FROM (select id,count(*) from test1 group by id having count(*) > 1) x;"+----------+| count(*) |+----------+| 0 |+----------+