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 added a comment - - edited

            The error isn't bogus, here is a variation of the test case which additionally produces a wrong result:

            --source include/have_innodb.inc
            		 
             
            		 
            CREATE OR REPLACE TABLE t (
            		 
              id bigint NOT NULL,
            		 
              c varchar(8) NOT NULL,
            		 
              UNIQUE KEY id (id)
            		 
            ) ENGINE=InnoDB;
            		 
             
            		 
            ALTER TABLE t ADD PRIMARY KEY (id DESC);
            		 
             
            		 
            INSERT INTO t VALUES (1,''),(2,'foo'),(3,''),(4,'bar');
            		 
             
            		 
            SELECT * FROM t WHERE id > 2 OR c IS NULL;
            		 
             
            		 
            # Cleanup
            		 
            DROP TABLE t;

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

            		 
            SELECT * FROM t WHERE id > 2 OR c IS NULL;
            		 
            id	c
            		 
            DROP TABLE t;
            		 
            bug.t2 'innodb'                          [ fail ]  Found warnings/errors in server log file!
            		 
                    Test ended at 2022-01-06 21:35:15
            		 
            line
            		 
            2022-01-06 21:35:15 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB.
            		 
            2022-01-06 21:35:15 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t

            elenst Elena Stepanova added a comment - - edited The error isn't bogus, here is a variation of the test case which additionally produces a wrong result: --source include/have_innodb.inc   CREATE OR REPLACE TABLE t ( id bigint NOT NULL , c varchar (8) NOT NULL , UNIQUE KEY id (id) ) ENGINE=InnoDB;   ALTER TABLE t ADD PRIMARY KEY (id DESC );   INSERT INTO t VALUES (1, '' ),(2, 'foo' ),(3, '' ),(4, 'bar' );   SELECT * FROM t WHERE id > 2 OR c IS NULL ;   # Cleanup DROP TABLE t; preview-10.8-MDEV-13756-desc-indexes 43444ff5d SELECT * FROM t WHERE id > 2 OR c IS NULL ; id c DROP TABLE t; bug.t2 'innodb' [ fail ] Found warnings/errors in server log file! Test ended at 2022-01-06 21:35:15 line 2022-01-06 21:35:15 4 [ERROR] Found index PRIMARY whose column info does not match that of MariaDB. 2022-01-06 21:35:15 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t
            marko Marko Mäkelä added a comment -

            thiru, since you already fixed the similar issue MDEV-27374, could you please fix this one as well? I suspect that something in prepare_inplace_alter_table_dict() or its callees must be changed.

            marko Marko Mäkelä added a comment - thiru , since you already fixed the similar issue MDEV-27374 , could you please fix this one as well? I suspect that something in prepare_inplace_alter_table_dict() or its callees must be changed.

            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.