MySQL Bugs: #58910: #1503 - A UNIQUE INDEX must include all columns in the table's partitioning func

Bug #58910	#1503 - A UNIQUE INDEX must include all columns in the table's partitioning func
Submitted:	14 Dec 2010 4:45	Modified:	21 Dec 2010 8:42
Reporter:	harjeet singh	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	mysql 5.5.6	OS:	Linux (centos 5.5)
Assigned to:		CPU Architecture:	Any
Tags:	my partition

Description:
Dear Sir

When I have created the partition on new table(dump of existing table of my database with primary and unique keys only structure of table ) then this message is appear #1503 - A UNIQUE INDEX must include all columns in the table's partitioning func 
after this i have deleted the unique key then partition is created on table  after that I 
have inserted the data in that table and make the unique key then i was shocked 
 the table seems tobe empty and all inserted data is deleted so plz suggest to me sir 

How to repeat:
this can be repeated

Please, provide problematic CREATE TABLE statement.

CREATE TABLE  `emp_info` (
  `emp_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(64) NOT NULL,
  `token_no` varchar(8) NOT NULL,
  `email_id` varchar(128) DEFAULT NULL,
  `dob` date NOT NULL,
  `father_husband_name` varchar(64) NOT NULL,
  `mother_name` varchar(64) DEFAULT NULL,
  `sex` enum('M','F') NOT NULL DEFAULT 'M',
  `religion` varchar(32) NOT NULL,
  `permanent_address` text,
  `temporary_address` text,
  `qualfication` varchar(128) DEFAULT NULL,
   `punch_card_no` varchar(16) DEFAULT NULL,
  `photo` varchar(32) DEFAULT NULL,
  
PRIMARY KEY (`emp_id`),
  UNIQUE KEY `token_no` (`token_no`),
  UNIQUE KEY `email_id` (`email_id`),
  UNIQUE KEY `photo` (`photo`),
  UNIQUE KEY `punch_card_no` (`punch_card_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1

above is my existing table with no records after this i go to next step for partitioning

-----And partition command is------

alter table emp_info PARTITION BY RANGE(emp_id) 
(PARTITION p0 VALUES LESS THAN (100),
 PARTITION p1 VALUES LESS THAN (500),
 PARTITION p2 VALUES LESS THAN maxvalue)

----Then this error is appearing below mentioned----

#1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

After that i had removed the unique key of above table such as UNIQUE KEY `token_no` (`token_no`),
  UNIQUE KEY `email_id` (`email_id`),
  UNIQUE KEY `photo` (`photo`),
  UNIQUE KEY `punch_card_no` (`punch_card_no`) AND THEN PARTITION DONE FOR THAT TABLE

BUT when i am going to inserting data and make unique key then table seems to be empty with following errors:--

Error

SQL query: Edit

SHOW FULL FIELDS FROM `emp_info` ;

MySQL said: Documentation
#1503 - A UNIQUE INDEX must include all columns in the table's partitioning function

This is my whole procedure  so now plz guidance me sir

Then error message perfectly explains the problem. Please, read the manual, http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations-partitioning-keys-unique-k...:

"The rule governing this relationship can be expressed as follows: All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

In other words, every unique key on the table must use every column in the table's partitioning expression."

Dear sir

#58910 with ref. this id i have make a partition on table which have not primay key and unique key, partition made successfully but problem is coming when we are making primary key and unique key on fields as already mentioned so plz give me simple example how we can make partition with unique key plz help sir

sir iam waiting plz reply

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.

As per mysql explaination "all columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have". Why only unique/primary keys, why not other keys too ?