drop index with a comma in the name

Question

I have a table (well there are a bunch of them) with an index that contains a comma

....
FULLTEXT KEY `description` (`description`),
  FULLTEXT KEY `description,title` (`description`,`title`), -- <-- HERE
  FULLTEXT KEY `content` (`content`),
  FULLTEXT KEY `content,title` (`content`,`title`)
) ENGINE=MyISAM AUTO_INCREMENT=150296 DEFAULT CHARSET=utf8

I am moving to InnoDB + sphinx so want to drop all the FULLTEXT indexes and these are proving to be a problem.

Quotes:

DROP INDEX 'content,title' ON `table`
ALTER TABLE `table` DROP INDEX 'content,title'

Backticks: DROP INDEX content,title ON table ALTER TABLE table DROP INDEX content,title

Escaped: ALTER TABLE articles DROP INDEX description\,title

Various forms of escaping, also tried using LIKE with wild chars.

Actual error: Can't DROP 'description,title'; check that column/key exists

@CodeBird

SELECT * FROM information_schema.STATISTICS WHERE TABLE_NAME='articles' AND TABLE_SCHEMA='test' AND INDEX_NAME LIKE 'descri%'\G
*************************** 1. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: test
   TABLE_NAME: articles
   NON_UNIQUE: 1
 INDEX_SCHEMA: test
   INDEX_NAME: description,title
 SEQ_IN_INDEX: 1
  COLUMN_NAME: description
    COLLATION: NULL
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: FULLTEXT
      COMMENT:
*************************** 2. row ***************************
TABLE_CATALOG: NULL
 TABLE_SCHEMA: test
   TABLE_NAME: articles
   NON_UNIQUE: 1
 INDEX_SCHEMA: test
   INDEX_NAME: description,title
 SEQ_IN_INDEX: 2
  COLUMN_NAME: title
    COLLATION: NULL
  CARDINALITY: NULL
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: FULLTEXT
      COMMENT:
2 rows in set (0.00 sec)

What is the error you are getting. How about `DROP INDEX 'content\,title' ON `table`` — Saurabh, May 01 '14 at 13:14
If all else fails, can't you create a new table the way you want it, export the data from the old in to the new, drop the old, and then rename the new to the old name? — Patrick Q, May 01 '14 at 13:16
@PatrickQ there are many tables, and 15GB DB in total, would rather find a way to drop it. — dogmatic69, May 01 '14 at 13:17
You are using quotes for the first statement use back ticks. — Mihai, May 01 '14 at 13:21
try selecting the indexes of this table from information_schema, maybe it gives you a clue `SELECT INDEX_NAME FROM information_schema.STATISTICS WHERE TABLE_NAME='your_table_name'` — CodeBird, May 01 '14 at 13:27
Suggestion: Try to rename the index name stored into system table — Luc M, May 01 '14 at 13:30
`ALTER TABLE … DROP … ` with backticks seem to work for me. http://sqlfiddle.com/#!2/af5602 (no error when you build the schema). Also, note that my previous comment did say "if all else fails" ;) It's up to you to determine when you should cut your losses trying to do it this way. — Patrick Q, May 01 '14 at 13:41
I don't see why a comma would cause an issue especially surrounded by backticks, just try to select your indexes and check what is really happening. — CodeBird, May 01 '14 at 13:44
`UPDATE information_schema.STATISTICS SET INDEX_NAME='to_be_dropped' WHERE TABLE_NAME='articles' AND TABLE_SCHEMA='test' AND INDEX_NAME LIKE 'descri%'\G` can you try this if you have root access? — CodeBird, May 01 '14 at 13:49
@CodeBird nice one, that will do. make an answer and its accepted — dogmatic69, May 01 '14 at 13:52

score 2 · Accepted Answer · answered May 01 '14 at 13:55

2

Having root access you should be able to update your index name in information_schema then try dropping them:

UPDATE information_schema.STATISTICS SET INDEX_NAME='to_be_dropped' 
WHERE TABLE_NAME='articles' AND TABLE_SCHEMA='test' 
AND INDEX_NAME LIKE 'descri%'

answered May 01 '14 at 13:55

CodeBird

3,883
2
20
35

I have not figured out what is required to run updates on information_schema, but this made it easy to find all the duff fields and edit manually. – dogmatic69 May 01 '14 at 14:04

score 0 · Answer 2 · answered May 01 '14 at 13:44

Below command should work, please check again and share if getting any error:

ALTER TABLE `mytable` DROP INDEX `content,title`;

You can check below sqlfiddle: First build above statement and then with above statement, it will successfully drop the index. fulltext index drop

score 0 · Answer 3 · answered May 01 '14 at 13:45

Table and column names must be quoted with backticks, but you're using single quotes. (You're using backticks correctly everywhere else).

Change:

DROP INDEX 'content,title' ON `table`;
ALTER TABLE `table` DROP INDEX 'content,title';

To:

DROP INDEX `content,title` ON `table`;
ALTER TABLE `table` DROP INDEX `content,title`;

Also, I suggest you not use table/column/index names that need to be quoted, because it's a hassle and error-prone, as you've seen. An index name of content_title instead of content,title would have saved you this trouble.

As I said in the question, I have tried both quoted and backticked, both no dice. Also this is a DB that was created 10 years before I started on it ;) — dogmatic69, May 01 '14 at 13:47

drop index with a comma in the name

3 Answers3