Bug #8351 Double quote in Boolean Fulltext query causes crash
Submitted: 7 Feb 2005 4:32 Modified: 9 Mar 2005 19:55
Reporter: Wade Maxfield Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.9 OS:Mac OS X (Mac OS X 10.3.7)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[7 Feb 2005 4:32] Wade Maxfield
Description:
Doing a Boolean Fulltext query using a double quote (for a literal string match) causes mysqld to die and respawn.

SELECT COUNT(*) as count FROM table
WHERE match (field1,field2) against ('"this exact search string"' IN BOOLEAN MODE);

The error message in mysql cli:

mysql> SELECT COUNT(*) as count FROM table WHERE match (field1,field2) against ('"this exact search string"' IN BOOLEAN MODE);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: theDatabase

ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

The exact version is Ver 4.1.9-standard for apple-darwin7.7.0 on powerpc (Official MySQL-standard binary)

How to repeat:
SELECT COUNT(*) as count FROM table
WHERE match (field1,field2) against ('"this exact search string"' IN BOOLEAN MODE);

will cause it to die everytime.
[8 Feb 2005 2:26] Jorge del Conde
root# uname -a
Darwin mac.local 7.7.0 Darwin Kernel Version 7.7.0

mysql> CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT (title,body));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) as count FROM articles WHERE match(title,body) against ('"this exact search string"' IN BOOLEAN
    -> MODE);
+-------+
| count |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> select version();
+----------------+
| version()      |
+----------------+
| 4.1.9-standard |
+----------------+
[8 Feb 2005 7:14] Wade Maxfield
Done more testing and it appears to be related to a collation being set to utf8_unicode_ci on a per column basis, and it only dies if any matches are found. If no rows match, it doesn't die.  I use Navicat (OS X), and during a session it has added CHARACTER SET and COLLATE to each of my column definitions, as in the CREATE TABLE below.

mysql> CREATE TABLE articles (id INT UNSIGNED AUTO_INCREMENT NOT NULL
    -> PRIMARY KEY,title VARCHAR(200) character set utf8 collate utf8_unicode_ci,
    -> body TEXT character set utf8 collate utf8_unicode_ci,
    -> FULLTEXT (title,body));
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) as count FROM articles WHERE
	-> MATCH(title,body) AGAINST ('"bob dyl"' IN BOOLEAN MODE);
+-------+
| count |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) as count FROM articles WHERE
    -> MATCH(title,body) AGAINST ('"bob dylan"' IN BOOLEAN MODE);
+-------+
| count |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> insert into `articles` values('1','bob dylan','highway 61'),
    -> ('2','dylan, bob','Best of');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) as count FROM articles WHERE
	-> MATCH(title,body) AGAINST ('"bob dyl"' IN BOOLEAN MODE);
+-------+
| count |
+-------+
|     0 |
+-------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) as count FROM articles WHERE
	-> MATCH(title,body) AGAINST ('"bob dylan"' IN BOOLEAN MODE);
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[8 Feb 2005 16:11] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this bug.
[14 Feb 2005 21:04] Sergei Golubchik
Fixed in 4.1.11
[4 Mar 2005 12:09] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22656
[6 Mar 2005 20:16] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22703
[7 Mar 2005 12:47] Sergey Vojtovich
Fixed in 5.0.3
[9 Mar 2005 19:55] Paul Dubois
Noted change in phrase searching behavior in the
boolean full-text section of the manual.

Noted bugfix in 4.1.11, 5.0.3 changelog.
Noted behavior change in 5.0.3 changelog.