FULLTEXT search using InnoDB

Question

I'm trying to add FULLTEXT index to an existing table, but I'm getting the following error.

1214 - The used table type doesn't support FULLTEXT indexes

The query that I'm using is:

alter table <table name> add FULLTEXT INDEX (column name(s))

The same query seems to work on my localhost. My db engine is InnoDB for both localhost and live server.

Show create table displays:

CREATE TABLE `xxx` (
`name1` varchar(50) NOT NULL,
`city1` varchar(30) NOT NULL,
`area1` varchar(100) NOT NULL,
`add1` varchar(100) NOT NULL,
`cont1` varchar(10) NOT NULL,
`gen` varchar(1) NOT NULL,
`type` varchar(10) NOT NULL,
`landm` varchar(30) NOT NULL,
`mo` varchar(10) NOT NULL,
`email` varchar(100) NOT NULL,
`passwd` varchar(100) NOT NULL,
`exp` varchar(10) NOT NULL,
`qual` varchar(30) NOT NULL,
`id` varchar(255) NOT NULL,
`location_map` varchar(500) DEFAULT NULL,
`email_v` varchar(1) NOT NULL DEFAULT 'n',
`v` varchar(1) NOT NULL DEFAULT 'n',
PRIMARY KEY (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Answer 1

InnoDB first offered FULLTEXT capabilities in MySQL version 5.6.4. It seems likely that your server is running an earlier version of MySQL than that. (Hosting vendors, especially the inexpensive ones, are notorious for this.)

Use this query to find the versions of various subsystems.

 show variables like "%version%";