Bug #19625 CREATE TABLE .... PARTITION BY order not per docs
Submitted: 8 May 2006 23:22 Modified: 12 May 2006 23:33
Reporter: Brian Morin Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Partitions Severity:S3 (Non-critical)
Version:5.1.9/5.1BK OS:Linux (Linux)
Assigned to: Jon Stephens CPU Architecture:Any

[8 May 2006 23:22] Brian Morin
Description:
Per the docs the CREATE TABLE syntax should be:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [partition_options]
    [table_options] [select_statement]

[partition_options] is before [table_options]

However, this doesn't work:

DROP TABLE IF EXISTS `order_good`;

CREATE TABLE `order_good` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH(id);

But this does:

DROP TABLE IF EXISTS `order_bad`;

CREATE TABLE `order_bad` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) PARTITION BY HASH(id) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

How to repeat:
With a fresh NDP Cluster database run:

DROP TABLE IF EXISTS `order_good`;

CREATE TABLE `order_good` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 PARTITION BY HASH(id);

DROP TABLE IF EXISTS `order_bad`;

CREATE TABLE `order_bad` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY  (`id`)
) PARTITION BY HASH(id) ENGINE=ndbcluster DEFAULT CHARSET=latin1;

The second create table call fails with a syntax error.

Suggested fix:
Either update the docs or fix the parser.
[9 May 2006 0:02] MySQL Verification Team
Changing Category to Cluster.
[9 May 2006 12:48] MySQL Verification Team
Thank you for the bug report.

mysql> CREATE TABLE `order_bad` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY  (`id`)
    -> ) PARTITION BY HASH(id) ENGINE=MyISAM DEFAULT CHARSET=latin1;
ERROR 1064 (42000): You have an error in your SQL

mysql> CREATE TABLE `order_good` (
    ->   `id` bigint unsigned NOT NULL AUTO_INCREMENT,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH(id);
Query OK, 0 rows affected (0.02 sec)
[10 May 2006 14:40] Mikael Ronström
This is a documentation bug.
The proper order is
[table_options][partition_options]
[12 May 2006 23:33] Jon Stephens
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

Corrected error in CREATE TABLE Syntax.

(Note that the examples shown in the Partitioning chapter were correct.)