MySQL Bugs: #22622: mysqld crashes when reorganizing partitions

Bug #22622	mysqld crashes when reorganizing partitions
Submitted:	23 Sep 2006 1:43	Modified:	23 Sep 2006 2:38
Reporter:	Boyd Hemphill	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Partitions	Severity:	S2 (Serious)
Version:	5.1.7	OS:	Windows (Window XP Media Center)
Assigned to:		CPU Architecture:	Any

Description:
I attempted to take the last partition of a range partitioned table and reorganize it into two partitions.   I first did it with my own code, but learned that the code on this page also causes the same issue:

http://dev.mysql.com/doc/refman/5.1/en/partitioning-management-range-list.html

How to repeat:
Create this table:

CREATE TABLE `members` (
  `id` int(11) default NULL,
  `fname` varchar(25) default NULL,
  `lname` varchar(25) default NULL,
  `dob` date default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 
PARTITION BY RANGE ( YEAR(dob) ) (
  PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM, 
  PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM, 
  PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
  PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)

(note the syntax error in the code on for parition p2 in the documentation)

then run:

ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    PARTITION s0 VALUES LESS THAN (1960),
    PARTITION s1 VALUES LESS THAN (1970)
);

Interesting note:
When I did this through Toad for MySQL I got this error:
Error on rename of '.\quest\members#P#s0#TMP#.MYI' to '.\quest\members#P#s0.MYI' 

When I did it through the MySQL client, mysqld.exe died immediately.

Suggested fix:
Because of the interesting note above I think this might have to do with how Windows XP locks files.

This set of statements also causes the same issue.

create table taks (
  taks_id int unsigned not null auto_increment,
  subject_id int unsigned not null, -- fk to a class table
  region_id int unsigned not null, -- fk to region table
  school_year_id int unsigned not null, -- fk to grading_period table
  student_id int unsigned not null, -- fk to the student table
  birth_dt date not null,
  score_ft varchar(5) not null,
  pass_ind enum('Y','N','NK') not null default 'NK',
  commended_ind enum('Y','N','NK') not null default 'NK',
  primary key (taks_id,region_id),
  index fk_subject$taks(subject_id),
  index fk_region$taks(region_id),
  index fk_school_year$taks(school_year_id),
  index fk_student$taks(student_id)
) engine = myisam
partition by list (region_id) (
  partition p0 values in (4,5,6,7,8),
  partition p1 values in (1,2,3,9,12,13,14,15,20),
  partition p2 values in (10,11,16,17,18,19)
)
;
alter table taks reorganize partition p2 into ( 
  partition p2 values in (10,11,16,17,18,19,21)
)
;

Thank you for the bug report. I was unable to repeat with current released
server. Please upgrade:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.11-beta

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `members` (
    ->   `id` int(11) default NULL,
    ->   `fname` varchar(25) default NULL,
    ->   `lname` varchar(25) default NULL,
    ->   `dob` date default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> PARTITION BY RANGE ( YEAR(dob) ) (
    ->   PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
    ->   PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
    ->   PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM.
    ->   PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
    -> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
  PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
)' at line 10
mysql> CREATE TABLE `members` (
    ->   `id` int(11) default NULL,
    ->   `fname` varchar(25) default NULL,
    ->   `lname` varchar(25) default NULL,
    ->   `dob` date default NULL
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> PARTITION BY RANGE ( YEAR(dob) ) (
    ->   PARTITION p0 VALUES LESS THAN (1970) ENGINE = MyISAM,
    ->   PARTITION p1 VALUES LESS THAN (1980) ENGINE = MyISAM,
    ->   PARTITION p2 VALUES LESS THAN (1990) ENGINE = MyISAM,
    ->   PARTITION p3 VALUES LESS THAN (2000) ENGINE = MyISAM
    -> );
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER TABLE members REORGANIZE PARTITION p0 INTO (
    ->     PARTITION s0 VALUES LESS THAN (1960),
    ->     PARTITION s1 VALUES LESS THAN (1970)
    -> );
Query OK, 0 rows affected (0.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create table taks (
    ->   taks_id int unsigned not null auto_increment,
    ->   subject_id int unsigned not null, -- fk to a class table
    ->   region_id int unsigned not null, -- fk to region table
    ->   school_year_id int unsigned not null, -- fk to grading_period table
    ->   student_id int unsigned not null, -- fk to the student table
    ->   birth_dt date not null,
    ->   score_ft varchar(5) not null,
    ->   pass_ind enum('Y','N','NK') not null default 'NK',
    ->   commended_ind enum('Y','N','NK') not null default 'NK',
    ->   primary key (taks_id,region_id),
    ->   index fk_subject$taks(subject_id),
    ->   index fk_region$taks(region_id),
    ->   index fk_school_year$taks(school_year_id),
    ->   index fk_student$taks(student_id)
    -> ) engine = myisam
    -> partition by list (region_id) (
    ->   partition p0 values in (4,5,6,7,8),
    ->   partition p1 values in (1,2,3,9,12,13,14,15,20),
    ->   partition p2 values in (10,11,16,17,18,19)
    -> )
    -> ;
Query OK, 0 rows affected (0.06 sec)

mysql> alter table taks reorganize partition p2 into (
    ->   partition p2 values in (10,11,16,17,18,19,21)
    -> )
    -> ;
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>