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

In Partiitioned table using HASH, AUTOINCREMENT value reverts to original CREATE TABLE value when INSERTing explicitly to empty partition immediately after ANALYZE TABLE.

    XMLWordPrintable

Details

    Description

      INSERTing explicitly to specific partition immediately after running ANALYZE TABLE in a MyISAM or Aria table partitioned by HASH with an AUTOINCREMENT column causes the AUTOINCREMENT value to revert to the original CREATE TABLE value, resulting in duplicate values.

      This happens when the table is MyISAM, has at least 2 partitions, and the inserts are done first to one partition, and immediately after running ANALYZE TABLE, INSERTs are done to another partition, and all inserts are done to specific partitions of a table partitioned by HASH.

      Any statement that touches table metadata, such as a SELECT from the table or a SHOW CREATE TABLE, makes it so the subsequent INSERT works correctly, but an INSERT into an empty partition, done immediately after an ANALYZE TABLE, always produces this error.

      Changing engines to either Aria or InnoDB has no effect in 10.2 servers (they still work correctly). However, in 10.3 and 10.4 servers using either Aria or InnoDB for the test results in a server crash on the first insert operation.

      ====Test Case====

      drop table if exists example_table ;
      CREATE TABLE example_table (
        key1_id bigint(20) NOT NULL,
        key2_id bigint(20) NOT NULL,
        independent_id int(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (key1_id,key2_id) USING BTREE,
        KEY independent_id (independent_id) USING BTREE
      ) ENGINE=MyISAM AUTO_INCREMENT=1312895095 DEFAULT CHARSET=latin1
       PARTITION BY HASH (key1_id)
      (PARTITION p0001 ENGINE = MyISAM,
       PARTITION p0002 ENGINE = MyISAM);
       
      insert ignore into example_table partition(p0001) (
        key1_id,
        key2_id
      )
      values
      (0,1),
      (0,2),
      (0,3),
      (0,4),
      (0,5),
      (0,6),
      (0,7),
      (0,8)
      ;
      insert ignore into example_table partition(p0002) (
        key1_id,
        key2_id
      )
      values
      (1,1),
      (1,2),
      (1,3),
      (1,4),
      (1,5)
      ;
       
       
      analyze table example_table;
       
      --show create table example_table\G;
      -- select 'a' from example_table limit 1;
       
      insert ignore into example_table partition(p0001) (
        key1_id,
        key2_id
      )
      values
      (2,1),
      (2,2),
      (2,3),
      (2,4),
      (2,5)
      ;
       
      select independent_id, count(*) from example_table
      group by independent_id;
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              juan.vera Juan
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.