3

I am simply trying to convert a table from MyISAM to INNODB. This is for a bugzilla upgrade with testopia.

This simple command fails. ALTER TABLE table_name TYPE = INNODB;

ERROR 1214 (HY000): The used table type doesn't support FULLTEXT indexes

I know it does not support FULLTEXT indexes, never the less I want it to convert. Would I have to drop the fulltext indexes on the table before conversion? Is there a way to query for them and drop them all?

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
user150484
  • 33
  • 1
  • 4

1 Answers1

5

First, see your CREATE TABLE statement:

SHOW CREATE TABLE tablename

It will show you all your fulltext indexes like this:

…,
FULLTEXT KEY key_name (column_list),
…

Drop all these keys:

ALTER TABLE tablename DROP INDEX key_name;
…

, then convert:

ALTER TABLE tablename ENGINE=InnoDB;
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Worked. Alternatively, is there some way to preserve the data? I should probably gauge how important this data is. – user150484 Aug 04 '09 at 17:10
  • Sure, just backup it :) `mysqldump --all-databases > backup.sql`. `FULLTEXT` indexes are secondary data source, i. e. they contain only the data contained in the table itself. Unless anything goes wrong with your server, you will not lose any information you will not be able to recover. – Quassnoi Aug 04 '09 at 17:15
  • I will do a dump and just save it. You say I should never need it? If so sounds good. Now I move onto my next error ;) – user150484 Aug 04 '09 at 21:06