MySQL Forums
Forum List  »  Partitioning

Can not partition existings tables
Posted by: Olivier Bourdon
Date: June 03, 2010 04:06AM

Hello

I have a few already created tables that I'd like to partition but I always hit error message like
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

whatever partitioning method I use (LIST or HASH)
I know what it basically means because the of restrictions of the partitioning functions but can not determine how I should transform my tables to be able to use partitioning

Note I am using mysql 5.1.47 but can easily migrate to 5.5.3 if required

Here is definition of one table
and the field I'd like to partition on is seg_id which as a finite set of values (around 30)

mysql> show create table Items\G;
Table: Items
Create Table: CREATE TABLE `Items` (
`db_id` bigint(20) NOT NULL AUTO_INCREMENT,
`last_modified` datetime NOT NULL,
`created` datetime DEFAULT NULL,
`digest` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`is_active` bit(1) NOT NULL,
`seg_id` bigint(20) DEFAULT NULL,
`name` varchar(255) COLLATE latin1_general_cs NOT NULL,
`arch` varchar(20) COLLATE latin1_general_cs DEFAULT NULL,
`version` varchar(40) COLLATE latin1_general_cs DEFAULT NULL,
`l_release` varchar(60) COLLATE latin1_general_cs DEFAULT NULL,
`type` varchar(64) COLLATE latin1_general_cs DEFAULT NULL,
`parent_uri` varchar(255) COLLATE latin1_general_cs NOT NULL,
`fullname` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`origname` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`soft_group` varchar(60) COLLATE latin1_general_cs DEFAULT NULL,
`summary` text COLLATE latin1_general_cs,
`l_license` varchar(120) COLLATE latin1_general_cs DEFAULT NULL,
`installed_size` bigint(20) DEFAULT NULL,
`l_file_date` datetime DEFAULT NULL,
`l_done_date` datetime DEFAULT NULL,
`l_url` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`is_fake` bit(1) DEFAULT NULL,
`is_finished` bit(1) DEFAULT NULL,
`flag` int(11) DEFAULT NULL,
`l_source` varchar(120) COLLATE latin1_general_cs DEFAULT NULL,
`binary_uri` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`location` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`uri` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`description` text COLLATE latin1_general_cs,
`size` bigint(20) DEFAULT NULL,
`md5sum` varchar(255) COLLATE latin1_general_cs DEFAULT NULL,
`binary_uploaded` bit(1) DEFAULT NULL,
`l_status` bigint(20) DEFAULT NULL,
PRIMARY KEY (`db_id`),
UNIQUE KEY `is_active` (`is_active`,`seg_id`,`name`,`arch`,`version`,`l_release`,`type`,`parent_uri`),
KEY `ix_l_source` (`l_source`),
KEY `ix_l_origname` (`origname`),
KEY `ix_l_fullname` (`fullname`),
KEY `ix_l_ac` (`is_fake`),
KEY `ix_l` (`seg_id`,`name`),
KEY `fk_l_status` (`l_status`)
) ENGINE=MyISAM AUTO_INCREMENT=64567419 DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs

Options: ReplyQuote


Subject
Views
Written By
Posted
Can not partition existings tables
3133
June 03, 2010 04:06AM


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.