Mattermost-server: Fix search for emails in Posts in databases

[mattermod]

When you try to search Posts using an email as a search term you get no results. The underlying problem is that the '@' character is a forbidden character in MySQL and we're replacing it with an empty space in order to avoid that problem.

We have to find a way to fix this either for Postgres and MySQL.

Postgres is not a problem because you can use the @ in your search without a problem. Either to*tsvector('english', Message) @@ to_tsquery('english', '[email protected]') or to_tsvector('english', Message) @@ to*tsquery('english', '\”[email protected]\”')will work.

MySQL is the problem. We should probably just wrap the search term with escaped quotes in order to fix it. With enclosed quotes is working MATCH (Message) AGAINST ('[email protected]"' IN BOOLEAN MODE)but if we remove the quotes MATCH (Message) AGAINST ('[email protected]' IN BOOLEAN MODE)the query result is empty

---

If you're interested please comment here and come join our "Contributors" community channel on our daily build server, where you can discuss questions with community members and the Mattermost core team. For technical advice or questions, please join our "Developers" community channel.

New contributors please see our Developer's Guide.

JIRA: https://mattermost.atlassian.net/browse/MM-24454

[fedealconada]

I'd like to give this a shot

[ethervoid]

@fedealconada are you still working on this issue or we should put back to let another community member work on it?

[fedealconada]

Hey @ethervoid, sorry its taking me so long, I've been busy the last weeks. I've pushed something, would like you to give it a look

[jasonblais]

Opening this ticket back to the community - see here for start of a PR to get started: https://github.com/mattermost/mattermost-server/pull/14481

cc @ethervoid

[Leryan]

i had a quick look on that issue, here are some resources that may be useful:

https://bugs.mysql.com/bug.php?id=74042

following a link in the latest comment: https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-modify-character-set

Specifically:

> The fulltext index operates on words, and (I'm guessing) will not index the @ character as it is not considered a word character. This is the default setting at least [1], 

That default can be changed. https://dev.mysql.com/doc/refman/8.0/en/fulltext-fine-tuning.html#fulltext-modify-characte...
The set of characters that are considered word characters can be changed. And if "@" can't be added, then that's another bug (or wrong design decision).

[ethervoid]

@Leryan thank you very much for doing some research and contribute to this issue.

Sadly, given the heterogeneity of our users, even though they're using the same database, we are trying to avoid any change in the database. We're supporting multiple kinds of versions and also we're supporting MariaDB and even though this change is supported in all the databases we know that making that kind of changes leads to undesired side-effects :(

[Leryan]

@ethervoid hello :) i didn't mention it but yeah, such changes have a lot of potential to create undesirable side effects and tbh i didn't even try to actually change anything in my test database 😅

however it seems that fulltext search cannot be used in order to match "words" containing special chars without doing this kind of shenanigans, this is what i wanted to share ^^

i may continue this topic once i'm done with lru cache, for now it's still open to anyone ^^

[ethervoid]

@Leryan Great! Thank you for sharing, this would pave the way for any future interested contributor in the case is not you :)