导航：首页 > 数据库 >

mysql partition table use to_days bug

发表于：2025-02-01 作者：千家信息网编辑

千家信息网最后更新 2025年02月01日，to_days分区表 bugCREATE TABLE `aaaaaaaaaa` ( `id` int(255) NOT NULL AUTO_INCREMENT, `year` int(4) NOT N

千家信息网最后更新 2025年02月01日mysql partition table use to_days bugto_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 会crash select sid as sid,source as source,sum(valid) as valid,sum(error) as error from aaaaaaaaaa where startTime>="2016-07-08 10:00:00" 通过下面的方法可以fix alter 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没有找到相关的bug 5.1 5.6 中都没有这个问题，5.5.24中有这个问题转载请注明源出处 QQ 273002188 欢迎一起学习 QQ 群 236941212 oracle,mysql,PG 相互交流

很赞哦！