MySQL Fulltext Search Error Only On Some Queries

Question

when I use fulltext search in my MySQL 5.6 MyISAM table I sometimes get an error but most of the time it works fine.

This query works fine

SELECT title, MATCH(title, keywords, description) AGAINST ('background+water+sea') AS 'relevance' FROM `content` WHERE 
MATCH(title, keywords, description) AGAINST ('background+water+sea');

But this one doesn't

SELECT title, MATCH(title, keywords, description) AGAINST ('background+water+sea+blue') AS 'relevance' FROM `content` WHERE 
MATCH(title, keywords, description) AGAINST ('background+water+sea+blue');

They are basically the same only the second also asks for "blue". There are over 100,000 lines in the table. It gives me "#2014 - Commands out of sync; you can't run this command now" in phpMyAdmin and "Got error -1 from storage engine" if run on a website.

Any ideas?

The problem occurs only when you run from website? If you run the script on phpmyadmin or whatever you are using, do you get the same error? — Diéfani Favareto Piovezan, Oct 10 '17 at 18:43
If I run it in phpMyAdmin I get "#2014 - Commands out of sync; you can't run this command now" error message. If I run it on a website I get "Got error -1 from storage engine" error message — Peter K, Oct 10 '17 at 18:53
Seems to match this bug in phpMyAdmin: https://sourceforge.net/p/phpmyadmin/bugs/3475/ It is reported fixed in PMA 3.5. What version are you using? — Bill Karwin, Oct 10 '17 at 19:00
I've never heard of the "Got error -1" issue coming from MyISAM. It's always associated with InnoDB. Can you use `SHOW CREATE TABLE content;` on your website and confirm which ENGINE this table uses? Also run `SELECT @@global.innodb_force_recovery;` and confirm it is zero (0). — Bill Karwin, Oct 10 '17 at 19:02
This is the result: CREATE TABLE `content` ( `ID` mediumint(8) unsigned NOT NULL, `title` varchar(40) DEFAULT NULL, `keywords` varchar(255) DEFAULT NULL, `description` varchar(255) DEFAULT NULL, PRIMARY KEY (`ID`), FULLTEXT KEY `IDX_srch` (`title`,`keywords`,`description`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 — Peter K, Oct 10 '17 at 19:07
I'm not completely sure if I understood. In phpMyAdmin I use this command in SQL tab. — Peter K, Oct 10 '17 at 19:26
Ond more question, have you tried a text different from both cases mentioned in your question? — Diéfani Favareto Piovezan, Oct 10 '17 at 19:34
Yes, I tried many. and I'm really desperate because I simply cannot figure it out. Here are some examples: background+water+ocean+blue ERROR, background+water+sea+red ERROR, background+water+sea+green ERROR, red+water+sea+blue OK, red+water+sea+food OK, background+water+sea+food ERROR, yellow+water+sea+food OK, background+red+sea+food ERROR, background+red+ocean OK, background+sea+ocean OK, background+sea+ocean+blue OK — Peter K, Oct 10 '17 at 19:54

score 0 · Answer 1 · answered Oct 10 '17 at 18:53

From MySQL 5.6 manual:

If you get Commands out of sync; you can't run this command now in your client code, you are calling client functions in the wrong order.

This can happen, for example, if you are using mysql_use_result() and try to execute a new query before you have called mysql_free_result(). It can also happen if you try to execute two queries that return data without calling mysql_use_result() or mysql_store_result() in between.

THis error occurs in PHPMyAdmin sometimes if you execute two or more statements at a time. Check the question with a similar problem.

Unfortunately, that's all I can suggest without knowing about the code that calls all of this. If it will not solve the problem, you can share more details

This is the whole command, nothing else. SELECT title, MATCH(title, keywords, description) AGAINST ('background+water+sea+blue') AS 'relevance' FROM `content` WHERE MATCH(title, keywords, description) AGAINST ('background+water+sea+blue'); If I delete the word "blue" it's executed successfully. — Peter K, Oct 10 '17 at 18:57

MySQL Fulltext Search Error Only On Some Queries

1 Answers1