Bug #19771 Error 24 from storage engine during alter to add partitions

• Submitted: 12 May 2006 13:48
• Modified: 12 May 2006 21:12
• Reporter: avi weiss
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.1.9
• OS: Windows (Windows XP)
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[12 May 2006 13:48] avi weiss

Description:

Have 11 million record MyISAM table:

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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

attempted to add partions with the following alter statement:

"alter table pagelog partition by hash (dayofyear(pl_datetime)) partitions 365"

following result is generated:

"got error 24 from storage engine"

How to repeat:

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`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

then execute this sql;

alter table pagelog partition by hash (dayofyear(pl_datetime)) partitions 365

[12 May 2006 21:12] Hartmut Holzgraefe

See "Restrictions and Limitations on Partitioning" :

  * If, when creating tables with a very large number of partitions,
     you encounter an error message such as Got error 24 from 
     storage engine, you may need to increase the value of the 
     open_files_limit system variable.

http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html