mysql partition table use to_days bug
to_days分区表 bug
CREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT NULL, `month` int(2) NOT NULL, `day` int(2) NOT NULL, `startTime` datetime NOT NULL, `endTime` datetime NOT NULL, `version` varchar(12) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT '', `sid` varchar(12) NOT NULL, `valid` int(8) NOT NULL, `error` int(8) NOT NULL, `total` int(8) NOT NULL, PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`), KEY `aaaaaaaaaa_index_startTime` (`startTime`), KEY `aaaaaaaaaa_index_endTime` (`endTime`), KEY `aaaaaaaaaa_muti_index` (`year`,`month`,`source`), KEY `aaaaaaaaaa_index_source` (`source`), KEY `month_index` (`month`), KEY `year_index` (`year`)) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(startTime))(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */ 执行下面的sql，mysql 会crashselect sid as sid,source as source,sum(valid) as valid,sum(error) as error from aaaaaaaaaa where startTime>="2016-07-08 10:00:00" 通过下面的方法可以fixalter table aaaaaaaaaa add PARTITION (partition p_max values less than(maxvalue));
另：
CREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT NULL, `month` int(2) NOT NULL, `day` int(2) NOT NULL, `startTime` datetime NOT NULL, `endTime` datetime NOT NULL, `version` varchar(12) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT '', `sid` varchar(12) NOT NULL, `valid` int(8) NOT NULL, `error` int(8) NOT NULL, `total` int(8) NOT NULL, PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(startTime))(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */这样不会出现上面的问题但如果把starttime列加上索引 ，就会有这个问题CREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT NULL, `month` int(2) NOT NULL, `day` int(2) NOT NULL, `startTime` datetime NOT NULL, `endTime` datetime NOT NULL, `version` varchar(12) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT '', `sid` varchar(12) NOT NULL, `valid` int(8) NOT NULL, `error` int(8) NOT NULL, `total` int(8) NOT NULL, PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`), KEY `aaaaaaaaaa_index_startTime` (`startTime`)) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(startTime))(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */MOS没有找到相关的bug5.1 5.6 中都没有这个问题，5.5.24中有这个问题转载请注明源出处QQ 273002188 欢迎一起学习QQ 群 236941212oracle,mysql,PG 相互交流
CREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT NULL, `month` int(2) NOT NULL, `day` int(2) NOT NULL, `startTime` datetime NOT NULL, `endTime` datetime NOT NULL, `version` varchar(12) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT '', `sid` varchar(12) NOT NULL, `valid` int(8) NOT NULL, `error` int(8) NOT NULL, `total` int(8) NOT NULL, PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`), KEY `aaaaaaaaaa_index_startTime` (`startTime`), KEY `aaaaaaaaaa_index_endTime` (`endTime`), KEY `aaaaaaaaaa_muti_index` (`year`,`month`,`source`), KEY `aaaaaaaaaa_index_source` (`source`), KEY `month_index` (`month`), KEY `year_index` (`year`)) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(startTime))(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */ 执行下面的sql，mysql 会crashselect sid as sid,source as source,sum(valid) as valid,sum(error) as error from aaaaaaaaaa where startTime>="2016-07-08 10:00:00" 通过下面的方法可以fixalter table aaaaaaaaaa add PARTITION (partition p_max values less than(maxvalue));
另：
CREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT NULL, `month` int(2) NOT NULL, `day` int(2) NOT NULL, `startTime` datetime NOT NULL, `endTime` datetime NOT NULL, `version` varchar(12) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT '', `sid` varchar(12) NOT NULL, `valid` int(8) NOT NULL, `error` int(8) NOT NULL, `total` int(8) NOT NULL, PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`) ) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(startTime))(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */这样不会出现上面的问题但如果把starttime列加上索引 ，就会有这个问题CREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT NULL, `month` int(2) NOT NULL, `day` int(2) NOT NULL, `startTime` datetime NOT NULL, `endTime` datetime NOT NULL, `version` varchar(12) NOT NULL DEFAULT '', `source` varchar(12) NOT NULL DEFAULT '', `sid` varchar(12) NOT NULL, `valid` int(8) NOT NULL, `error` int(8) NOT NULL, `total` int(8) NOT NULL, PRIMARY KEY (`id`,`startTime`,`version`,`source`,`sid`), KEY `aaaaaaaaaa_index_startTime` (`startTime`)) ENGINE=InnoDB AUTO_INCREMENT=1267666446 DEFAULT CHARSET=utf8/*!50100 PARTITION BY RANGE (to_days(startTime))(PARTITION p20160405 VALUES LESS THAN (736425) ENGINE = InnoDB,PARTITION p20160620 VALUES LESS THAN (736501) ENGINE = InnoDB,PARTITION p20160706 VALUES LESS THAN (736517) ENGINE = InnoDB) */MOS没有找到相关的bug5.1 5.6 中都没有这个问题，5.5.24中有这个问题转载请注明源出处QQ 273002188 欢迎一起学习QQ 群 236941212oracle,mysql,PG 相互交流
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
