Bug #875 Can't find syntax for InnoDB "ALTER TABLE ADD INDEX"
Submitted: 18 Jul 2003 12:36 Modified: 28 Jul 2003 12:10
Reporter: xian Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version: OS:
Assigned to: Paul DuBois CPU Architecture:Any

[18 Jul 2003 12:36] xian
Description:
From the following paragraph (see ***'s) it's not clear what the correct syntax for InnoDB "ALTER TABLE ADD INDEX" is.
I have been unable to find further clarification in the manual:

* The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything, except for InnoDB type tables which support ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...). ********Note that InnoDB does not allow an index_name to be specified. See section 7.5 InnoDB Tables. ********The syntax for other table types is provided only for compatibility, to make it easier to port code from other SQL servers and to run applications that create tables with references. See section 1.8.4 MySQL Differences Compared To SQL-92. 

How to repeat:
.

Suggested fix:
Currently the link associated with "See section 7.5 InnoDB Tables" suggests but does not appear to lead to further information.
[21 Jul 2003 6:33] Indrek Siitan
ALTER TABLE syntax is described in: http://www.mysql.com/doc/en/ALTER_TABLE.html

InnoDB uses the same syntax with the exception that you cannot specify a name for the index, i.e. 
you should just use "ALTER TABLE tablename ADD INDEX (fields)".

But I'll let our Documentation Team look at the pages you mentioned to see if these can be cleared 
up to be less confusing.
[23 Jul 2003 11:01] xian
It appears that, at least in mysqld 4.0.13, InnoDB does accept an IndexName **provided** it contains no spaces.  I prove this in the following console output, with apologies for length:

mysql> show create table Seed;

-------------------------------------------------------------+
| Seed  | CREATE TABLE `Seed` (
  `Name` varchar(255) NOT NULL default '',
  `Source` varchar(255) default NULL,
  PRIMARY KEY  (`Name`)
) TYPE=InnoDB |
+-------+-------------------------------------------------------

mysql> alter table Seed add index MySpecialName (Name);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table Seed add index 'My Special Name' (Name);
ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to yo
ur MySQL server version for the right syntax to use near ''My Special Name' (Name)' at lin
e 1
mysql> show index from Seed;
+-------+------------+---------------+--------------+-------------+-----------+-----------
--+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinalit
y | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+---------------+--------------+-------------+-----------+-----------
--+----------+--------+------+------------+---------+
| Seed  |          0 | PRIMARY       |            1 | Name        | A         |
0 |     NULL | NULL   |      | BTREE      |         |
| Seed  |          1 | MySpecialName |            1 | Name        | A         |
0 |     NULL | NULL   |      | BTREE      |         |
+-------+------------+---------------+--------------+-------------+-----------+-----------
--+----------+--------+------+------------+---------+
2 rows in set (0.00 sec)

mysql>
[28 Jul 2003 10:36] Sergei Golubchik
Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

As explained in the manual, and as you can see from SHOW CREATE TABLE output, MySQL uses backticks ` to quote names, not single ticks. Correct syntax is

alter table Seed add index `My Special Name` (Name);
[28 Jul 2003 11:44] xian
Thank you for pointing out the correct syntax:

"alter table Seed add index `My Special Name` (Name);"

I tried this successfully on my InnoDB table.
Thus the documentation incorrectly states the following:

http://www.mysql.com/doc/en/ALTER_TABLE.html
"Note that InnoDB does not allow an index_name to be specified. See section 7.5 InnoDB Tables."
[28 Jul 2003 12:10] Paul DuBois
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).