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

    XMLWordPrintable

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

          Activity

            People

              thiru Thirunarayanan Balathandayuthapani
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 4h 20m
                  4h 20m

                  Git Integration

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