How to change engine of table without making any changes to table

Question

My table is using 'InnoDB' storage engine when i try to do 'full text search' on it gives me an error 'The used table doesn't support full text indexes' That's why i tried to change my table engine from 'InnoDB' to 'MyIsam' but it again given me error 'Cannont delete or update table foreign key contraint fail'. It is linked to other table and i don't want to change both the tables and data in it

when i run

SHOW VARIABLES LIKE "%version%"

innodb_version 1.1.8
protocol_version 10
slave_type_conversions 
version 5.5.24
version_comment MySQL Community Server (GPL)
version_compile_machine x86
version_compile_os Win64

This is my sql version and InnoDb version please help me.

Answer 1

Option 1 : Upgrade to MySQL 5.6

Since you do not want to change the data, your only recourse is to upgrade to MySQL 5.6 because InnoDB supports FULLTEXT indexes in that version.

I have mentioned using InnoDB-specific FULLTEXT options in the DBA StackExchange (Full text search results in a large amount of time spent in 'FULLTEXT initialization')

For full clarification, please read the MySQL Documentation on it

Option 2 : Switch to MyISAM

You need to drop constraints to change the Storage Engine with

ALTER TABLE yourtable ENGINE=MyISAM;

Option 3 : Use MySQL Replication

If you have a Slave, run this

SET foreign_key_checks = 0;
SET unique_checks = 0;
STOP SLAVE;
ALTER TABLE childtable DROp CONSTRAINT ... ;
ALTER TABLE parenttable ENGINE=MyISAM;
ALTER TABLE childtable ENGINE=MyISAM;
ALTER TABLE ADD FULLTEXT ftindexname (columns);
START SLAVE;

That way, the Master stays all InnoDB, and the slave's tables are changed to MyISAM.

It is possible to do this because I wrote a post in the DBA StackExchange entitled Can I have an InnoDB master and MyISAM slaves with Full-Text for searching? on how to do this.

Once you do this, just perform the FULLTEXT searching on the Slave.