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).
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.