Can't find FULLTEXT index matching the column list

Magnus_Lind

The issue

Long time user with an old issue, it seems that search appears to be broken in my installation. The API returns error 500 on discussion searches, however the user search function works. Here's the error in my log:

flarum.ERROR: PDOException: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list

I did some research on the issue, my forum was installed many years ago and have been upgraded successfully over time. I do understand that I'm missing some index based on this issue, but in all the topics on this issue I can't find any that explains how this index is supposed to look like. I tried adding a fulltext index for the content field in posts table:

ALTER TABLE posts ADD FULLTEXT INDEX `ft_content` (content);

But it seems to have no effect. The current indexes in my post table are:

PRIMARY id
UNIQUE discussion_id, number
INDEX edited_user_id
INDEX hidden_user_id
INDEX discussion_id, number
INDEX discussion_id, created_at
INDEX user_id, created_at
INDEX type
INDEX type, created_at

Any ideas how I can create this missing index, or how it is supposed to look when it works?

Flarum information

Flarum core: 1.8.5
PHP version: 8.3.6
MySQL version: 10.11.6-MariaDB-0+deb12u1
Loaded extensions: Core, date, libxml, openssl, pcre, zlib, filter, hash, json, pcntl, random, Reflection, SPL, session, standard, sodium, mysqlnd, PDO, xml, apcu, calendar, ctype, curl, dom, mbstring, FFI, fileinfo, ftp, gd, gettext, iconv, igbinary, imap, intl, exif, mysqli, pdo_mysql, pdo_pgsql, pgsql, Phar, posix, readline, redis, shmop, SimpleXML, sockets, sysvmsg, sysvsem, sysvshm, tokenizer, xmlreader, xmlwriter, xsl, yaml, zip, Zend OPcache

luceos

Magnus_Lind Long time user

😍

Magnus_Lind But it seems to have no effect.

What is the output of the command? And can you confirm this is actually the right table and database (yes I have to ask).

And please post the output of this sql query:

show index from posts;

Magnus_Lind

errors: [{status: "500", code: "unknown"}]

And in the UI, the usual red banner "Oops! Something went wrong. Please reload the page and try again." Pretty sure it's not a code error, everything else works just fine.

I'm 100% certain it's the correct database, less sure about the table tho, do search operate on post or discussions or maybe both? Here are the indexes for posts table:

flarum_posts	0	PRIMARY	1	id	A	1989	NULL	NULL		BTREE			NO
flarum_posts	0	posts_discussion_id_number_unique	1	discussion_id	A	994	NULL	NULL		BTREE			NO
flarum_posts	0	posts_discussion_id_number_unique	2	number	A	1989	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_edited_user_id_foreign	1	edited_user_id	A	64	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_hidden_user_id_foreign	1	hidden_user_id	A	8	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_discussion_id_number_index	1	discussion_id	A	994	NULL	NULL		BTREE			NO
flarum_posts	1	flarum_posts_discussion_id_number_index	2	number	A	1989	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_discussion_id_created_at_index	1	discussion_id	A	994	NULL	NULL		BTREE			NO
flarum_posts	1	flarum_posts_discussion_id_created_at_index	2	created_at	A	1989	NULL	NULL		BTREE			NO
flarum_posts	1	flarum_posts_user_id_created_at_index	1	user_id	A	198	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_user_id_created_at_index	2	created_at	A	1989	NULL	NULL		BTREE			NO
flarum_posts	1	flarum_posts_type_index	1	type	A	10	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_type_created_at_index	1	type	A	10	NULL	NULL	YES	BTREE			NO
flarum_posts	1	flarum_posts_type_created_at_index	2	created_at	A	1989	NULL	NULL		BTREE			NO

Should be relatively close to original, I have a few extensions so maybe that's interfering. Done a few migrations too of both web server and database, perhaps something got lost on the way. Love the project BTW, Flarum is amazing. 👌

luceos

Magnus_Lind try manually running the query from your first post on the right table, flarum_posts.

clarkwinkelmann

Assuming you have no missing columns, something you could do is create a data-only dump of your database, create a fresh copy of Flarum with the same extensions, delete all data from the new database then import the data-only dump from the old database into the new database. This way you would be certain to have all correct indexes, foreign keys, column types, etc.

Of course if you do that, also make sure you have a full backup of the database and files in case something goes wrong. You could even use the full backup to perform this operation on a different server while leaving the main server in maintenance mode, then copy the new database to the original server.

If it's just one index missing though, it should definitely be quicker to fix manually in the existing database.

Magnus_Lind

clarkwinkelmann Might be a good solution, if nothing else works. Tho if anyone with a working search could post their index for flarum_posts/posts table so I can compare, I'd appreciate that by a lot.

luceos Now this is interesting, it didn't solve the problem, but I'm getting a different and probably more useful error:

Next Illuminate\Database\QueryException: SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list (
SQL: 
select `flarum_discussions`.*, `flarum_posts_ft`.`most_relevant_post_id` 
from `flarum_discussions` inner join ((select `flarum_posts`.`discussion_id`, SUM(MATCH(`flarum_posts`.`content`) AGAINST (test)) as score, SUBSTRING_INDEX(GROUP_CONCAT(`flarum_posts`.`id` ORDER BY MATCH(`flarum_posts`.`content`) AGAINST (test) DESC, `flarum_posts`.`number`), ',', 1) as most_relevant_post_id from `flarum_posts` where exists (select 1 from `flarum_discussions` where `flarum_discussions`.`id` = `flarum_posts`.`discussion_id` and (`flarum_discussions`.`id` not in (select `discussion_id` from `flarum_discussion_tag` where `tag_id` not in (select `flarum_tags`.`id` from `flarum_tags` where (`flarum_tags`.`id` in (select `flarum_perm_tags`.`id` from `flarum_tags` as `flarum_perm_tags`) and (`flarum_tags`.`parent_id` in (select `flarum_perm_tags`.`id` from `flarum_tags` as `flarum_perm_tags`) or `flarum_tags`.`parent_id` is null))))) and (`flarum_discussions`.`is_private` = 0 or (((`flarum_discussions`.`is_approved` = 0))))) and (`flarum_posts`.`is_private` = 0 or (((`flarum_posts`.`is_approved` = 0)))) and `flarum_posts`.`type` = comment and MATCH(`flarum_posts`.`content`) AGAINST (test IN BOOLEAN MODE) and `type` in (discussionTagged, discussionStickied, discussionLocked, comment, discussionRenamed) group by `flarum_posts`.`discussion_id`) union (select `id`, NULL as score, first_post_id as most_relevant_post_id from `flarum_discussions` where MATCH(`flarum_discussions`.`title`) AGAINST (test IN BOOLEAN MODE))) `flarum_posts_ft` on `flarum_posts_ft`.`discussion_id` = `flarum_discussions`.`id` where (`flarum_discussions`.`id` not in (select `discussion_id` from `flarum_discussion_tag` where `tag_id` not in (select `flarum_tags`.`id` from `flarum_tags` where (`flarum_tags`.`id` in (select `flarum_perm_tags`.`id` from `flarum_tags` as `flarum_perm_tags`) and (`flarum_tags`.`parent_id` in (select `flarum_perm_tags`.`id` from `flarum_tags` as `flarum_perm_tags`) or `flarum_tags`.`parent_id` is null))))) and (`flarum_discussions`.`is_private` = 0 or (((`flarum_discussions`.`is_approved` = 0)))) group by `flarum_discussions`.`id` order by MATCH(`flarum_discussions`.`title`) AGAINST (test) desc, `flarum_posts_ft`.`score` desc limit 21
) in /.../vendor/illuminate/database/Connection.php:712

Search term: "test". I'm also using "flarum_" as table prefix.

Although I'm still scratching my head, not sure what to make out of this.

I'll have to do some more research.

Magnus_Lind

Good news! I did end up making a brand new installation on a identical server. The installation process has really become convenient and simple over the years, I really appreciate that. 👍️

Anyway, after the new installation had generated a default database I found a match on flarum posts. A FULLTEXT index for content now exist on both forums.

But... (and this make me feel stupid), I went on to check flarum_discussions too, because obviously when we search we do search for titles too, not just posts. And there was the problem, new installation has a FULLINDEX for the title column, but not my production forum. Added that index and now search works again. 😃

Basically, if anyone else runs an older upgraded forum and has the same issue. These SQL commands will add the missing indexes and should make the search function work again:

ALTER TABLE flarum_posts ADD FULLTEXT INDEX `content` (content);
ALTER TABLE flarum_discussions ADD FULLTEXT INDEX `title` (title);