5

I have a table structure like-

CREATE TABLE `cdr` (`id` bigint(20) NOT NULL AUTO_INCREMENT,
                    `dataPacketDownLink` bigint(20) DEFAULT NULL,
                    `dataPacketUpLink` bigint(20) DEFAULT NULL,
                    `dataPlanEndTime` datetime DEFAULT NULL,
                    `dataPlanStartTime` datetime DEFAULT NULL,
                    `dataVolumeDownLink` bigint(20) DEFAULT NULL,
                    `dataVolumeUpLink` bigint(20) DEFAULT NULL,  
                    `dataplan` varchar(255) DEFAULT NULL,  
                    `dataplanType` varchar(255) DEFAULT NULL,  
                    `createdOn` datetime DEFAULT NULL,  
                    `deviceName` varchar(500) DEFAULT NULL,  
                    `duration` int(11) NOT NULL,  
                    `effectiveDuration` int(11) NOT NULL,  
                    `hour` int(11) DEFAULT NULL,  
                    `eventDate` datetime DEFAULT NULL,  
                    `msisdn` bigint(20) DEFAULT NULL,  
                    `quarter` int(11) DEFAULT NULL,  
                    `validDays` int(11) DEFAULT NULL,  
                    `dataLeft` bigint(20) DEFAULT NULL,  
                    `completedOn` datetime DEFAULT NULL,   
                PRIMARY KEY (`id`),   
                KEY `msisdn_index` (`msisdn`),   
                KEY `eventdate_index` (`eventDate`)   
            ) ENGINE=MyISAM AUTO_INCREMENT=55925171 DEFAULT CHARSET=latin1

and when i am creating partition -

ALTER TABLE cdr PARTITION BY RANGE (TO_DAYS(eventdate))  (
    PARTITION p01 VALUES LESS THAN (TO_DAYS('2013-09-01')),  
    PARTITION p02 VALUES LESS THAN (TO_DAYS('2013-09-15')),  
    PARTITION p03 VALUES LESS THAN (TO_DAYS('2013-09-30')),   
    PARTITION p04 VALUES LESS THAN (MAXVALUE));

getting the 

error 1503: A primary key must include all columns in the table's partitioning function

i have read everywhere about this but not getting anything so please let me know how to partition this table. i have 20+ million records in it.

Thank you.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Aamir
  • 738
  • 2
  • 17
  • 41
  • possible duplicate of [A primary must include all columns in the table's partitioning location error?](http://stackoverflow.com/questions/11896067/a-primary-must-include-all-columns-in-the-tables-partitioning-location-error) – Barmar Nov 28 '13 at 07:29

2 Answers2

1

I have already solved this problem by adding eventdate with primary key.

Aamir
  • 738
  • 2
  • 17
  • 41
  • What does that even mean? Did you make `eventdate` your primary key? What if two events lie on the same date? Where is the SQL? – Chloe Sep 25 '19 at 22:36
-1

Possible solutions:

  1. change eventdate to eventDate on 'ALTER TABLE cdr PARTITION BY RANGE (TO_DAYS(eventdate)) '
  2. change eventDate to timestamp. (mysql can't do partition on datetime)
Daniel Armanto
  • 1
  • 1
    Uh, since when hasn't MySQL been able to partition on DATETIME? Per their documentation: "Partition the table by RANGE, and for the partitioning expression, employ a function operating on a DATE, TIME, or DATETIME column and returning an integer value" https://dev.mysql.com/doc/refman/5.5/en/partitioning-range.html – dave Feb 03 '16 at 03:30