MySQL Bugs: #19755: meaningless error message

Bug #19755	meaningless error message
Submitted:	12 May 2006 7:49	Modified:	12 May 2006 7:57
Reporter:	Nick Turner	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.9	OS:	Windows (Windows XP Home)
Assigned to:		CPU Architecture:	Any

Description:
I am having difficulty understanding this error message (1482):
"A PRIMARY KEY need [sic] to include all fields in the partition function". Could you please explain what this means: does it mean that a primary key must be included - if so, then how?

Thanks

NICK TURNER

How to repeat:
Use the show create (best in a .sql script file):

CREATE TABLE enquiries_test (
  `Enquiry ID` int(11) NOT NULL AUTO_INCREMENT,
  `Master Enquiry ID` int(11) DEFAULT NULL,
  `enquiry_date` date NOT NULL DEFAULT '0000-00-00',
  `Sale Date` date DEFAULT NULL,
  `Sale Status` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `Date Last Modified` date DEFAULT NULL,
  `Enquiry Source` int(11) DEFAULT NULL,
  `Customer ID` int(11) DEFAULT NULL,
  `Sales Staff ID` int(11) NOT NULL DEFAULT '0',
  `Vehicle Price ID` int(11) DEFAULT NULL,
  `GovtContract` varchar(50) NOT NULL DEFAULT '',
  `Negotiated Vehicle Price` decimal(21,2) DEFAULT '0.00',
  `Current_Orig` tinyint(1) DEFAULT '0',
  `Orig_LP_Ex_GST` decimal(21,2) DEFAULT '0.00',
  `List Price Incl GST` decimal(21,2) DEFAULT '0.00',
  `Delivery Fee Paid` decimal(21,2) DEFAULT '0.00',
  `Est_Delivery_Date` date DEFAULT NULL,
  `Stamp Duty Paid` decimal(21,2) DEFAULT '0.00',
  `Vehicle Colour` int(11) DEFAULT NULL,
  `Vehicle Trim` int(11) DEFAULT NULL,
  `Rego User Type` int(11) unsigned DEFAULT '1',
  `Registration Fee Paid` decimal(21,2) DEFAULT '0.00',
  `Rego Time Period` int(11) unsigned DEFAULT '4',
  `Rego District` int(11) unsigned DEFAULT '1',
  `Rego State` char(3) DEFAULT 'SA',
  `CTP_Premium_Paid` decimal(21,2) DEFAULT '0.00',
  `CTP_ITC_Entitled` tinyint(1) DEFAULT '0',
  `CTP_Type` int(11) unsigned NOT NULL DEFAULT '1',
  `Appraisals` tinyint(3) unsigned DEFAULT '0',
  `Deposit Paid` decimal(21,2) DEFAULT '0.00',
  `Receipt Number` varchar(50) DEFAULT NULL,
  `Balance Financed By` varchar(50) DEFAULT NULL,
  `Customers Order Number` varchar(50) DEFAULT NULL,
  `Demo Date` date DEFAULT '0000-00-00',
  `No Sale reason` int(11) unsigned DEFAULT NULL,
  `Fleet` tinyint(1) DEFAULT '0',
  `Fleet Discount` decimal(21,2) DEFAULT '0.00',
  `IgSD` tinyint(1) DEFAULT '0',
  `IgReg` tinyint(1) DEFAULT '0',
  `IgDelFee` tinyint(1) DEFAULT '0',
  `GST Rate` float DEFAULT '0',
  `Enquiry Complete` tinyint(1) NOT NULL DEFAULT '0',
  `Discount Type` int(11) unsigned DEFAULT '0',
  `Status` varchar(10) DEFAULT 'NNNNNNNNNN',
  `Next Contact Date` date DEFAULT NULL,
  `Notes` text,
  `Delivery Date` date DEFAULT NULL,
  `Completely Invoiced` tinyint(1) DEFAULT '0',
  `Use Accessory Advertised Price` tinyint(1) DEFAULT '0',
  `Use Vehicle Advertised Price` tinyint(1) DEFAULT '0',
  `Sales Rank` int(4) unsigned NOT NULL DEFAULT '0',
  `Letter Sent` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `Addendum Notes` text,
  `Amendment Date` date DEFAULT NULL,
  `Common Expiry Date` date DEFAULT NULL,
  `Post_Sale_Letter_Sent` tinyint(3) unsigned DEFAULT NULL,
  `GM_Onum` int(11) unsigned DEFAULT '0',
  `Rego Category` int(11) unsigned DEFAULT '0',
  `Additional_Plate_Type` varchar(100) DEFAULT NULL,
  `Additional_Plate_Cost` decimal(21,2) NOT NULL DEFAULT '0.00',
  `Type` char(1) DEFAULT NULL,
  `SmartPak` varchar(10) DEFAULT NULL,
  `TStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Enquiry ID`),
  KEY `Customer I ID` (`Customer ID`),
  KEY `Master Enquiry ID` (`Master Enquiry ID`),
  KEY `SalesStaff ID` (`Sales Staff ID`),
  KEY `Vehicle Price ID` (`Vehicle Price ID`),
  KEY `Source` (`Enquiry Source`),
  KEY `Colour` (`Vehicle Colour`),
  KEY `Trim` (`Vehicle Trim`),
  KEY `Rego User` (`Rego User Type`),
  KEY `Rego Time` (`Rego Time Period`),
  KEY `Rego District` (`Rego District`),
  KEY `CTP Type` (`CTP_Type`),
  KEY `Discount Type` (`Discount Type`),
  KEY `NS Reason` (`No Sale reason`),
  KEY `GM Onum` (`GM_Onum`),
  KEY `Rego Category` (`Rego Category`),
  KEY `enq_date` (`enquiry_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

PARTITION BY RANGE (year(enquiry_date)) (
    PARTITION p0 VALUES LESS THAN (2000),
    PARTITION p1 VALUES LESS THAN (2001),
    PARTITION p2 VALUES LESS THAN (2002),
    PARTITION p3 VALUES LESS THAN (2003),
    PARTITION p4 VALUES LESS THAN (2004),
    PARTITION p5 VALUES LESS THAN (2005),
    PARTITION p6 VALUES LESS THAN (2006),
    PARTITION p7 VALUES LESS THAN MAXVALUE
);

Suggested fix:
don't know

(PS you can tell this table needs 'vertical partitioning'!!

We're sorry, but the bug system is not the appropriate forum for 
asking help on using MySQL products. Your problem is not the result 
of a bug.

Support on using our products is available both free in our forums
at http://forums.mysql.com and for a reasonable fee direct from our
skilled support engineers at http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

Please, read the manual (http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html):

"If a table that is to be partitioned has a primary key, then any columns used in the partitioning expression must be part of the primary key."

This is what your error message about.