Description:
Table comment and partition statements cannot co-exist in a single CREATE TABLE statement.
The following statement, at first glance, contains no syntax error but MySQL
client produced the following error:
ERROR 1064 (42000) at line 37: 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 linear key(id)
partitions 4' at line 10
create table if not exists t_linearpart (
id int unsigned auto_increment key,
lname varchar(20) not null,
fname varchar(20) not null,
sex enum('M','F'),
score mediumint unsigned,
index (lname,fname)
comment 'table cannot have non-integer unique key on linear partition'
) comment "linear hash is also possible",
partition by linear key(id)
partitions 4;
However, when separated, CREATE TABLE with comment ONLY and with partition ONLY produce no MySQL error.
How to repeat:
Run the following CREATE TABLE statement on MySQL client:
create table if not exists t_linearpart (
id int unsigned auto_increment key,
lname varchar(20) not null,
fname varchar(20) not null,
sex enum('M','F'),
score mediumint unsigned,
index (lname,fname)
comment 'table cannot have non-integer unique key on linear partition'
) comment "linear hash is also possible",
partition by linear key(id)
partitions 4;
The following error is produced:
ERROR 1064 (42000) at line 37: 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 linear key(id)
partitions 4' at line 10
Suggested fix:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
table_options:
table_option [[,] table_option] ...
table_option:
...
| COMMENT [=] 'string'
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY [ALGORITHM={1|2}] (column_list)
| RANGE{(expr) | COLUMNS(column_list)}
| LIST{(expr) | COLUMNS(column_list)} }
[PARTITIONS num]...
Based on the Syntax above for CREATE TABLE, table comment and partition should be able to co-exist.
Description: Table comment and partition statements cannot co-exist in a single CREATE TABLE statement. The following statement, at first glance, contains no syntax error but MySQL client produced the following error: ERROR 1064 (42000) at line 37: 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 linear key(id) partitions 4' at line 10 create table if not exists t_linearpart ( id int unsigned auto_increment key, lname varchar(20) not null, fname varchar(20) not null, sex enum('M','F'), score mediumint unsigned, index (lname,fname) comment 'table cannot have non-integer unique key on linear partition' ) comment "linear hash is also possible", partition by linear key(id) partitions 4; However, when separated, CREATE TABLE with comment ONLY and with partition ONLY produce no MySQL error. How to repeat: Run the following CREATE TABLE statement on MySQL client: create table if not exists t_linearpart ( id int unsigned auto_increment key, lname varchar(20) not null, fname varchar(20) not null, sex enum('M','F'), score mediumint unsigned, index (lname,fname) comment 'table cannot have non-integer unique key on linear partition' ) comment "linear hash is also possible", partition by linear key(id) partitions 4; The following error is produced: ERROR 1064 (42000) at line 37: 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 linear key(id) partitions 4' at line 10 Suggested fix: CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options] [partition_options] table_options: table_option [[,] table_option] ... table_option: ... | COMMENT [=] 'string' partition_options: PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY [ALGORITHM={1|2}] (column_list) | RANGE{(expr) | COLUMNS(column_list)} | LIST{(expr) | COLUMNS(column_list)} } [PARTITIONS num]... Based on the Syntax above for CREATE TABLE, table comment and partition should be able to co-exist.