MySQL Forums
Forum List  »  Partitioning

Can I partition a table with a varchar primary key?
Posted by: Eric B
Date: June 07, 2007 10:06AM

Hi,

I'm struggling with getting my tables partitioned. All the tables I want to partition have a single primary key id which is a varchar. They also have a created datetime field to let me know when each record was added to the db.

Ex:
CREATE TABLE `document` (
`document_id` varchar(50) NOT NULL DEFAULT '',
`filename` varchar(255) NOT NULL DEFAULT '',
`document` longblob,
`created` datetime DEFAULT NULL,
`last_modified` datetime DEFAULT NULL,
`size` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`document_id`),
) ENGINE=MyISAM DEFAULT CHARSET=latin1


Ideally, I want to partition the table on a per month basis. Originally I though I could do the following:
partition by range( Year( created) * 1000 + month(created) )(
partition 2004_01 values less than (200401),
partition 2004_02 values less than (200402) );

but that gave me an error 1491: A PRIMARY KEY must include all columns in the table's partitioning function.


So that didn't work. And adding the created field to my PK may cause too many problems that I don't want to deal with at the moment. So instead, I looked at my id field. Lucikly, the first 16 characters are hex representation of the timestamp. So a little quick script generated ranges for me to parition against that as well.

partition by range( id )
(
partition 2002_01 values less than ('000000ebc0102c7f00000000000000000000'),
partition 2002_02 values less than ('000000ec50423c7f00000000000000000000'),
partition 2002_03 values less than ('000000ece0744c7f00000000000000000000')
);

However, that causes me an error as well:
Error Code : 1064
VALUES value must be of same type as partition function near '),

partition 2002_02 values less than ('000000ec50423c7f00000000000000000000'),' at line 7


Can anyone point me in the right direction please? Is it impossible to partition a table this way? I've read (and reread) the docs multiple times, but can't seem to find anything to help me out.

I'm running version 5.1.19-beta-community-nt-debug.

Thanks!

Eric



Edited 2 time(s). Last edit at 06/07/2007 10:22AM by Eric B.

Options: ReplyQuote


Subject
Views
Written By
Posted
Can I partition a table with a varchar primary key?
6660
June 07, 2007 10: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.