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
MySQL Dev Zone
MySQL Server Documentation
Oracle
Edited 2 time(s). Last edit at 06/14/2007 03:33PM by Jon Stephens.