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

ASC/DESC primary and unique keys cause index inconsistency between InnoDB and server

Details

    Description

      --source include/have_innodb.inc
      		 
       
      		 
      CREATE TABLE t (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
      		 
      ALTER TABLE t ADD PRIMARY KEY (id), ALGORITHM=INPLACE; # Also NOCOPY, DEFAULT/none
      		 
      SHOW CREATE TABLE t;
      		 
       
      		 
      # Cleanup
      		 
      DROP TABLE t;

      preview-10.8-MDEV-13756-desc-indexes 43444ff5d

      		 
      2022-01-06 16:50:24 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB.
      		 
      2022-01-06 16:50:24 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t

      ALTER is important, direct CREATE with the resulting table definition doesn't cause the error.
      ALGORITHM has been added for clarity, absence of any algorithm clause leads to the same result.
      Also reproducible with reverse ASC/DESC attributes (ASC unique key and DESC PK).
      Not reproducible when both are ASC or both are DESC.
      Not reproducible with ALGORITHM=COPY.

      Attachments

        Issue Links

          is caused by

          Task - A task that needs to be done. MDEV-13756 Implement descending index: KEY (a DESC, b ASC)

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27374 InnoDB table becomes corrupt after renaming DESC-indexed column

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            elenst Elena Stepanova created issue -
            elenst Elena Stepanova made changes -
            Field Original Value New Value
            elenst Elena Stepanova made changes -
            Description {code:sql}
            --source include/have_innodb.inc

            CREATE TABLE t (id INT AUTO_INCREMENT, UNIQUE(id DESC)) ENGINE=InnoDB;
            ALTER TABLE t ADD PRIMARY KEY (id), ALGORITHM=INPLACE; # Also NOCOPY, DEFAULT/none
            SHOW CREATE TABLE t;

            # Cleanup
            DROP TABLE t;
            {code}

            {noformat:title=preview-10.8-MDEV-13756-desc-indexes 43444ff5d}
            2022-01-06 16:50:24 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB.
            2022-01-06 16:50:24 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t
            {noformat}

            ALTER is important, direct CREATE with the resulting table definition doesn't cause the error.
            ALGORITHM has been added for clarity, absence of any algorithm clause leads to the same result.
            Also reproducible with reverse ASC/DESC attributes (ASC unique key and DESC PK).
            Not reproducible when both are ASC or both are DESC.
            Not reproducible with ALGORITHM=COPY.
            Not reproducible without AUTO_INCREMENT.
            		{code:sql}
            --source include/have_innodb.inc

            CREATE TABLE t (id INT NOT NULL, UNIQUE(id DESC)) ENGINE=InnoDB;
            ALTER TABLE t ADD PRIMARY KEY (id), ALGORITHM=INPLACE; # Also NOCOPY, DEFAULT/none
            SHOW CREATE TABLE t;

            # Cleanup
            DROP TABLE t;
            {code}

            {noformat:title=preview-10.8-MDEV-13756-desc-indexes 43444ff5d}
            2022-01-06 16:50:24 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB.
            2022-01-06 16:50:24 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t
            {noformat}

            ALTER is important, direct CREATE with the resulting table definition doesn't cause the error.
            ALGORITHM has been added for clarity, absence of any algorithm clause leads to the same result.
            Also reproducible with reverse ASC/DESC attributes (ASC unique key and DESC PK).
            Not reproducible when both are ASC or both are DESC.
            Not reproducible with ALGORITHM=COPY.
            marko Marko Mäkelä made changes -
            marko Marko Mäkelä made changes -
            Assignee Marko Mäkelä [ marko ] Thirunarayanan Balathandayuthapani [ thiru ]
            thiru Thirunarayanan Balathandayuthapani made changes -
            Fix Version/s 10.8.1 [ 26815 ]
            Fix Version/s 10.8 [ 26121 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]

            People

              thiru Thirunarayanan Balathandayuthapani
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.