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

User · Answer

Resolved -  1214 - The used table type doesn t support FULLTEXT indexes                 Its Very Simple to resolve this issue  People are answering here in very difficult words which are not easily understandable by the people who are not technical   So i am mentioning here steps in very simple words will resolve your issue   1   Open your  sql file with Notepad by right clicking on file Edit Or Simply open a Notepad file and drag and drop the file on Notepad and the file will be opened   Note  Please don t change the extention  sql of file as its still your sql database  Also to keep a copy of your sql file to save yourself from any mishappening   2   Click on Notepad Menu Edit   Replace  A Window will be pop us with Find What  amp  Replace With Fields   3   In Find What Field Enter ENGINE InnoDB  amp  In Replace With Field Enter ENGINE MyISAM  4   Now Click on Replace All Button  5   Click CTRL S or File Save  6   Now Upload This File and I am Sure your issue will be resolved

User · Answer

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

User · Answer

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

User · Answer

Simply do the following    Open your  sql file with Notepad or Notepad    Find InnoDB  and  Replace  all  around 87  with MyISAM Save and now you can import your database with out error

User · Answer

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

User · Answer

From official reference     Full-text indexes can be used only with MyISAM tables   In MySQL 5 6 and up  they can also be used with InnoDB tables   Full-text indexes can be created only for CHAR  VARCHAR  or TEXT columns    https   dev mysql com doc refman 5 5 en fulltext-search html  InnoDB with MySQL 5 5 does not support Full-text indexes

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

Examples related to mysql