Re: make number of partitions variable

[Mat private]

Hi Phil,

the code seems clear to me. Thanks. I didn't know about EXECUTE exec_str, handy ;-)

A problem I just encountered was that it is not possible to partition by date.

This is my table:

CREATE TABLE `ratioticks` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`symbol` varchar(8) COLLATE latin1_general_ci DEFAULT NULL,
`stockPrice` double(12,3) DEFAULT NULL,
`indexSymbol` varchar(8) COLLATE latin1_general_ci DEFAULT NULL,
`indexPrice` double(12,3) DEFAULT NULL,
`ratio` double(25,15) DEFAULT NULL,
`winStockTime` double(25,15) DEFAULT NULL,
`dbtime` bigint(16) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `symbol` (`symbol`,`indexSymbol`,`dbtime`)
) ENGINE=MyISAM AUTO_INCREMENT=368983 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci DELAY_KEY_WRITE=1

for a test I made this partioning query (before you came with the SP code)

alter table tickdata.ratioticks
partition by range (dbtime)
(
partition p0 values less than (20080528000000),
partition p1 values less than (20080529000000),
partition p2 values less than (20080530000000),
partition p3 values less than (20080602000000),
partition p4 values less than maxvalue
);


when I run the query, I get

Error Code : 1503
A PRIMARY KEY must include all columns in the table's partitioning function
(0 ms taken)


is tehre a way to work around this?

Regards,

Matthijs