Bug #62745 ALTER TABLE clauses have ordering restrictions
Submitted: 14 Oct 2011 20:54 Modified: 10 Nov 2011 18:56
Reporter: Rick James Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.1.48, 5.1.60 OS:Linux (2.6.18-164.2.1.el5)
Assigned to: CPU Architecture:Any
Tags: alter, engine
Triage: Needs Triage: D3 (Medium)

[14 Oct 2011 20:54] Rick James
Description:
In this, notice that one ordering of the clauses fails with a syntax error; the other works.

mysql> CREATE TABLE foo (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ORDER BY i,
    ->     ENGINE=MyISAM;
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 '=MyISAM' at line 4
mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ENGINE=MyISAM,
    ->     ORDER BY i;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

The BNF in 
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
clearly states that the order is not important.

How to repeat:
CREATE TABLE foo (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
ALTER TABLE foo
    DROP PRIMARY KEY,
    ORDER BY i,
    ENGINE=MyISAM;   # Fails
ALTER TABLE foo
    DROP PRIMARY KEY,
    ENGINE=MyISAM,
    ORDER BY i;      # Succeeds

Suggested fix:
Either:
* Fix the ALTER TABLE doc, or
* Fix the code to really allow arbitrary order of ALTER clauses.
[15 Oct 2011 14:09] Valeriy Kravchuk
Thank you for the bug report, Verified just as described with 5.1.60 also:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.60 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE foo (i INT, PRIMARY KEY(i)) ENGINE=InnoDB;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ORDER BY i,
    ->     ENGINE=MyISAM; 
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 '=MyISAM' at line 4
mysql> ALTER TABLE foo
    ->     DROP PRIMARY KEY,
    ->     ENGINE=MyISAM,
    ->     ORDER BY i;  
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
[15 Oct 2011 14:15] Peter Laursen
I checked 5.0.90 and 5.5.16 and it is the same.
[10 Nov 2011 18:56] Paul Dubois
It's a grammar ambiguiity. Will update ORDER BY description to indicate that it should be last.