MySQL Forums
Forum List  »  Partitioning

Re: Proactive partitioning by Week
Posted by: Jonathan Stephens
Date: July 03, 2007 01:21AM

The error message says: A PRIMARY KEY must include all columns in the table's partitioning function. Note the use of the word include: this does not mean that the PK may consist only of the column(s) used as the partitioning key.

You have two choices:

1. Remove the PK:

mysql> CREATE TABLE `m_msg` (
    ->  `m_md5` varchar(32) NOT NULL default '',
    ->  `m_from` varchar(128) NOT NULL default '',
    ->  `m_to` varchar(128) NOT NULL default '',
    ->  `m_cc` text NOT NULL,
    ->  `m_subject` text NOT NULL,
    ->  `m_attach` tinyint(1) NOT NULL default '0',
    ->  `m_image` tinyint(1) NOT NULL default '0',
    ->  `m_date` date NOT NULL default '0000-00-00',
    ->  `m_time` time NOT NULL default '00:00:00',
    ->  `m_archived` tinyint(1) unsigned NOT NULL default '0',
    ->  KEY `i_date` (`m_date`),
    ->  KEY `i_from` USING BTREE (`m_from`(25)),
    ->  KEY `i_to` USING BTREE (`m_to`(25)),
    ->  KEY `i_attach` USING BTREE (`m_attach`),
    ->  KEY `i_image` USING BTREE (`m_image`)
    ->  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2000000000 PACK_KEYS=0
    ->  PARTITION BY RANGE(TO_DAYS(m_date)) (
    ->  PARTITION p0 VALUES LESS THAN (733041),
    ->  PARTITION p1 VALUES LESS THAN (733048),
    ->  PARTITION p2 VALUES LESS THAN (733056),
    ->  PARTITION p3 VALUES LESS THAN MAXVALUE
    ->  );
Query OK, 0 rows affected (0.08 sec)

2. Make the column containing the date part of the PK:

mysql> CREATE TABLE `m_msg` (
    ->  `m_md5` varchar(32) NOT NULL default '',
    ->  `m_from` varchar(128) NOT NULL default '',
    ->  `m_to` varchar(128) NOT NULL default '',
    ->  `m_cc` text NOT NULL,
    ->  `m_subject` text NOT NULL,
    ->  `m_attach` tinyint(1) NOT NULL default '0',
    ->  `m_image` tinyint(1) NOT NULL default '0',
    ->  `m_date` date NOT NULL default '0000-00-00',
    ->  `m_time` time NOT NULL default '00:00:00',
    ->  `m_archived` tinyint(1) unsigned NOT NULL default '0',
    ->  PRIMARY KEY (`m_md5`, `m_date`),
    ->  KEY `i_date` (`m_date`),
    ->  KEY `i_from` USING BTREE (`m_from`(25)),
    ->  KEY `i_to` USING BTREE (`m_to`(25)),
    ->  KEY `i_attach` USING BTREE (`m_attach`),
    ->  KEY `i_image` USING BTREE (`m_image`)
    ->  ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=2000000000 PACK_KEYS=0
    ->  PARTITION BY RANGE(TO_DAYS(m_date)) (
    ->  PARTITION p0 VALUES LESS THAN (733041),
    ->  PARTITION p1 VALUES LESS THAN (733048),
    ->  PARTITION p2 VALUES LESS THAN (733056),
    ->  PARTITION p3 VALUES LESS THAN MAXVALUE
    ->  );
Query OK, 0 rows affected (0.09 sec)

You may need to enforce the uniqueness of m_md5 in your application for this to work, at least at this point in the development of MySQL partitioning.

Hope this helps.

Jon Stephens
MySQL Documentation Team @ Oracle
Orlando, Florida, USA

MySQL Dev Zone
MySQL Server Documentation
Oracle

Options: ReplyQuote


Subject
Views
Written By
Posted
4725
June 08, 2007 04:14PM
2836
June 13, 2007 11:14AM
3003
July 02, 2007 01:46PM
Re: Proactive partitioning by Week
4467
July 03, 2007 01:21AM
2908
July 03, 2007 08:43AM


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.