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.