[MDEV-25357] AUTO_INCREMENT on secondary column can result in duplicate keys when using Aria storage engine Created: 2021-04-07  Updated: 2023-04-27

Status: Confirmed
Project: MariaDB Server
Component/s: Storage Engine - Aria
Affects Version/s: 5.5, 10.0, 10.1, 10.4.13, 10.4.17, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Martijn Assignee: Alexey Botchkov
Resolution: Unresolved Votes: 0
Labels: auto_increment
Environment:

Linux


 Description    

CREATE TABLE `test` (
 
  `name` varchar(32) NOT NULL,
 
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 
  PRIMARY KEY (`name`,`id`)
 
) ENGINE=Aria

Start two or more connections running

INSERT INTO test (name) VALUES ('foo')

This will eventually result in a duplicate entry error (1062).
(within a couple of hundred rows in my test setup with three connections)

This does not happen when using the MyISAM storage engine.

 Comments   
Comment by Martijn [ 2021-04-07 ]

Perhaps worth noting that this feature (having AUTO_INCREMENT on a secondary column) is not mentioned on https://mariadb.com/kb/en/auto_increment/

It is however mentioned in the MySQL manual (for MyISAM):

For MyISAM tables, you can specify AUTO_INCREMENT on a secondary column in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column) + 1 WHERE prefix=given-prefix.

https://dev.mysql.com/doc/refman/8.0/en/example-auto-increment.html

Comment by Alice Sherepa [ 2021-04-12 ]

thank you, repeatable on 5.5-10.5, getting "Duplicate entry" errors:

./mtr --start-and-exit
 
client/mysql --defaults-file=mysql-test/var/my.cnf --database=test -e "CREATE TABLE t1 ( n varchar(32) NOT NULL, id int(10) unsigned NOT NULL auto_increment, PRIMARY KEY (n,id)) engine=aria;"
 
for i in {0..500}; do client/mysql --defaults-file=mysql-test/var/my.cnf --database=test -e  "INSERT INTO t1 (n) VALUES ('foo');"; done &
 
for i in {0..500}; do client/mysql --defaults-file=mysql-test/var/my.cnf --database=test -e  "INSERT INTO t1 (n) VALUES ('foo');"; done &

KB says "In some storage engines (including the default InnoDB), if the key consists of multiple columns, the AUTO_INCREMENT column must be the first column. Storage engines that permit the column to be placed elsewhere are Aria, MyISAM, MERGE, Spider, TokuDB, BLACKHOLE, FederatedX and Federated.", so it is mentioned, maybe not explained. (https://mariadb.com/kb/en/auto_increment/)

Generated at Thu Feb 08 09:37:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.