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

InnoDB: Table ... contains <n> indexes inside InnoDB, which is different from the number of indexes <n> defined in the MariaDB

    XMLWordPrintable

    Details

      Description

      10.4 commit d87b725eebbddb6d319ee99e51924a62635185a1
      10.5 commit 42e825dd0a8d25c1d6fa93f5a07115ceed3ee0ff
      builds with debug and without debug.
       
      Snip of the protocol of my test run on 10.4
      CREATE TABLE t1 ( col_int INTEGER, col_string CHAR(20), col_varchar VARCHAR(500) ) ENGINE = InnoDB ROW_FORMAT = Dynamic ;
      ALTER TABLE t1 ADD KEY idx3 ( col_varchar(9) ), ADD KEY idX2 ( col_string(9) ) ;
      ALTER TABLE t1 DROP KEY idx3, ADD KEY iDx2 ( col_varchar(9) ) ;
      ERROR 42000: Duplicate key name 'iDx2'          <=========    There was never some index 'iDx2'  (uppercase D) created before. But maybe its just some case insensitive comparison of the names.
      ALTER TABLE t1 DROP KEY iDx3, ADD KEY Idx3 ( col_varchar(9) ) ;
      Warnings:
      Warning 1082    InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
      CHECK TABLE t1 EXTENDED ;
      Table   Op      Msg_type        Msg_text
      test.t1 check   status  OK
      SHOW CREATE TABLE t1;
      Table   Create Table
      t1      CREATE TABLE `t1` (
        `col_int` int(11) DEFAULT NULL,
        `col_string` char(20) DEFAULT NULL,
        `col_varchar` varchar(500) DEFAULT NULL,
        KEY `idX2` (`col_string`(9)),                            
        KEY `Idx3` (`col_varchar`(9))                          <=====  The first character of the indexname is here upper case.
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      SELECT * FROM information_schema . INNODB_SYS_INDEXES WHERE table_id =
      (SELECT table_id FROM information_schema . INNODB_SYS_TABLES WHERE Name = 'test/t1');
      INDEX_ID        NAME    TABLE_ID        TYPE    N_FIELDS        PAGE_NO SPACE   MERGE_THRESHOLD
      25      GEN_CLUST_INDEX 20      1       0       3       5       50
      26      idx3    20      0       1       4       5       50          <=====  The first character of the indexname is here lower case.
      28      idX2    20      0       1       6       5       50
      DROP TABLE t1;
      main.ml_weg6 'innodb'                    [ fail ]  Found warnings/errors in server log file!
              Test ended at 2020-02-05 13:25:40
      line
      2020-02-05 13:25:39 9 [ERROR] Cannot find index Idx3 in InnoDB index dictionary.
      2020-02-05 13:25:39 9 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t1
      2020-02-05 13:25:39 9 [ERROR] InnoDB could not find key no 1 with name Idx3 from dict cache for table test/t1
      2020-02-05 13:25:39 9 [ERROR] InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
       
      Snip of the protocol of my test run on 10.5 
      CREATE TABLE t1 ( col_int INTEGER, col_string CHAR(20), col_varchar VARCHAR(500) ) ENGINE = InnoDB ROW_FORMAT = Dynamic ;
      ALTER TABLE t1 ADD KEY idx3 ( col_varchar(9) ), ADD KEY idX2 ( col_string(9) ) ;
      ALTER TABLE t1 DROP KEY idx3, ADD KEY iDx2 ( col_varchar(9) ) ;
      ERROR 42000: Duplicate key name 'iDx2'
      ALTER TABLE t1 DROP KEY iDx3, ADD KEY Idx3 ( col_varchar(9) ) ;
      CHECK TABLE t1 EXTENDED ;
      Table   Op      Msg_type        Msg_text
      test.t1 check   Warning InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB 
      test.t1 check   status  OK
      SHOW CREATE TABLE t1;
      Table   Create Table
      t1      CREATE TABLE `t1` (
        `col_int` int(11) DEFAULT NULL,
        `col_string` char(20) DEFAULT NULL,
        `col_varchar` varchar(500) DEFAULT NULL,
        KEY `idX2` (`col_string`(9)),
        KEY `Idx3` (`col_varchar`(9))                      <=====  The first character of the indexname is here upper case.
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      SELECT * FROM information_schema . INNODB_SYS_INDEXES WHERE table_id =
      (SELECT table_id FROM information_schema . INNODB_SYS_TABLES WHERE Name = 'test/t1');
      INDEX_ID        NAME    TABLE_ID        TYPE    N_FIELDS        PAGE_NO SPACE   MERGE_THRESHOLD
      25      GEN_CLUST_INDEX 20      1       0       3       5       50
      26      idx3    20      0       1       4       5       50     <=====  The first character of the indexname is here lower case.
      28      idX2    20      0       1       6       5       50
      CHECK TABLE t1 EXTENDED ;
      Table   Op      Msg_type        Msg_text
      test.t1 check   status  OK
      DROP TABLE t1;
      main.ml_weg6a 'innodb'                   [ fail ]  Found warnings/errors in server log file!
              Test ended at 2020-02-05 13:32:03
      line
      2020-02-05 13:32:03 4 [ERROR] Cannot find index Idx3 in InnoDB index dictionary.
      2020-02-05 13:32:03 4 [ERROR] InnoDB indexes are inconsistent with what defined in .frm for table ./test/t1
      2020-02-05 13:32:03 4 [ERROR] InnoDB could not find key no 1 with name Idx3 from dict cache for table test/t1
      2020-02-05 13:32:03 4 [ERROR] InnoDB: Table test/t1 contains 2 indexes inside InnoDB, which is different from the number of indexes 2 defined in the MariaDB
       
      Whereas the results on 10.4 and 10.5 look quite similar there is some interesting difference
      10.4 ALTER TABLE t1 DROP KEY iDx3, ADD KEY Idx3 ( col_varchar(9) ) ;      harvests the warning
      10.5 The CHECK TABLE t1 EXTENDED after that ALTER harvests the warning.
       
      Other strange effect (observed in 10.5)
      In case I replace the
               ALTER TABLE t1 ADD KEY idx3 ( col_varchar(9) ), ADD KEY idX2 ( col_string(9) ) ;
      by
              ALTER TABLE t1 ADD KEY idx3 ( col_varchar(9) ), ADD KEY idX2 ( col_string ) ;
      than the warnings disappears.
      

        Attachments

          Activity

            People

            Assignee:
            kevg Eugene Kosov
            Reporter:
            mleich Matthias Leich
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: