MySQL Forums
Forum List  »  Partitioning

range columns including datetime column
Posted by: Rob Voyer
Date: August 15, 2012 10:01PM

I have a large reporting table that is difficult to manage (disk space problems) so I am trying to partition it using range columns including a datetime column. When I insert or select, it doesn't respect the partitions and particularly the datetime values. Here is my create table statement:

CREATE
TABLE sessions_grouped_partitioned
(
period SMALLINT NOT NULL,
START DATETIME NOT NULL,
group_by_type SMALLINT NOT NULL,
group_by_value_1 VARCHAR(30),
group_by_value_2 VARCHAR(30) NOT NULL,
group_by_value_3 VARCHAR(30) NOT NULL,
id bigint unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id, period, START),
UNIQUE INDEX groupByOneTwoThree (period, START, group_by_type, group_by_value_1, group_by_value_2, group_by_value_3),
INDEX groupByTwoOneThree (period, START, group_by_value_2, group_by_value_1, group_by_value_3)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE COLUMNS(period,start) (
PARTITION p123 VALUES LESS THAN (4, '2015-01-01 00:00:00'),
PARTITION p45_1205 VALUES LESS THAN (6,'2012-06-01 00:00:00'),
PARTITION p45_1206 VALUES LESS THAN (6,'2012-07-01 00:00:00'),
PARTITION p45_1207 VALUES LESS THAN (6,'2012-08-01 00:00:00'),
PARTITION p45_1208 VALUES LESS THAN (6,'2012-09-01 00:00:00'),
PARTITION p45_1209 VALUES LESS THAN (6,'2012-10-01 00:00:00'),
PARTITION p45_1210 VALUES LESS THAN (6,'2012-11-01 00:00:00'),
PARTITION p45_1211 VALUES LESS THAN (6,'2012-12-01 00:00:00'),
PARTITION p45_1212 VALUES LESS THAN (6,'2013-01-01 00:00:00'),
PARTITION p45_1301 VALUES LESS THAN (6,'2015-01-01 00:00:00')
);

I can get rows to go into only the first two partitions (p123 and p45_1205). I read someone's post elsewhere that they had a similar problem. They mentioned changing their my.cnf file but didn't specify exactly what they changed. Hoping there is an obvious solution! I just upgraded to 5.5.27 so I could do this easily without having to change my current table structure.

I have also tried using MAXVALUE above instead of far out values like 2015 but that causes an error in creating the table. Thanks

Options: ReplyQuote


Subject
Views
Written By
Posted
range columns including datetime column
2908
August 15, 2012 10:01PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.