MySQL5.5.28中Error: 1486导致TIMESTAMP不允许作为分区中的分区函数

saneknight

Error: 1486 SQLSTATE: HY000 (ER_WRONG_EXPR_IN_PARTITION_FUNC_ERROR [4028])
                Message: Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed       

导致下面的SQL报错：

CREATE TABLE `query_task` (
                  `queryid` BIGINT(20) NOT NULL AUTO_INCREMENT,
                  `querysql` TEXT COMMENT '查询对应的SQL，由后台回写',
                  `starttime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                  `endtime` DATETIME DEFAULT NULL,
                  `qstate` DECIMAL(3,0) DEFAULT NULL COMMENT '查询状态，0：未开始 1：查询中 2：成功 3：失败 4：超时 5：请求取消 6：已取消',
                  `processinfo` VARCHAR(256) DEFAULT NULL COMMENT '相关过程提示信息',
                  `cost` DECIMAL(6,0) DEFAULT '0' COMMENT '查询花费时间，单位：秒',
                  `matchnum` INT(11) DEFAULT NULL COMMENT '查询到的记录数',
                  `opeuser` VARCHAR(32) DEFAULT NULL COMMENT '查??用户，由请求者填写',
                  `opesip` VARCHAR(20) DEFAULT NULL,
                  `oncelimit` DECIMAL(8,0) DEFAULT NULL COMMENT '一次查询最大数量',
                  `twolimit` DECIMAL(8,0) DEFAULT NULL COMMENT '二次查询最大数量',
                  `errcode` VARCHAR(10) DEFAULT NULL,
                  `sqltimeouts` DECIMAL(6,0) DEFAULT '30',
                  `lastquerytime` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00',
                  PRIMARY KEY (`queryid`,`starttime`),
                  KEY `idx_query` (`queryid`,`qstate`)
                ) ENGINE=INNODB DEFAULT CHARSET=utf8
                /*!50100 PARTITION BY LIST (year(starttime)*10000+month(starttime)*100+day(starttime))
                (PARTITION p20120229 VALUES IN (20120229) ENGINE = InnoDB,
                 PARTITION p20121029 VALUES IN (20121029) ENGINE = InnoDB) */;

而上述SQL在5.1.34中是可以创建成功的，如何解决这个问题。

saneknight

这个SQL也报错，因为使用了happentime，而happentime是TIMESTAMP类型，是时区依赖的：


CREATE TABLE `sr_collinfo` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `statid` int(11) DEFAULT NULL,
  `tcount` int(11) DEFAULT NULL,
  `tsize` int(11) DEFAULT NULL,
  `kv1` varchar(12 DEFAULT NULL,
  `kv2` varchar(12 DEFAULT NULL,
  `kv3` varchar(12 DEFAULT NULL,
  `kv4` varchar(12 DEFAULT NULL,
  `kv5` varchar(12 DEFAULT NULL,
  `kv6` varchar(12 DEFAULT NULL,
  `kv7` varchar(12 DEFAULT NULL,
  `kv8` varchar(12 DEFAULT NULL,
  `kv9` varchar(12 DEFAULT NULL,
  `kv10` varchar(12 DEFAULT NULL,
  `kv11` varchar(128) DEFAULT NULL,
  `happentime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`,`happentime`),
  KEY `index_q1` (`statid`,`kv1`,`kv2`,`kv3`,`kv4`,`kv5`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='报表统计汇总信息'
/*!50100 PARTITION BY LIST (year(happentime)*10000+month(happentime)*100+day(happentime))
(PARTITION p20091112 VALUES IN (20091112) ENGINE = InnoDB,
PARTITION p20100817 VALUES IN (20100817) ENGINE = InnoDB,
PARTITION p20101026 VALUES IN (20101026) ENGINE = InnoDB,
PARTITION p20101126 VALUES IN (20101126) ENGINE = InnoDB,
PARTITION p20101213 VALUES IN (20101213) ENGINE = InnoDB,
PARTITION p20101223 VALUES IN (20101223) ENGINE = InnoDB,
PARTITION p20110503 VALUES IN (20110503) ENGINE = InnoDB,
PARTITION p20120203 VALUES IN (20120203) ENGINE = InnoDB,
PARTITION p20120229 VALUES IN (20120229) ENGINE = InnoDB,
PARTITION p20121029 VALUES IN (20121029) ENGINE = InnoDB) */

chinafenghao

@saneknight
  `starttime` DATETIME NOT NULL ,
将日期类型换了，然后通过程序来控制这个时间的更新。

RogerZhuo

回复 2# saneknight

这是由于MySQL partition的一个Bug.

从5.1.43版本开始修复了，所以之前的版本可以这样用，而5.5不能这样用。
详见：http://dev.mysql.com/doc/refman/5.1/en/news-5-1-43.html

1）可以像@ISADBA 的方法处理
2）也可以有link中提示的， 对字段加unix_timestamp化为时间戳处理

create table test2(id int , tim TIMESTAMP  NOT NULL DEFAULT now(),primary key(`id`,`tim`)) engine innodb partition by range( UNIX_TIMESTAMP(tim))( PARTITION p0 VALUES LESS THAN (631148400), PARTITION p2 VALUES LESS THAN (MAXVALUE) );