MySQL Forums
Forum List  »  Partitioning

Re: Proactive partitioning by Week
Posted by: Jonathan Stephens
Date: June 14, 2007 03:29PM

My understanding is that any unique key (including the PK) must use any columns that are part of the partitioning key. Note the word *unique*. A simple KEY is not a unique key. Only a UNIQUE KEY or a PRIMARY KEY is a unique key.

You have the PK on the m_md5 column, but this column isn't part of the partitioning expression, which uses only the m_date column.

Here's what I get when I try your table definition:

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`),
-> 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
-> );
ERROR 1491 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function


Read the section of the Manual I linked to previously for additional explanation plus some examples.

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

MySQL Dev Zone
MySQL Server Documentation
Oracle



Edited 2 time(s). Last edit at 06/14/2007 03:33PM by Jon Stephens.

Options: ReplyQuote


Subject
Views
Written By
Posted
4765
June 08, 2007 04:14PM
2850
June 13, 2007 11:14AM
Re: Proactive partitioning by Week
2893
June 14, 2007 03:29PM
3021
July 02, 2007 01:46PM
2920
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.