We're updating the issue view to help you get more done. 

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

Time Tracking

12h

Components

Fix versions

Affects versions

10.0.10

Priority

Critical