Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
2

This is the structure of my table:

-- Table: t_keyword_conversion_ga
-- Create Table: 
CREATE TABLE `t_keyword_conversion_ga` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`own_domain_id` int(11) DEFAULT NULL,
`keyword_id` int(11) DEFAULT NULL,
`traffic_date` date DEFAULT NULL,
`targeturl_id` int(11) DEFAULT NULL,
`entrance` int(11) DEFAULT NULL,
`transactions` int(11) DEFAULT NULL,
`item_revenue` decimal(9,2) DEFAULT NULL,
`goal1completions` int(11) DEFAULT NULL,
`goal2completions` int(11) DEFAULT NULL,
`goal3completions` int(11) DEFAULT NULL,
`goal4completions` int(11) DEFAULT NULL,
`goal5completions` int(11) DEFAULT NULL,
`goal6completions` int(11) DEFAULT NULL,
`goal7completions` int(11) DEFAULT NULL,
`goal8completions` int(11) DEFAULT NULL,
`goal9completions` int(11) DEFAULT NULL,
`goal10completions` int(11) DEFAULT NULL,
`goal1Value` decimal(9,2) DEFAULT NULL,
`goal2Value` decimal(9,2) DEFAULT NULL,
`goal3Value` decimal(9,2) DEFAULT NULL,
`goal4Value` decimal(9,2) DEFAULT NULL,
`goal5Value` decimal(9,2) DEFAULT NULL,
`goal6Value` decimal(9,2) DEFAULT NULL,
`goal7Value` decimal(9,2) DEFAULT NULL,
`goal8Value` decimal(9,2) DEFAULT NULL,
`goal9Value` decimal(9,2) DEFAULT NULL,
`goal10Value` decimal(9,2) DEFAULT NULL,
`medium` varchar(255) DEFAULT NULL,
`source` varchar(255) DEFAULT NULL,
KEY `id` (`id`),
KEY `keyword_id` (`keyword_id`),
KEY `traffic_date` (`traffic_date`),
KEY `own_domain_id` (`own_domain_id`,`traffic_date`),
KEY `targeturl_id` (`targeturl_id`)
) ENGINE=InnoDB AUTO_INCREMENT=581732054 DEFAULT CHARSET=utf8
/*!50500 PARTITION BY RANGE COLUMNS(traffic_date)
(PARTITION p201101 VALUES LESS THAN ('2011-01-01') ENGINE = InnoDB,
PARTITION p201102 VALUES LESS THAN ('2011-02-01') ENGINE = InnoDB,
PARTITION p201103 VALUES LESS THAN ('2011-03-01') ENGINE = InnoDB,
PARTITION p201104 VALUES LESS THAN ('2011-04-01') ENGINE = InnoDB,
---->PARTITION p201105 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
PARTITION p201106 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB,
PARTITION p201107 VALUES LESS THAN ('2011-07-01') ENGINE = InnoDB,
PARTITION p201108 VALUES LESS THAN ('2011-08-01') ENGINE = InnoDB,
PARTITION p201109 VALUES LESS THAN ('2011-09-01') ENGINE = InnoDB,
PARTITION p201110 VALUES LESS THAN ('2011-10-01') ENGINE = InnoDB,
PARTITION p201111 VALUES LESS THAN ('2011-11-01') ENGINE = InnoDB,
PARTITION p201112 VALUES LESS THAN ('2011-12-01') ENGINE = InnoDB,
PARTITION p201201 VALUES LESS THAN ('2012-01-01') ENGINE = InnoDB,
PARTITION p201202 VALUES LESS THAN ('2012-02-01') ENGINE = InnoDB,
PARTITION p201203 VALUES LESS THAN ('2012-03-01') ENGINE = InnoDB,
PARTITION p201204 VALUES LESS THAN ('2012-04-01') ENGINE = InnoDB,
PARTITION p201205 VALUES LESS THAN ('2012-05-01') ENGINE = InnoDB,
PARTITION p201206 VALUES LESS THAN ('2012-06-01') ENGINE = InnoDB,
PARTITION p201207 VALUES LESS THAN ('2012-07-01') ENGINE = InnoDB,
PARTITION p201208 VALUES LESS THAN ('2012-08-01') ENGINE = InnoDB,
PARTITION p201209 VALUES LESS THAN ('2012-09-01') ENGINE = InnoDB,
PARTITION p201210 VALUES LESS THAN ('2012-10-01') ENGINE = InnoDB,
PARTITION p201211 VALUES LESS THAN ('2012-11-01') ENGINE = InnoDB,
PARTITION p201212 VALUES LESS THAN ('2012-12-01') ENGINE = InnoDB,
PARTITION p201301 VALUES LESS THAN ('2013-01-01') ENGINE = InnoDB,
PARTITION p201302 VALUES LESS THAN ('2013-02-01') ENGINE = InnoDB,
PARTITION p201303 VALUES LESS THAN ('2013-03-01') ENGINE = InnoDB,
PARTITION p201304 VALUES LESS THAN ('2013-04-01') ENGINE = InnoDB,
PARTITION p201305 VALUES LESS THAN ('2013-05-01') ENGINE = InnoDB,
PARTITION p201306 VALUES LESS THAN ('2013-06-01') ENGINE = InnoDB,
PARTITION pEOW VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */
1 row in set (0.02 sec)

I dropped the partition 'p201105'. Now I am trying to add it again with the below statement but it throws an error:

ALTER TABLE t_keyword_conversion_ga 
ADD PARTITION (PARTITION p201105 VALUES LESS THAN ('2011-05-01'));

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

How to add the same partition?

share|improve this question

1 Answer

up vote 2 down vote

The following seems to work in Mysql 5.5.

ALTER TABLE `t_keyword_conversion_ga` REORGANIZE PARTITION p201106  INTO (
    PARTITION p201105 VALUES LESS THAN ('2011-05-01') ENGINE = InnoDB,
    PARTITION p201106 VALUES LESS THAN ('2011-06-01') ENGINE = InnoDB
);

as you can check by sqlfiddle

share|improve this answer
You are correct on your disagreement. I overlooked REORGANIZED. So, +1 !!! – RolandoMySQLDBA Dec 3 '12 at 18:03
I am bookmarking this one in favorites. Thank You. – RolandoMySQLDBA Dec 3 '12 at 18:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.