| Bug #72605 | Syntax error fulltext search InnoDB tables 5.6.16 | ||
|---|---|---|---|
| Submitted: | 10 May 2014 7:23 | Modified: | 3 Jun 2014 16:50 |
| Reporter: | Kym Farnik | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S3 (Non-critical) |
| Version: | 5.6.16, 5.6.19 | OS: | Linux (Centos 5.10) |
| Assigned to: | Daniel Price | CPU Architecture: | Any |
| Tags: | fulltext innodb | ||
[10 May 2014 7:33]
Kym Farnik
Changed category to FULLTEXT search
[11 May 2014 0:14]
MySQL Verification Team
Thank you for the bug report. Please provide the complete test case create table, insert data etc. Thanks.
[11 May 2014 1:08]
Kym Farnik
-- full example of bug
CREATE TABLE atest_table (
imageid int(10) unsigned NOT NULL AUTO_INCREMENT,
title varchar(300) CHARACTER SET utf8 NOT NULL,
text mediumtext CHARACTER SET utf8,
PRIMARY KEY (imageid),
FULLTEXT KEY search_index (title,text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
insert into atest_table (title,text)
values
('Test 1','Test 1 description'),
('Jewel','Crystal Jewellery'),
('Line 3','Line 1 description');
SELECT
COUNT(imageid) AS imagecount
FROM atest_table
WHERE
MATCH
(title, text)
AGAINST
('+jewel* +* +crystal* ' IN BOOLEAN MODE);
-- MySQL said:
-- #1064 - syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'
ALTER TABLE `atest_table` ENGINE = MyISAM;
SELECT
COUNT(imageid) AS imagecount
FROM atest_table
WHERE
MATCH
(title, text)
AGAINST
('+jewel* +* +crystal* ' IN BOOLEAN MODE);
-- Result:
-- imagecount
-- 1
[11 May 2014 5:49]
MySQL Verification Team
drop table if exists t1;
create table t1(a text,fulltext index a(a))engine=innodb;
select match(a) against ('+* +a* ' in boolean mode) from t1;
[12 May 2014 9:55]
MySQL Verification Team
Hello Kym, Thank you for the report. Verified as described. Thanks, Umesh
[12 May 2014 9:56]
MySQL Verification Team
Thank you Shane for the simplified test case.
mysql> drop table if exists t1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> create table t1(a text,fulltext index a(a))engine=innodb;
Query OK, 0 rows affected (0.12 sec)
mysql> select match(a) against ('+* +a* ' in boolean mode) from t1;
ERROR 1064 (42000): syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*'
mysql>
mysql>
// With MyISAM
mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)
mysql> create table t1(a text,fulltext index a(a))engine=myisam;
Query OK, 0 rows affected (0.01 sec)
mysql> select match(a) against ('+* +a* ' in boolean mode) from t1;
Empty set (0.00 sec)
mysql> select version();
+-------------------------------------------+
| version() |
+-------------------------------------------+
| 5.6.19-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)
[13 May 2014 10:09]
Kym Farnik
Thanks guys. This is my first bug report. I'm not a DBA, but am the tech admin for a forum http://www.ausphotography.net.au I have general development skills Since MySQL 5.6 we've changed most of our tables to InnoDB which has been a positive move in terms of stability; i.e. MyISAM tables would have errors every few weeks. This error is localised to the photo Gallery addon search function. Which being a photography forum is rather important :)
[20 May 2014 2:42]
Shaohua Wang
The real problem is "AGAINST ('+*' IN BOOLEAN MODE)". If we remove '+*' from
the fts query string, we will get correct result.
Hi Kym, why do you need '+*' in fts query? it's meaningless and useless.
[21 May 2014 3:46]
Kym Farnik
The code in question is generated by a plugin to the vBulletin forum system. I agree the +* is stupid, but is not in my hands. The code works for MyISAM tables and not for InnoDB - that is still an issue.
[23 May 2014 6:50]
Shaohua Wang
Hi Kym, we decided to document syntax differences, and wouldn't fix the bug(IMO, it's not a bug, but a real compatible issue). Sorry for the inconvenience. I'd strongly recommend you to file a bug to "vBulletin". It could be a simple fix, I guess. Hope you can go around this soon. Thanks again for the bug report.
[24 May 2014 9:07]
Kym Farnik
Thanks, I've reported the issue to the vBulletin 3rd Party addon developer and fixed the code myself for our site. (the "+*) is not generated. As said, there is a difference between MyISAM and InnoDB when ideally they should be the same
[3 Jun 2014 16:50]
Daniel Price
Invalid full-text search queries for InnoDB ('+*' , '+-', '+-good') are now documented here:
http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html
http://dev.mysql.com/doc/refman/5.7/en/fulltext-boolean.html
Thank you for the bug report.
Description: This works for MyISAM but not InnoDB tales. MySQL Error : syntax error, unexpected '+', expecting FTS_TERM or FTS_NUMB or '*' Error Number : 1064 SELECT COUNT(imageid) AS imagecount FROM dbtech_gallery_images WHERE instanceid = 1 && approved = 1 && deleted = 0 && MATCH (title, text, tagging, username) AGAINST ('+jewellery* +jewel* +crystal* +fashion* +* +abstract* ' IN BOOLEAN MODE); Ca How to repeat: Use syntax similar to the above against a InnoDB table vs similar MyISAM table.