Re: Why is this an invalid hash function?

[avi weiss]

Hi Mikael;

Thanks for answering. I was trying to partition a table with 11 million records, created with this:

DROP TABLE IF EXISTS `devvnsp_usage`.`pagelog`;
CREATE TABLE `devvnsp_usage`.`pagelog` (
`pl_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pl_pn_id` int(11) NOT NULL DEFAULT '0',
`pl_r_id` int(11) NOT NULL DEFAULT '0',
`pl_s_id` varchar(255) NOT NULL DEFAULT '',
`site_id` int(11) DEFAULT NULL,
KEY `pl_datetime` (`pl_datetime`),
KEY `pl_s_id` USING BTREE (`pl_s_id`),
KEY `site_id` USING BTREE (`site_id`)
)

Here is how I attempted to partition:

"ALTER table pagelog partition by hash (date_add(pl_datetime, interval 0 day)+0) partitions 365"

this resulted in

"the PARTITION function returns the wrong type"

To break down the problem, I finally dropped the table, and found a partitioning hash function that wasnt a problem:

DROP TABLE IF EXISTS `devvnsp_usage`.`pagelog`;
CREATE TABLE `devvnsp_usage`.`pagelog` (
`pl_datetime` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`pl_pn_id` int(11) NOT NULL DEFAULT '0',
`pl_r_id` int(11) NOT NULL DEFAULT '0',
`pl_s_id` varchar(255) NOT NULL DEFAULT '',
`site_id` int(11) DEFAULT NULL,
KEY `pl_datetime` (`pl_datetime`),
KEY `pl_s_id` USING BTREE (`pl_s_id`),
KEY `site_id` USING BTREE (`site_id`)
)
partition by hash (dayofyear(pl_datetime)) partitions 365;

However, when I tried to insert a single record:

insert into pagelog values('2006-05-11 00:01:01', 1 , 1 , 1 , 1)

I got the following error

"got error 24 from storage engine"

any insight/help/thoughts/pointers, etc would be most appreciated.