MySQL Bugs: #70133: CREATE TABLE doesn't follow syntax in mysql manual

Bug #70133	CREATE TABLE doesn't follow syntax in mysql manual
Submitted:	23 Aug 2013 10:23	Modified:	13 Oct 2013 7:39
Reporter:	Jim Michaels	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.27	OS:	Windows (win7sp1ult64)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
CREATE TABLE IF NOT EXISTS counters (
		  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		  url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE INDEX (url),
		  counter BIGINT NOT NULL DEFAULT '1'
		)

#1064 - 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 'INDEX (url), counter BIGINT NOT NULL DEFAULT '1' )' at line 3

according the syntax diagram at http://dev.mysql.com/doc/refman/5.7/en/create-table.html
this should succeed. but it fails at INDEX or at (url)

I checked the manual for even version 5.5 and 5.1 and the syntax for UNIQUE INDEX in a column is still there. it's possible it only accepts the word "KEY".

How to repeat:
paste the following syntax in mysql.exe or phpmyadmin:

CREATE TABLE IF NOT EXISTS counters (
		  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		  url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE INDEX (url),
		  counter BIGINT NOT NULL DEFAULT '1'
		);

CREATE TABLE IF NOT EXISTS counters (
		  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		  url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE (url),
		  counter BIGINT NOT NULL DEFAULT '1'
		);
causes the other error at (url). but it also should not. keyword INDEX is optional. it doesn't like the keyword UNIQUE alone either.

I'd say this is expected and documented behavior. Look:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.13-log |
+------------+
1 row in set (0.04 sec)

mysql> CREATE TABLE IF NOT EXISTS counters (
    ->            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->            url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQ
UE (url),
    ->            counter BIGINT NOT NULL DEFAULT '1'
    ->          );
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 '(url)
,
                  counter BIGINT NOT NULL DEFAULT '1'
                )' at line 3

In the above you just had used wrong syntax, you can only use UNIQUE [KEY] alone, without column name, in column definition. Please, double check syntax description. Now, this is your real problem:

mysql> CREATE TABLE IF NOT EXISTS counters (
    ->            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->            url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQ
UE,
    ->            counter BIGINT NOT NULL DEFAULT '1'
    ->          );
ERROR 1170 (42000): BLOB/TEXT column 'url' used in key specification without a k
ey length

You just can NOT define any index on thew entire LONGTEXT column - you have to specify prefix. So, either you switch to VARCHAR(N):

mysql> CREATE TABLE IF NOT EXISTS counters (
    ->            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->            url VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL
UNIQUE,
    ->            counter BIGINT NOT NULL DEFAULT '1'
    ->          );
Query OK, 0 rows affected (1.27 sec)

Or try to add UNIQUE KEY on prefix later in the statement:

mysql> DROP TABLE counters;
Query OK, 0 rows affected (0.40 sec)

mysql> CREATE TABLE IF NOT EXISTS counters (
    ->            id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->            url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    ->            counter BIGINT NOT NULL DEFAULT '1',
    ->                   UNIQUE KEY k1(url(255))
    ->          );
Query OK, 0 rows affected (0.71 sec)

There is a limit on prefix length, depending on storage engine and character set used, but that's another story...

I specifically see the word 
[KEY | INDEX] which means it can use either KEY or INDEX in that particular instance after the word UNIQUE. if you have not seen this kind of standard unix options syntax before, that's what this means.
read the diagram carefully again.

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [reference_definition]

either this changed or I must have missed something.

but I did see this:
create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...

I think I had even tried a CONSTRAINT, but this failed also.
let me try again and see what mysql does.

Database changed
mysql> CREATE TABLE IF NOT EXISTS `counters2` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `url` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    ->   `counter` BIGINT(20) NOT NULL DEFAULT '1',
    ->   PRIMARY KEY (`id`),
    ->   CONSTRAINT UNIQUE INDEX (`url`)
    -> )
    -> ;
ERROR 1170 (42000): BLOB/TEXT column 'url' used in key specification without a key length
mysql>

I didn't think LONGTEXT had a length. it's a blob.
please fix?
Server version: 5.5.27 MySQL Community Server (GPL)
although I will best this same problem is still in 5.7, try it.

mysql> CREATE TABLE IF NOT EXISTS `counters2` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `url` LONGTEXT(2000) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    ->   `counter` BIGINT(20) NOT NULL DEFAULT '1',
    ->   PRIMARY KEY (`id`),
    ->   CONSTRAINT UNIQUE INDEX (`url`)
    -> );
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 '(2000
) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `counter` BIGINT(20) NOT ' at line 3
mysql>

mysql rejects specifying a length for LONGTEXT since it is a blob. so I get an error either way I try to make this.

Thank you for the bug report. Verified as Documentation bug.

There are a couple of issues here, but no documentation bug that I can see.

First issue:

CREATE TABLE IF NOT EXISTS counters (
		  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
		  url LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL UNIQUE INDEX (url),
		  counter BIGINT NOT NULL DEFAULT '1'
		)

As already noted, this produces an error because in a column definition, you don't name the column again following INDEX.

Second issue:

mysql> CREATE TABLE IF NOT EXISTS `counters2` (
    ->   `id` INT(11) NOT NULL AUTO_INCREMENT,
    ->   `url` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
    ->   `counter` BIGINT(20) NOT NULL DEFAULT '1',
    ->   PRIMARY KEY (`id`),
    ->   CONSTRAINT UNIQUE INDEX (`url`)
    -> )
    -> ;
ERROR 1170 (42000): BLOB/TEXT column 'url' used in key specification without a key length

Jim comments: "I didn't think LONGTEXT had a length. it's a blob."

That's correct, but what the error means is that you must specify a length *for the index* for TEXT or BLOB columns, not for the column itself.

Please see: http://dev.mysql.com/doc/refman/5.7/en/create-index.html

"BLOB and TEXT columns also can be indexed, but a prefix length must be given."

That section has additional information about index prefixes.

So the correct CREATE TABLE statement is:

CREATE TABLE IF NOT EXISTS `counters2` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `url` LONGTEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `counter` BIGINT(20) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  CONSTRAINT UNIQUE INDEX (`url`(255))
);

The prefix length can be a value other than 255 if desired.

you are missing the point of the bug report.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

note that is says the the documentation I linked to in OP that you append
 [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] after a column definition before you put the comma. so I am allowed supposedly to do 

abc LONGTEXT NOT NULL DEFAULT '' UNIQUE INDEX,

this is the documentation bug the other guy mentioned. 
I guess I wasn't clear the first time.

unless, this is truly a mysql bug... in which case I would really like to be able to use this kind of syntax! it would make things SOOO much easier for me...

if it's any consequence, declaring a bug as a non-bug is not my way of fixing bugs. it doesn't solve problems. it just leaves them around for someone else to solve. again.

"if it's any consequence, declaring a bug as a non-bug is not my way of fixing bugs."

I think that's unwarranted. There's no attempt here to avoid dealing with this bug report.

Thanks for the additional clarification. Upon further investigation, I find that UNIQUE INDEX and UNIQUE KEY seem not to be synonymous at the end of a column definition. This surprises me because INDEX and KEY generally can be used interchangeably. I'll inquire about this, whether it's deliberate or an oversight.

However, even if we use KEY, this will not work, for BLOB or TEXT columns:

CREATE TABLE t (abc LONGTEXT NOT NULL DEFAULT '' UNIQUE KEY);

It remains true that in MySQL such columns can be indexed only if a prefix length is given, which there is no syntax for at the end of a column definition. A separate UNIQUE clause is required to declare the index:

CREATE TABLE t (
abc LONGTEXT NOT NULL DEFAULT '',
UNIQUE KEY (abc(255))   -- or UNIQUE INDEX
);

You *can*, however, use UNIQUE KEY at the end of column definitions for other data types. Example:

CREATE TABLE t (abc VARCHAR(20) NOT NULL DEFAULT '' UNIQUE KEY);

Looking at the the grammar (source file sql/sql_yacc.yy) shows that indeed only UNIQUE KEY and not UNIQUE INDEX is permitted at the end of column definitions. And looking again at the manual, that's how it reads so me. So let's return to some earlier comments to see where we differ.

"
you are missing the point of the bug report.

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

note that is says the the documentation I linked to in OP that you append
 [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] after a column definition before you put the comma. so I am allowed supposedly to do 

abc LONGTEXT NOT NULL DEFAULT '' UNIQUE INDEX,
"

I don't see where this indicates that you can use the CONSTRAINT clause after the column definition before the comma.

The create_definition syntax consists of a list of alternatives. "col_name column_definition" and the CONSTRAINT clause are different alternatives. They should be separated by a comma if both are used.

The applicable syntax for UNIQUE at the end of the column definition is:

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

And here only UNIQUE KEY is permitted, not UNIQUE INDEX.

interesting. I had never assumed key and index were synonymous. I had though that a key always referred to something like a foreign or primary key only. that's about as far as we got in db class 20 yarns ago, we didn't cover indexes to my knowledge, I did self-learning later to learn this...

I think I need to study up on this subject more...

Okay, I think we've covered the relevant cases now. Re-closing.

nothing changed in the documentation. did you fix mysql instead? I have no indications that anything was done. it was just closed out of hand.
either this is a documentation bug, or it is a mysql bug.

Closed out of hand? No. As stated, "Okay, I think we've covered the relevant cases now." The comment at [27 Sep 14:43] Paul Dubois explains the behavior you observe, and why. So the documentation is consistent with observed behavior. Unless I am missing something, there is no documentation bug.

in case you have never read *nix man pages, the | in UNIQUE [INDEX|KEY] indicates alternation whereas a space indicates sequence and [] (to my best understanding) indicates an option (unless somehow I have misunderstood something that's also or supposed to be alternation only). so:
UNIQUE
or
UNIQUE KEY
or
UNIQUE INDEX
if this is optional, otherwise
UNIQUE KEY
or
UNIQUE INDEX
if this is alternation only,
as specified in the manual. what you found in lex & yacc doesn't match the manual either way. please fix one or the other.
thank you.

http://linuxcommand.org/reading_man_pages.php
here is how to read a *nix man page. note the part where it says how to handle [OPTION] and [FILE].
according to this, [] indicate whatever is in it is optional. that is how I have taken it in times past, wrote my documentation for commandline tools, and such for years. alternation is simply done with a | symbol, but there is no way to group things except maybe using perens in *nix, and that doesn't translate to SQL well. perhaps oracle IS using [] for alternation AND option. I wouldn't know. I am going to double-check to see if there is such a "how to read the manual" page.

if oracle has somehow changed that meaning (I think they have), oracle needs to include a page near the top of the documentation on how to read the manual. otherwise, a lot of people will misconstruct the syntax in their SQL code when reading those.

I found http://dev.mysql.com/doc/refman/5.7/en/manual-conventions.html
which says [] is alternation AND option (confusing).

so it could be any of the 3 items I mentioned.
UNIQUE
UNIQUE INDEX
UNIQUE KEY

lex & yacc (flex and bison?) is not treating them as the same.
so there's either a doc issue or a flex issue, one of which needs to be fixed.
I think I finally had to look up "triage" because they are now using medical terms in QA and I am having to learn new vocab.

I think this is indeed a bug. labeling this as "Status: not a bug" is wrong in this case.
my understanding is that the docs are OK. it's the the lex and yacc code that needs to be fixed, and that this isn't really a documentation bug, it's a lexer+parser bug. I don't happen to have 

http://www.iso.org/iso/search.htm?qt=9075&searchSubmit=Search&sort=rel&type=simple&publish...!

handy to check the exact syntax against. costs too much for my wallet.

"
I found http://dev.mysql.com/doc/refman/5.7/en/manual-conventions.html
which says [] is alternation AND option (confusing).
"

It does say that [] means optional, and it discusses [] in the context of its use in conjunction with |, but does not say that [] *is* alternation, that I can see.

Regarding, [], yes, I have read Unix man pages. You are correct that UNIQUE [INDEX|KEY] can mean any of:

UNIQUE
UNIQUE INDEX
UNIQUE KEY

However, that syntax is permitted only in:

[CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]

which is one possible create_definition value.

That syntax is not permitted in:

column_definition:

    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]

That permits only UNIQUE or UNIQUE KEY.

If you want to use UNIQUE INDEX, you must specify it as a separate create_definition clause, separated from the column_definition by a comma.

UNIQUE INDEX as part of a column_definition is not permitted by the server. And it's not said to be permitted in the documentation.

You might *want* UNIQUE INDEX to be permitted in column_definition, but it's not. The server doesn't permit it. The documentation does not say it's permitted. And this has been true in MySQL for a long time.

If you consider it a bug in the server that it doesn't permit this case, you could reopen this bug report and change it to a feature request. But as far as I can see, the documentation accurately describes server behavior and there is no docs bug.