Dealing with MySQL ERROR 1214 (HY000): The used table type doesn’t support FULLTEXT indexes

If you’re ever unfortunate to run into the same MySQL error that I’ve just run into, don’t stress. There is an easy solution to resolve this annoying error message.

Ther error I was getting:

In my case, I was trying to import a .sql dump and the error I got looked  like this:

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

The reason you’re getting this error:

In a nutshell: InnoDB doesn’t support FULLTEXT indexes prior to MySQL version 5.6. You have to use the MyISAM database engine for FULLTEXT indexes for older versions of MySQL/MariaDB.

What was going on for me:

In my case, the .sql dump was coming from AWS, that uses a newer version of MySQL/MariaDB and I was trying to import the .sql file into my MariaDB database that was running an older version. I was on a machine that was using MariaDB 5.5 (MySQL 5.5 equivalent). I needed a way to update the InnoDB engine entry to MyISAM as you have to use the MyISAM database engine for FULLTEXT indexes.

How to fix it:

Linux method: If you’re fortunate enough to be using Linux, then this command will work for you; (make a copy of the file first, just in case anything goes wrong)

sed -i 's/) ENGINE=InnoDB/) ENGINE=MyISAM/g' your_db_dump_180731.sql

On Linux systems, sed is a Unix utility that parses and transforms text.

Windows method: If you’re using Windows, you’ll have to resort to a find and replace method.  This is relatively easy to do; open your .sql file up in something like Notepad++ or Sublime and search for ENGINE=InnoDB and replace it with ENGINE=MyISAM (remember to save).

That’s it!

Hopefully, the above solution will work for you as it did for me. Importing your .sql dump should be a simple straightforward process and you should not run into any more import errors.