Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8038

MariaDB has a bug when add a index on partition table

    XMLWordPrintable

Details

    Description

      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

      Attachments

        Activity

          People

            Unassigned Unassigned
            louis liu liuyang
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.