partition table range
ERROR 1503 (HY000) at line 35: A PRIMARY KEY must include all columns in the table’s partitioning function
プライマリーキーにpartitionキーが入っている必要がある。
CREATE TABLE IF NOT EXISTS `payment` (
`user_id` varchar(32) NOT NULL,
`item_id` int NOT NULL,
/* 略 */
`created_at` TIMESTAMP DEFAULT 0,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`user_id`,`aaa_code`)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(
PARTITION p20130220 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130220′)),
PARTITION p20130320 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130320′)),
PARTITION p20130420 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130420′)),
PARTITION p20130520 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130520′)),
PARTITION pover VALUES LESS THAN (MAXVALUE));
`user_id` varchar(32) NOT NULL,
`item_id` int NOT NULL,
/* 略 */
`created_at` TIMESTAMP DEFAULT 0,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(`user_id`,`aaa_code`)
)
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(
PARTITION p20130220 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130220′)),
PARTITION p20130320 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130320′)),
PARTITION p20130420 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130420′)),
PARTITION p20130520 VALUES LESS THAN (UNIX_TIMESTAMP(‘20130520′)),
PARTITION pover VALUES LESS THAN (MAXVALUE));
primary keyにupdated_atを入れる必要がある。
PRIMARY KEY(`user_id`,`aaa_code`,`created_at`),
UNIQUE KEY(`user_id`,`aaa_code`),
UNIQUE KEY(`user_id`,`aaa_code`),
一意設定が必要なので別途unique keyを設定。
ERROR 1503 (HY000) at line 35: A UNIQUE INDEX must include all columns in the table’s partitioning function
ダメだったので。
PRIMARY KEY(`user_id`,`aaa_code`,`created_at`),
手動で重複確認します。
＊ show create tableするとこんな感じ
| payment | CREATE TABLE `payment` (
`user_id` varchar(32) NOT NULL,
/* 略 */
`created_at` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`,`acode`,`created_at`),
KEY `pcode` (`pcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(PARTITION p20130220 VALUES LESS THAN (1361286000) ENGINE = InnoDB,
PARTITION p20130320 VALUES LESS THAN (1363705200) ENGINE = InnoDB,
PARTITION p20130420 VALUES LESS THAN (1366383600) ENGINE = InnoDB,
PARTITION p20130520 VALUES LESS THAN (1368975600) ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
`user_id` varchar(32) NOT NULL,
/* 略 */
`created_at` timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00′,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`user_id`,`acode`,`created_at`),
KEY `pcode` (`pcode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(created_at))
(PARTITION p20130220 VALUES LESS THAN (1361286000) ENGINE = InnoDB,
PARTITION p20130320 VALUES LESS THAN (1363705200) ENGINE = InnoDB,
PARTITION p20130420 VALUES LESS THAN (1366383600) ENGINE = InnoDB,
PARTITION p20130520 VALUES LESS THAN (1368975600) ENGINE = InnoDB,
PARTITION pover VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |
dropやaddして試す。
参考
http://ftp.nchu.edu.tw/MySQL/tech-resources/articles/mysql_5.1_partitions.html
