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.
|