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. Incidentally, I couldn't find any
reference to this in the documentation anywhere, that the partitioning field
must belong to the PK. In fact, the examples in the docs don't even use a
PK.
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
