MariaDB has a bug when add a index on partition table

Description

table define:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 CREATE TABLE `track_raw_wap211_log` ( `table_id` varchar(100) DEFAULT NULL, `page_id` varchar(100) DEFAULT NULL, `banner_id` varchar(100) DEFAULT NULL, `button_id` varchar(100) DEFAULT NULL, `test_id` varchar(100) DEFAULT NULL, `classfication` varchar(100) DEFAULT NULL, `request_refer` varchar(100) DEFAULT NULL, `request_url` text, `title` varchar(100) DEFAULT NULL, `user_id` varchar(100) DEFAULT NULL, `language` varchar(100) DEFAULT NULL, `event` varchar(100) DEFAULT NULL, `event_desc` varchar(100) DEFAULT NULL, `event_type` varchar(100) DEFAULT NULL, `log_version` varchar(100) DEFAULT NULL, `project` varchar(100) DEFAULT NULL, `log_time` varchar(100) DEFAULT NULL, `unicookie` varchar(100) DEFAULT NULL, `session_id` varchar(100) DEFAULT NULL, `session_duration` varchar(100) DEFAULT NULL, `resolution` varchar(100) DEFAULT NULL, `channel` varchar(100) DEFAULT NULL, `refer` text, `user_agent` text, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `httplogtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `httpforwardip` varchar(100) DEFAULT NULL, `country` varchar(100) DEFAULT NULL, `province` varchar(100) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `area` varchar(100) DEFAULT NULL, `mapweidu` varchar(100) DEFAULT NULL, `mapjingdu` varchar(100) DEFAULT NULL, `sync_mysqlid` bigint(20) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`sync_mysqlid`,`create_time`), KEY `ix_create_time` (`create_time`), KEY `ix_httplogtime` (`httplogtime`), KEY `ix_httplogtime_userid` (`httplogtime`,`user_id`) ) ENGINE=InnoDB AUTO_INCREMENT=23217118 DEFAULT CHARSET=utf8 /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(`create_time`)) (PARTITION p20150318 VALUES LESS THAN (1426694400) ENGINE = InnoDB, PARTITION p20150319 VALUES LESS THAN (1426780800) ENGINE = InnoDB, PARTITION p20150320 VALUES LESS THAN (1426867200) ENGINE = InnoDB, PARTITION p20150321 VALUES LESS THAN (1426953600) ENGINE = InnoDB, PARTITION p20150322 VALUES LESS THAN (1427040000) ENGINE = InnoDB, PARTITION p20150323 VALUES LESS THAN (1427126400) ENGINE = InnoDB, PARTITION p20150324 VALUES LESS THAN (1427212800) ENGINE = InnoDB, PARTITION p20150325 VALUES LESS THAN (1427299200) ENGINE = InnoDB, PARTITION p20150326 VALUES LESS THAN (1427385600) ENGINE = InnoDB, PARTITION p20150327 VALUES LESS THAN (1427472000) ENGINE = InnoDB, PARTITION p20150328 VALUES LESS THAN (1427558400) ENGINE = InnoDB, PARTITION p20150329 VALUES LESS THAN (1427644800) ENGINE = InnoDB, PARTITION p20150330 VALUES LESS THAN (1427731200) ENGINE = InnoDB, PARTITION p20150331 VALUES LESS THAN (1427817600) ENGINE = InnoDB, PARTITION p20150401 VALUES LESS THAN (1427904000) ENGINE = InnoDB, PARTITION p20150402 VALUES LESS THAN (1427990400) ENGINE = InnoDB, PARTITION p20150403 VALUES LESS THAN (1428076800) ENGINE = InnoDB, PARTITION p20150404 VALUES LESS THAN (1428163200) ENGINE = InnoDB, PARTITION p20150405 VALUES LESS THAN (1428249600) ENGINE = InnoDB, PARTITION p20150406 VALUES LESS THAN (1428336000) ENGINE = InnoDB, PARTITION p20150407 VALUES LESS THAN (1428422400) ENGINE = InnoDB, PARTITION p20150408 VALUES LESS THAN (1428508800) ENGINE = InnoDB, PARTITION p20150409 VALUES LESS THAN (1428595200) ENGINE = InnoDB, PARTITION p20150410 VALUES LESS THAN (1428681600) ENGINE = InnoDB, PARTITION p20150411 VALUES LESS THAN (1428768000) ENGINE = InnoDB, PARTITION p20150412 VALUES LESS THAN (1428854400) ENGINE = InnoDB, PARTITION p20150413 VALUES LESS THAN (1428940800) ENGINE = InnoDB, PARTITION p20150414 VALUES LESS THAN (1429027200) ENGINE = InnoDB, PARTITION p20150415 VALUES LESS THAN (1429113600) ENGINE = InnoDB, PARTITION p20150416 VALUES LESS THAN (1429200000) ENGINE = InnoDB, PARTITION p20150417 VALUES LESS THAN (1429286400) ENGINE = InnoDB, PARTITION p20150418 VALUES LESS THAN (1429372800) ENGINE = InnoDB, PARTITION p20150419 VALUES LESS THAN (1429459200) ENGINE = InnoDB, PARTITION p20150420 VALUES LESS THAN (1429545600) ENGINE = InnoDB, PARTITION p20150421 VALUES LESS THAN (1429632000) ENGINE = InnoDB, PARTITION p20150422 VALUES LESS THAN (1429718400) ENGINE = InnoDB, PARTITION p20150423 VALUES LESS THAN (1429804800) ENGINE = InnoDB, PARTITION p20150424 VALUES LESS THAN (1429891200) ENGINE = InnoDB, PARTITION p20150425 VALUES LESS THAN (1429977600) ENGINE = InnoDB, PARTITION p20150426 VALUES LESS THAN (1430064000) ENGINE = InnoDB, PARTITION p20150427 VALUES LESS THAN (1430150400) ENGINE = InnoDB, PARTITION p20150428 VALUES LESS THAN (1430236800) ENGINE = InnoDB, PARTITION p20150429 VALUES LESS THAN (1430323200) ENGINE = InnoDB, PARTITION p20150430 VALUES LESS THAN (1430409600) ENGINE = InnoDB, PARTITION p20150501 VALUES LESS THAN (1430496000) ENGINE = InnoDB, PARTITION p20150502 VALUES LESS THAN (1430582400) ENGINE = InnoDB, PARTITION p20150503 VALUES LESS THAN (1430668800) ENGINE = InnoDB, PARTITION p20150504 VALUES LESS THAN (1430755200) ENGINE = InnoDB, PARTITION p20150505 VALUES LESS THAN (1430841600) ENGINE = InnoDB, PARTITION p20150506 VALUES LESS THAN (1430928000) ENGINE = InnoDB, PARTITION p20150507 VALUES LESS THAN (1431014400) ENGINE = InnoDB, PARTITION p20150508 VALUES LESS THAN (1431100800) ENGINE = InnoDB, PARTITION p20150509 VALUES LESS THAN (1431187200) ENGINE = InnoDB, PARTITION p20150510 VALUES LESS THAN (1431273600) ENGINE = InnoDB, PARTITION p20150511 VALUES LESS THAN (1431360000) ENGINE = InnoDB) */

--------------------------------------------------------------------------------------------------

This table has more than 20 million records.

when add index on this table maria do a copy to tmp table – that cost a long time but still can't finish.

1 2 3 create index idx_tmp on track_raw_wap211_log (log_time); 21407 | root | localhost | jkgj_log | Query | 34 | copy to tmp table | create index idx_tmp on track_raw_wap211_log (log_time) | 0.000 |

drop index command also meets this problem

1 2 3 alter table track_raw_wap211_log drop index ix_httplogtime; 22488 | root | localhost | jkgj_log | Query | 33 | copy to tmp table | alter table track_raw_wap211_log drop index ix_httplogtime | 0.409 |

----------------------------------------------------------------------

Repeat these on 5.5.5-10.0.15-MariaDB-log MariaDB Server everything is OK

Environment

CentOS release 6.5 (Final)

Status

Assignee

Unassigned

Reporter

liuyang

Labels

External issue ID

None

External issue ID

None

Time tracking

12h

Components

Fix versions

Affects versions

10.0.10

Priority

Critical