Can I partition a table with a varchar primary key?

[Eric B]

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.