2

I have recently upgraded my CPanel server from mySQL 5.5 to mySQL 5.6 as it now supports full-text searches for InnoDB. Now, I'm not so great at mySQL so I was wondering is there anything special I need to keep in mind other than running

ALTER TABLE my_table ENGINE=InnoDB;

Several of my tables have full text indexes. What will happen to them? Will the indexes be automatically converted? Or do I have to re-build them manually afterwards?

  • 2
    When I did this conversion, I did a full dump and reload. Better to be safe than sorry... – Michael Hampton Mar 7 '14 at 23:22
  • ...but surely we all do regular backups anyway and we always verify them before attempting upgrades or schema changes – symcbean Mar 8 '14 at 0:30
1

If you do not change any of the FULLTEXT startup parameters, your conversion from MyISAM to InnoDB will take care of creating the FULLTEXT indexes for InnoDB.

CONVERSION STEPS

If you can connect to MySQL from a command-line interface, run this query

SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics WHERE index_type='FULLTEXT';

This will give you every table that has a FULLTEXT index.

Using that query, run this query

SELECT CONCAT('ALTER TABLE ',B.table_schema,'.',B.table_name,' ENGINE=InnoDB') Cmd
FROM (SELECT DISTINCT table_schema,table_name
FROM information_schema.statistics WHERE index_type='FULLTEXT') A
INNER JOIN information_schema.tables B USING (table_schema,table_name)
WHERE B.engine='MyISAM';

This will show you all the commands to convert MyISAM tables with FULLTEXT indexes into InnoDB.

You can dump the commands to a text file.

SQL="SELECT CONCAT('ALTER TABLE ',B.table_schema,'.',B.table_name,' ENGINE=InnoDB')"
SQL="${SQL} FROM (SELECT DISTINCT table_schema,table_name"
SQL="${SQL} FROM information_schema.statistics"
SQL="${SQL} WHERE index_type='FULLTEXT') A"
SQL="${SQL} INNER JOIN information_schema.tables B"
SQL="${SQL} USING (table_schema,table_name)"
SQL="${SQL} WHERE B.engine='MyISAM'"
mysql -uroot -p -ANe"${SQL}" > ConversionScript.sql
less ConversionScript.sql

This will create the conversion script and let you view it.

If you are satisfied with the content of the scripts, log in to mysql and run this:

mysql> source ConversionScript.sql

Give it a Try !!!

  • Thank you for your detailed reply! :) I ran the first command successfully, but the 2nd gives me a syntax error saying "syntax error near unexpected token". Any ideas? – user2643870 Mar 14 '14 at 14:01
  • I forgot the word CONCAT. I'll fix it, BRB !!! – RolandoMySQLDBA Mar 14 '14 at 14:03
  • OK I just fixed it. Try it now !!! – RolandoMySQLDBA Mar 14 '14 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.