Tuesday, August 28, 2018

MySQL InnoDB Full text search containing email address

By Hường Hana 8:00 AM Leave a Comment

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 ('lawyer@domain.com.au' IN BOOLEAN MODE)  OR user_account.username like '%lawyer@domain.com.au%'  OR legal_matter.display_name like '%lawyer@domain.com.au%'  OR client_account.company_name like '%lawyer@domain.com.au%'

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.

4 Answers

Answers 1

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('test@user.com' IN BOOLEAN MODE); ERROR 1064 (42000): syntax error, unexpected '@', expecting $end  SELECT username FROM users WHERE MATCH(user_email) AGAINST("test@user.com" 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('"test@user.com"' IN BOOLEAN MODE);

or escaped:

SELECT username FROM users WHERE MATCH(user_email) AGAINST('\"test@user.com\"' IN BOOLEAN MODE);

Answers 2

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.

Answers 3

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     '%lawyer@domain.com.au%'`

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.)

Answers 4

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 ('+lawyer@domain.com.au' IN BOOLEAN MODE)  OR user_account.username like '%lawyer@domain.com.au%'  OR legal_matter.display_name like '%lawyer@domain.com.au%'  OR client_account.company_name like '%lawyer@domain.com.au%'
If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment