MySQL InnoDB Full text search containing email address

Question

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.

Answer 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('[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);