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

Question

I'm getting an error saying that the table type doesn't support FULLTEXT indices. How can I achieve this?

Here's my table:

CREATE TABLE gamemech_chat (
  id bigint(20) unsigned NOT NULL auto_increment,
  from_userid varchar(50) NOT NULL default '0',
  to_userid varchar(50) NOT NULL default '0',
  text text NOT NULL,
  systemtext text NOT NULL,
  timestamp datetime NOT NULL default '0000-00-00 00:00:00',
  chatroom bigint(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY from_userid (from_userid),
  FULLTEXT KEY from_userid_2 (from_userid),
  KEY chatroom (chatroom),
  KEY timestamp (timestamp)
)  ;

*

Answer 1

Before MySQL 5.6 Full-Text Search is supported only with MyISAM Engine.

Therefore either change the engine for your table to MyISAM

CREATE TABLE gamemech_chat (
  id bigint(20) unsigned NOT NULL auto_increment,
  from_userid varchar(50) NOT NULL default '0',
  to_userid varchar(50) NOT NULL default '0',
  text text NOT NULL,
  systemtext text NOT NULL,
  timestamp datetime NOT NULL default '0000-00-00 00:00:00',
  chatroom bigint(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY from_userid (from_userid),
  FULLTEXT KEY from_userid_2 (from_userid),
  KEY chatroom (chatroom),
  KEY timestamp (timestamp)
) ENGINE=MyISAM;

Here is SQLFiddle demo

or upgrade to 5.6 and use InnoDB Full-Text Search.

Answer 2

The problem occurred because of wrong table type.MyISAM is the only type of table that Mysql supports for Full-text indexes.

To correct this error run following sql.

 CREATE TABLE gamemech_chat (
  id bigint(20) unsigned NOT NULL auto_increment,
  from_userid varchar(50) NOT NULL default '0',
  to_userid varchar(50) NOT NULL default '0',
  text text NOT NULL,
  systemtext text NOT NULL,
  timestamp datetime NOT NULL default '0000-00-00 00:00:00',
  chatroom bigint(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY from_userid (from_userid),
  FULLTEXT KEY from_userid_2 (from_userid),
  KEY chatroom (chatroom),
  KEY timestamp (timestamp)
) ENGINE=MyISAM;

Answer 3

Only MyISAM allows for FULLTEXT, as seen here.

Try this:

CREATE TABLE gamemech_chat (
  id bigint(20) unsigned NOT NULL auto_increment,
  from_userid varchar(50) NOT NULL default '0',
  to_userid varchar(50) NOT NULL default '0',
  text text NOT NULL,
  systemtext text NOT NULL,
  timestamp datetime NOT NULL default '0000-00-00 00:00:00',
  chatroom bigint(20) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY from_userid (from_userid),
  FULLTEXT KEY from_userid_2 (from_userid),
  KEY chatroom (chatroom),
  KEY timestamp (timestamp)
) ENGINE=MyISAM;