Bug #17541 Partitioning: Out of memory on DROP PARTITION
Submitted: 18 Feb 2006 4:07 Modified: 18 Feb 2006 17:36
Reporter: Jeremy Cole (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.6 OS:Linux (Fedora Core 4)
Assigned to: CPU Architecture:Any

[18 Feb 2006 4:07] Jeremy Cole
Description:
I get an out of memory error when trying to drop a partition after loading a million rows (500k into two partitions) into the table.

How to repeat:
DROP TABLE IF EXISTS ontime;
CREATE TABLE ontime (
  id                    int unsigned            not null        auto_increment,
  carrier               char(2)                 not null,
  origin                char(3)                 not null,
  destination           char(3)                 not null,
  flight_num            char(5)                 not null,
  flight_time           smallint unsigned       not null,
  tail_num              char(8)                 not null,
  dep_time              datetime                not null,
  arr_time              datetime                not null,
  dep_delay             smallint                not null,
  arr_delay             smallint                not null,
  taxi_out              smallint                not null,
  taxi_in               smallint                not null,
  distance              smallint unsigned       not null,
  cancelled             enum('Y','N')           not null,
  diverted              enum('Y','N')           not null,
  KEY(id),
  INDEX(carrier),
  INDEX(origin, destination),
  INDEX(destination),
  INDEX(tail_num)
)  ENGINE=MyISAM
PARTITION BY RANGE(YEAR(dep_time)*100 + MONTH(dep_time)) (
  PARTITION p_old     VALUES LESS THAN (200001),
  PARTITION p_2000_01 VALUES LESS THAN (200002),
  PARTITION p_2000_02 VALUES LESS THAN (200003),
  PARTITION p_2000_03 VALUES LESS THAN (200004),
  PARTITION p_2000_04 VALUES LESS THAN (200005),
  PARTITION p_2000_05 VALUES LESS THAN (200006),
  PARTITION p_2000_06 VALUES LESS THAN (200007),
  PARTITION p_2000_07 VALUES LESS THAN (200008),
  PARTITION p_2000_08 VALUES LESS THAN (200009),
  PARTITION p_2000_09 VALUES LESS THAN (200010),
  PARTITION p_2000_10 VALUES LESS THAN (200011),
  PARTITION p_2000_11 VALUES LESS THAN (200012),
  PARTITION p_2000_12 VALUES LESS THAN (200101),
  PARTITION p_2001_01 VALUES LESS THAN (200102),
  PARTITION p_2001_02 VALUES LESS THAN (200103),
  PARTITION p_2001_03 VALUES LESS THAN (200104),
  PARTITION p_2001_04 VALUES LESS THAN (200105),
  PARTITION p_2001_05 VALUES LESS THAN (200106),
  PARTITION p_2001_06 VALUES LESS THAN (200107),
  PARTITION p_2001_07 VALUES LESS THAN (200108),
  PARTITION p_2001_08 VALUES LESS THAN (200109),
  PARTITION p_2001_09 VALUES LESS THAN (200110),
  PARTITION p_2001_10 VALUES LESS THAN (200111),
  PARTITION p_2001_11 VALUES LESS THAN (200112),
  PARTITION p_2001_12 VALUES LESS THAN (200201)
);

Load the following mysqldump into the table:

http://jcole.us/files/ontime.sql.gz

mysql> alter table ontime drop partition p_2000_01;
ERROR 1037 (HY001): Out of memory; restart server and try again (needed 8 bytes)
[18 Feb 2006 17:36] Miguel Solorzano
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

mysql> select count(*) from ontime;
+----------+
| count(*) |
+----------+
|   914976 |
+----------+
1 row in set (0.01 sec)

mysql> alter table ontime drop partition p_2000_01;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.8-beta-debug |
+------------------+
1 row in set (0.00 sec)