I'm in the process of moving to MariaDB on RDS. My tables are up and running and everything looks good, but given that I'm on MariaDB 10.0.24, I'm considering switching a couple of my MyISAM tables over to Innodb. Prior to this, I required using MyISAM because InnoDb had no support for full-text. But, as I understand it, MariaDB > 10.0.5 supports full-text searching on InnoDB.
In my first attempt, I converted the table from MyISAM to InnoDB using:
ALTER TABLE `courses` ENGINE=INNODB
But after that, my FULLTEXT indexes appeared to have been reset. They're still listed there, but don't respond to FULLTEXT searching.
Can't find FULLTEXT index matching the column list
Also, when I attempt to create new FULLTEXT INDEXES on the table, I can no longer create them -- only INDEX and UNIQUE are available.
Finally, while I see settings in my RDS parameter group that let me configure the INNODB FULLTEXT settings, I didn't see any setting that would let me "turn it on", so I'm assuming it's on by default.
Am I stuck with MyISAM or is it possible to change these tables to InnoDB and I just haven't figured out how to do that?
Thanks for your help!
SHOW CREATE TABLE:
CREATE TABLE `courses` ( `id` varchar(32) NOT NULL, `name` varchar(255) DEFAULT NULL, `description` text, `instructor_id` varchar(32) DEFAULT NULL, `catalog_id` varchar(32) NOT NULL, `site_id` varchar(32) NOT NULL, `created_on` datetime NOT NULL, `modified_on` datetime NOT NULL, PRIMARY KEY (`id`), KEY `site_id` (`site_id`), KEY `instructor_id` (`instructor_id`), KEY `catalog_id` (`catalog_id`), FULLTEXT KEY `name_description` (`name`,`description`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SELECT `c`.*, (MATCH (c.name) AGAINST ('+yoga*' IN BOOLEAN MODE)) AS `nameRelevance`, (MATCH (c.description) AGAINST ('+yoga*' IN BOOLEAN MODE)) AS `descriptionRelevance` FROM `courses` AS `c` WHERE (c.catalog_id IN ('xxxxx')) AND (c.site_id = 'yyyyy') AND (MATCH (c.name, c.description) AGAINST ('+yoga*' IN BOOLEAN MODE) OR MATCH (c.name, c.description) AGAINST ('+yoga*' IN BOOLEAN MODE))
Error: Can't find FULLTEXT index matching the column list