Bug #32432 ALTER TABLE does not accept the ENGINE and PARTITION clauses together
Submitted: 16 Nov 2007 10:55 Modified: 29 Nov 2007 22:22
Reporter: Giuseppe Maxia Email Updates:
Status: Patch pending Impact on me:
None 
Category:MySQL Server: Parser Severity:S2 (Serious)
Version:5.1.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, engine, parser, partition

[16 Nov 2007 10:55] Giuseppe Maxia
Description:
If you want to change a MyISAM table to a partitioned ARCHIVE, or a partitioned INNODB, you can't do that in a single ALTER TABLE.
Combining ENGINE=xxx and PARTITION BY yyy in the same alter table statement results in an error.

alter table t1 
    engine = archive,
    partition by range (id)
    ( 
        partition p1 values less than (1000),
        partition p2 values less than (maxvalue)
    );

ERROR 1064 (42000): You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server version 
for the right syntax to use near 'partition by range (id)
    ( 
        partition p1 values less than (1000),
   ' at line 4

Workarounds exists (see below), but they require either two ALTER TABLE statements (thus processing the data twice) or 4 instructions to copy the data to a new table (and doubling the data in the meantime).

How to repeat:
drop table if exists t1;
create table t1 (id int ) engine = MyISAM;
alter table t1 
    engine = archive,
    partition by range (id)
    ( 
        partition p1 values less than (1000),
        partition p2 values less than (maxvalue)
    );

alter table t1 
    engine = innodb,
    partition by range (id)
    ( 
        partition p1 values less than (1000),
        partition p2 values less than (maxvalue)
    );

Suggested fix:
As a workaround, use two separate ALTER TABLE instructions. 
1a. change the engine, 
1b. apply partitions.

Or 

2a. create a new partitioned table, 
2b. copy the data, 
2c. remove the original one,
2d. rename the partitioned table
[16 Nov 2007 11:52] Giuseppe Maxia
On further examination, ALTER TABLE seems to refuse any other clause when PARTITION BY is invoked.

As noted before, this means that data needs to be processed twice. For large data warehouse tables, which are prime candidates for partitioning, it is mighty inconvenient.
[23 Nov 2007 10:39] Lu Jingdong
There are some differences between the realization of "alter table with 
partition clauses" and reference manual.

The reference manual explains the "ALTER TABLE syntax" like this:
 ALTER TABLE tbl_name alter_specification [, alter_specification] ...

It will get an error result like this bug mentioned. But if you use this 
kind of statement ( no comma between engine clause and partition clause) 
it will be right. Because defination of  "alter_commands" in 
sql/sql_yacc.yy  is like this (no comma between "alter_list" and 
"opt_partitioning").

mysql> alter table t1
    ->     engine = archive
    ->  partition by range (id)
    ->     (
    ->         partition p1 values less than (1000),
    ->         partition p2 values less than (maxvalue)
    ->     );
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0
[23 Nov 2007 11:36] Giuseppe Maxia
Not a bug.
Simply a typo in the SQL command. It should be without a comma
[23 Nov 2007 15:52] Chad MILLER
I see the problem and will consider the patch sent to internals@lists.
[29 Nov 2007 22:22] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/38895

ChangeSet@1.2610, 2007-11-29 17:22:13-05:00, cmiller@zippy.cornsilk.net +3 -0
  Bug#32432: ALTER TABLE does not accept the ENGINE and PARTITION \
  	clauses together
  
  The manual is wrong, but does suggest a good idea.  A 
  partitioning operation must come at the end of an ALTER TABLE 
  statement, but it should be allowed to be preceeded by commas
  if there are other alterations to be performed.
  
  Now, make commas optional.
  
  Thanks to Mikael for defining the specialness of partitioning 
  for me.