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

MariaDB has a bug when add a index on partition table

table define:

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.

 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

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

Status

Assignee

Unassigned

Reporter

liuyang