Fixing “InnoDB presently supports one FULLTEXT index creation at a time” Error

This week I was trying to back up a database with a MyISAM default storage engine from HostGator’s cPanel and restore it on a new server using MariaDB with an InnoDB default storage engine. Apparently, I could not do this because HostGator was using an outdated version of MySQL and when the time came to restore the SQL file on the new server, I ran into the error below :

ERROR 1795 (HY000) at line 4615: InnoDB presently supports one FULLTEXT index creation at a time

Three of the tables from the database had FULLTEXT indexes and in order to proceed, these needed to be removed. This guide shows you how to do just that. Some minor SQL knowledge might be needed.

How to find all “FULLTEXT index” from tables in a database without searching each table individually

To mass find them, run the following SQL query :

SELECT DISTINCT
    TABLE_NAME,
    INDEX_NAME
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_schema';

To run an SQL query in phpMyAdmin, go to the database and select the SQL tab. There should be an input box where you can enter text. Copy/paste the SQL query in the box and press the “Go” button to execute it.

Important: It is always recommended to make a backup of your database before running SQL queries.

The same can be done to show FULLTEXT index individually from each table using the following SQL query :

SHOW INDEX FROM table_name;

After finding which tables they are in, you now need to drop them.

How to remove “FULLTEXT index” from tables in a MySQL database

From the screenshot above, there are 3-4 tables with a “FULLTEXT” index type and values for the key name. We need to use the SQL query below to drop the INDEX from each of these tables. Replace “table_name” with the actual table name and “key_name” with the actual key name.

ALTER TABLE table_name DROP INDEX key_name;

So if we were to use the above screenshot as an example, you would need to run the three SQL queries below :

ALTER TABLE wp_wpforo_posts DROP INDEX title;
ALTER TABLE wp_wpforo_posts DROP INDEX body;
ALTER TABLE wp_wpforo_posts DROP INDEX title_plus_body;

After executing the above, make a backup of your database via phpMyAdmin or an alternative method and import it to the new server. It should have been imported properly without any errors this time.