problem with LIST COLUMNS partitioning ("A PRIMARY KEY must include all columns in the table's partitioning function")

[phdphd]

Hi All, I am trying to partition an existing table with LIST COLUMNS partitioning.

 

The structure of my statement is the following :

alter table table_name
PARTITION BY LIST COLUMNS(field_name) (
    PARTITION p1 VALUES IN('value1'),
    PARTITION p2 VALUES IN(' value2')
)

I get the message "A PRIMARY KEY must include all columns in the table's partitioning function".

 

I do not understand this message. In the example below (successfully tested) taken from http://dev.mysql.com/doc/refman/5.5/en/partitioning-columns-list.htm it does not seem that the city column is a primary key. Actually there seems to be no primary key at all in the table definition.

 

CREATE TABLE customers_1 (
    first_name VARCHAR(25),
    last_name VARCHAR(25),
    street_1 VARCHAR(30),
    street_2 VARCHAR(30),
    city VARCHAR(15),
    renewal DATE
)
PARTITION BY LIST COLUMNS(city) (
    PARTITION pRegion_1 VALUES IN('Oskarshamn', 'Högsby', 'Mönsterås'),
    PARTITION pRegion_2 VALUES IN('Vimmerby', 'Hultsfred', 'Västervik'),
    PARTITION pRegion_3 VALUES IN('Nässjö', 'Eksjö', 'Vetlanda'),
    PARTITION pRegion_4 VALUES IN('Uppvidinge', 'Alvesta', 'Växjo')
);

There must be something I did not grasp. Thanks for helping.

 

[vinny42]

 

Actually there seems to be no primary key at all in the table definition.

 

That may be the issue; if you have a PK then that should probably be taken into account by the partitions, otherwise you could get duplicate values in two partitions.

[phdphd]

OK. From the example taken from  http://dev.mysql.com I sucessfully added an id field then created a pk spanning both fields (id+city). I then run

SHOW CREATE TABLE `customers_1` 

and got

CREATE TABLE `customers_1` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `first_name` varchar(25) DEFAULT NULL,
 `last_name` varchar(25) DEFAULT NULL,
 `street_1` varchar(30) DEFAULT NULL,
 `street_2` varchar(30) DEFAULT NULL,
 `city` varchar(15) NOT NULL DEFAULT '',
 `renewal` date DEFAULT NULL,
 PRIMARY KEY (`id`,`city`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
/*!50500 PARTITION BY LIST  COLUMNS(city)
(PARTITION pRegion_1 VALUES IN ('Oskarshamn','Högsby','Mönsterås') ENGINE = InnoDB,
PARTITION pRegion_2 VALUES IN ('Vimmerby','Hultsfred','Västervik') ENGINE = InnoDB,
PARTITION pRegion_3 VALUES IN ('Nässjö','Eksjö','Vetlanda') ENGINE = InnoDB,
PARTITION pRegion_4 VALUES IN ('Uppvidinge','Alvesta','Växjo') ENGINE = InnoDB) */

What does "50500" stand for ?

[mikosiko]

The long and exact behavior explanation:

 

"All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have."

 

 

http://dev.mysql.com/doc/refman/5.5/en/partitioning-limitations-partitioning-keys-unique-keys.html