| Bug #76423 | Double Quotes and Full-Text Search -- Memory Exhaustion | ||
|---|---|---|---|
| Submitted: | 21 Mar 2015 5:46 | Modified: | 27 Dec 2017 20:05 |
| Reporter: | techupin techu | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: FULLTEXT search | Severity: | S1 (Critical) |
| Version: | 5.6.19 | OS: | Linux (Ubuntu) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | fulltext, innodb, MySQL | ||
[21 Mar 2015 7:48]
MySQL Verification Team
Hi, Please show us the table structure from SHOW CREATE TABLE, and the exact query. Thanks!
[21 Mar 2015 8:09]
techupin techu
CREATE TABLE `tablename` (
`field1` int(11) NOT NULL AUTO_INCREMENT,
`field2` varchar(1) NOT NULL DEFAULT '',
`field3` varchar(80) NOT NULL DEFAULT '',
`field4` date NOT NULL DEFAULT '0000-00-00',
`field5` varchar(80) NOT NULL DEFAULT '',
`field6` varchar(500) CHARACTER SET latin1 NOT NULL DEFAULT '',
`field7` int(11) NOT NULL DEFAULT '0',
`field8` double NOT NULL DEFAULT '0',
`field9` varchar(20) NOT NULL DEFAULT '',
`field10` double NOT NULL DEFAULT '0',
`field11` double NOT NULL DEFAULT '0',
`field12` varchar(80) NOT NULL DEFAULT '',
PRIMARY KEY (`field1`),
KEY `idx_field4` (`field4`),
KEY `idx_field7` (`field7`),
FULLTEXT KEY `idx_field6` (`field6`),
FULLTEXT KEY `idx_field5` (`field5`),
FULLTEXT KEY `idx_field12` (`field12`)
) ENGINE=InnoDB AUTO_INCREMENT=6593938 DEFAULT CHARSET=utf8
Query:
If I do:
select * from tablename where match(field12) against ('food chain') limit 10;
I get: 10 records where field12 contains either food or chain, limited by 10 records
If I do:
select * from tablename where match(field12) against ('+food +chain') limit 10;
I get: 10 records where field12 contains both food and chain, limited by 10 records
If I do:
select * from tablename where match(field12) against ('"food chain"') limit 10;
(to match the exact word ``food chain'')
I get: ERROR 2013 (HY000): Lost connection to MySQL server during query
similary if I have against('"chain"') I get the above error, while it goes through for against('chain');
query:
select count(*) from tablename where match(field12) against ('chain');
I get the result:
+----------+
| count(*) |
+----------+
| 2497550 |
+----------+
1 row in set (2.71 sec)
while when I do:
select count(*) from tablename where match(field12) against ('"chain"');
I get:
ERROR 2013 (HY000): Lost connection to MySQL server during query
[27 Nov 2017 20:05]
MySQL Verification Team
Thank you for the feedback. Please try with a major release version 5.7 and if the issue continues, provide a repeatable test case. Thanks.
[28 Dec 2017 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: Hello, I have a table with approximately 4 million rows. The DB Engine is InnoDB. One of the columns is a Full-Text Index. when I do a match(X) against ('Y')... I get the results. when I do a match(X) against ('"Y"')... the memory gets exhausted and I get an error ``ERROR 2013 (HY000): Lost connection to MySQL server during query'' the reason for using double quotes in my against phrase is that I actually have a do a match against two consecutive words, .e.g., against('"food chain"'). I tried fiddling with the max_allowed_packet with different values in the increasing order, but no luck. Any help greatly appreciated. Regards, tu. --- mysql> show variables like 'innodb_ft%'; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | +---------------------------------+------------+ 12 rows in set (0.01 sec) show variables like '%version%'; +-------------------------+-------------------------+ | Variable_name | Value | +-------------------------+-------------------------+ | innodb_version | 5.6.19 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.19-0ubuntu0.14.04.1 | | version_comment | (Ubuntu) | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | +-------------------------+-------------------------+ 7 rows in set (0.00 sec) How to repeat: Hello, I have a table with approximately 4 million rows. The DB Engine is InnoDB. One of the columns is a Full-Text Index. when I do a match(X) against ('Y')... I get the results. when I do a match(X) against ('"Y"')... the memory gets exhausted and I get an error ``ERROR 2013 (HY000): Lost connection to MySQL server during query'' the reason for using double quotes in my against phrase is that I actually have a do a match against two consecutive words, .e.g., against('"food chain"'). I tried fiddling with the max_allowed_packet with different values in the increasing order, but no luck. Any help greatly appreciated. Regards, tu. --- mysql> show variables like 'innodb_ft%'; +---------------------------------+------------+ | Variable_name | Value | +---------------------------------+------------+ | innodb_ft_aux_table | | | innodb_ft_cache_size | 8000000 | | innodb_ft_enable_diag_print | OFF | | innodb_ft_enable_stopword | ON | | innodb_ft_max_token_size | 84 | | innodb_ft_min_token_size | 3 | | innodb_ft_num_word_optimize | 2000 | | innodb_ft_result_cache_limit | 2000000000 | | innodb_ft_server_stopword_table | | | innodb_ft_sort_pll_degree | 2 | | innodb_ft_total_cache_size | 640000000 | | innodb_ft_user_stopword_table | | +---------------------------------+------------+ 12 rows in set (0.01 sec) show variables like '%version%'; +-------------------------+-------------------------+ | Variable_name | Value | +-------------------------+-------------------------+ | innodb_version | 5.6.19 | | protocol_version | 10 | | slave_type_conversions | | | version | 5.6.19-0ubuntu0.14.04.1 | | version_comment | (Ubuntu) | | version_compile_machine | x86_64 | | version_compile_os | debian-linux-gnu | +-------------------------+-------------------------+ 7 rows in set (0.00 sec)