CODEWITHSUNDEEP
mysql
Chappers1975
Chappers1975

Reputation: 123

MySQL InnoDB Full text search containing email address

I have mysql community 5.6.13 on 2 mac laptops - one with os x 10.8 and another with os x 10.9.

As far as I can tell, the installations of mysql are the same but the same full text search behaves differently on each installation.

The query I have is: 

SELECT legal_matter.* FROM legal_matter 
left join user_account 
on user_account.id = legal_matter.lawyer_id 
left join client_account 
on client_account.id = legal_matter.client_account_id 
WHERE MATCH (legal_matter.question) AGAINST ('[email protected]' IN BOOLEAN MODE) 
OR user_account.username like '%[email protected]%' 
OR legal_matter.display_name like '%[email protected]%' 
OR client_account.company_name like '%[email protected]%'

On the laptop with 10.8, the query executes normally, on the laptop with 10.9, the query complains: 

Error Code: 1064. syntax error, unexpected '@', expecting $end

TI have no idea if it has anything to do with the different OS versions, I suspect not but am at a loss as to what the issue is.

Any pointers gratefully received.

Thanks.

Upvotes: 11

Views: 2962

Answers (5)

thekinggpin
thekinggpin

Reputation: 83

For emails like [email protected] as @ is a reserved operator for FullText Search. Instead of the NATURAL LANGUAGE mode, you should try to stick to the BOOLEAN MODE as it supports wildcards(*) which infact is more "searchy".

For searching the [email protected]:

SELECT *, 
       MATCH(email) AGAINST ('+reymysterio* +619* +gmail* +com*' IN BOOLEAN MODE) AS relevance,
       email LIKE '[email protected]%' AS exact_match_relevance
FROM wrestlers
WHERE MATCH(email) AGAINST ('+reymysterio* +619* +gmail* +com*' IN BOOLEAN MODE) 
ORDER BY exact_match_relevance DESC, relevance DESC;

See the entire answer here, including additional config changes: https://stackoverflow.com/a/78374147/19456215

Upvotes: 0

Rick James
Rick James

Reputation: 142366

What is the context? mysql commandline tool? shell script? Java? PHP? Other? I suspect that the answer to this will lead to what changed.

Quite possibly preceding @ by \ will fix it. And that fix may work in both OSs.

Do you know if the complaint is about the @ in the AGAINST? Or all of the @? Try replacing the @ in AGAINST with a space.

Then, since 'lawyer' and 'domain' may not be next to each other, it may be necessary to say

MATCH(question) AGAINST('+lawyer +domain +com +au' IN BOOLEAN MODE)
  AND question LIKE     '%[email protected]%'`

FT gives you the performance; LIKE double checks that result.

Caveat: Unless you have the min token size set to 2, +au will cause lead to zero hits. One workaround is to not use the + on any 'word' shorter than the min token size. (The LIKE covers you.)

Upvotes: 1

Erubiel
Erubiel

Reputation: 2972

The @ symbol is an operator and conflicts with the BOOLEAN SEARCH... it shouldn't... its a bug.

This post, points that is more likely to be a problem of InnoDB and MyISAM...

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

Maybe, you had MyISAM on one machine's DB... dunno.

Upvotes: 1

seven
seven

Reputation: 2607

I had queries that used match against email which started failing when I switched to innodb since @ is used to search for words a certain distance apart in InnoDB:

SELECT username FROM users WHERE MATCH(user_email) AGAINST('[email protected]' IN BOOLEAN MODE);
ERROR 1064 (42000): syntax error, unexpected '@', expecting $end

SELECT username FROM users WHERE MATCH(user_email) AGAINST("[email protected]" IN BOOLEAN MODE);
ERROR 1064 (42000): syntax error, unexpected '@', expecting $end
mysql>

Try wrapping your email address like this:

SELECT username FROM users WHERE MATCH(user_email) AGAINST('"[email protected]"' IN BOOLEAN MODE);

or escaped:

SELECT username FROM users WHERE MATCH(user_email) AGAINST('\"[email protected]\"' IN BOOLEAN MODE);

Upvotes: 7

Up_One
Up_One

Reputation: 5271

Try to use this syntax :

SELECT legal_matter.* FROM legal_matter 
left join user_account 
on user_account.id = legal_matter.lawyer_id 
left join client_account 
on client_account.id = legal_matter.client_account_id 
WHERE MATCH (legal_matter.question) AGAINST ('[email protected]' IN BOOLEAN MODE) 
OR user_account.username like '%[email protected]%' 
OR legal_matter.display_name like '%[email protected]%' 
OR client_account.company_name like '%[email protected]%'

Upvotes: -1

Related Questions