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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | OS: | ||
Assigned to: | Paul DuBois | CPU Architecture: | Any |
[18 Jul 2003 12:36]
xian
[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).