Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-21842

auto_increment does not increment with compound primary key on partitioned table

    XMLWordPrintable

    Details

      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

          Activity

            People

            Assignee:
            holyfoot Alexey Botchkov
            Reporter:
            Hemera Martijn Smidt
            Votes:
            2 Vote for this issue
            Watchers:
            6 Start watching this issue

              Dates

              Created:
              Updated: