I just upgraded MySQL from 5.5 to 5.6 and changed all the tables in the PHPBB3 database into InnoDB tables. I did this because InnoDB is faster if you have lots of concurrent read / writes and MySQL 5.6 finally support FULLTEXT searching using InnoDB. However, I made a mistake and did the conversion to InnoDB before upgrading to MySQL 5.6 and lost the fulltext indexes from the tables.
So, I got this error:
Can't find FULLTEXT index matching the column list [1191]
I found the query that was throwing the error:
mysql> SELECT SQL_CALC_FOUND_ROWS p.post_id FROM phpbb3_posts p WHERE MATCH (p.post_subject, p.post_text) AGAINST ('+search ' IN BOOLEAN MODE) ORDER BY p.post_time DESC LIMIT 250;
I ran it and got this error:
ERROR 1191 (HY000): Can't find FULLTEXT index matching the column list
To solve the probem is to add the index back in. Note is requires an index across two columns. Run this MySQL command on your database.
create fulltext index post_subject_text on phpbb3_posts (post_subject, post_text);
Hope this helps someone.