MySQL Bugs: #93455: max_execution_time optimizer hint has no effect on boolean full-text search

Bug #93455	max_execution_time optimizer hint has no effect on boolean full-text search
Submitted:	3 Dec 2018 16:46	Modified:	5 Dec 2018 15:03
Reporter:	Miguel K	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.24	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When using MATCH(..) AGAINST ('...' IN BOOLEAN MODE)
the optimizer hint of /*+MAX_EXECUTION_TIME(1) */ does not work.

Optimizer hint reference: 
https://dev.mysql.com/doc/refman/5.7/en/optimizer-hints.html

How to repeat:
This does not halt execution of a long-running query:
SELECT
/*+MAX_EXECUTION_TIME(1) */
*
FROM test.t1
WHERE MATCH(title) AGAINST ('"a a a a a a a a a a a a a a"' IN BOOLEAN MODE)
;

Empty set (1.13 sec)

Remove "IN BOOLEAN MODE" and it works as expected.
SELECT
/*+MAX_EXECUTION_TIME(1) */
*
FROM test.t1
WHERE MATCH(title) AGAINST ('"a a a a a a a a a a a a a a"')
;

/* SQL Error (3024): Query execution was interrupted, maximum statement execution time exceeded */

Suggested fix:
Honor the optimizer hint.

Thank you!

Note that this was seen in MyISAM.

Similar to the tables that I tested in on.  Occurred on both VARCHAR() and TEXT.
CREATE TABLE `t1` (
`title` VARCHAR(100) NOT NULL COLLATE 'utf8_unicode_ci',
FULLTEXT INDEX `title` (`title`)
)
COLLATE='utf8_unicode_ci'
ENGINE=MyISAM
ROW_FORMAT=DYNAMIC
;

Hello Miguel,

Thank you for the report.
I'm not able to reproduce this issue at my end with dummy test case. May I request you to please provide exact reproducible test case? If required please mark it as private after posting here.  If the data you need to attach is more than 3MB, you should create a compressed archive of the data and a README file that describes the data with a filename that includes the bug number (recommended filename: mysql-bug-data-93455.zip) and upload one to sftp.oracle.com. Your Oracle Web account (*deleted*@*deleted*.com) and a client that supports SFTP are required in order to access the SFTP server. More details are in the "Files" section of this bug report.

-- 5.7.24/8.0.13

mysql> drop database if exists test;
use test;
CREATE TABLE articles (
          id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
          title VARCHAR(200),
          body TEXT,
          FULLTEXT (title,body)
        ) ENGINE=MyISAM COLLATE='utf8_unicode_ci' ROW_FORMAT=DYNAMIC;

INSERT INTO articles (title,body) VALUES
        ('MySQL Tutorial','DBMS stands for DataBase ...'),
        ('How To Use MySQL Well','After you went through a ...'),
        ('Optimizing MySQL','In this tutorial we will show ...'),
        ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
        ('MySQL vs. YourSQL','In the following database comparison ...'),
        ('MySQL Security','When configured properly, MySQL ...');
Query OK, 1 row affected (0.05 sec)

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE articles (
    ->           id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    ->           title VARCHAR(200),
    ->           body TEXT,
    ->           FULLTEXT (title,body)
    ->         ) ENGINE=MyISAM COLLATE='utf8_unicode_ci' ROW_FORMAT=DYNAMIC;
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> INSERT INTO articles (title,body) VALUES
    ->         ('MySQL Tutorial','DBMS stands for DataBase ...'),
    ->         ('How To Use MySQL Well','After you went through a ...'),
    ->         ('Optimizing MySQL','In this tutorial we will show ...'),
    ->         ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
    ->         ('MySQL vs. YourSQL','In the following database comparison ...'),
    ->         ('MySQL Security','When configured properly, MySQL ...');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> INSERT INTO articles(`title`,body) select  'MySQL Tutorial','DBMS stands for DataBase ...' from `articles` k1, `articles` k2, `articles` k3, `articles` k4,`articles` k5,`articles` k6, `articles` k7, `articles` k8, `articles` k9,`articles` k0,`articles` ka, `articles` kb, `articles` kc, `articles` kd limit  1000000;
Query OK, 1000000 rows affected (3 min 7.63 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> SELECT /*+MAX_EXECUTION_TIME(1) */ * FROM articles
    ->     WHERE MATCH (title,body)
    ->     AGAINST ('database' IN NATURAL LANGUAGE MODE);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql>
mysql> SELECT /*+MAX_EXECUTION_TIME(1) */ * FROM articles
    ->     WHERE MATCH (title,body)
    ->     AGAINST ('"Database"' IN BOOLEAN MODE);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql>
mysql> SELECT /*+MAX_EXECUTION_TIME(1) */ * FROM articles     WHERE MATCH (title,body)     AGAINST ('+Database' IN BOOLEAN MODE);
ERROR 3024 (HY000): Query execution was interrupted, maximum statement execution time exceeded
mysql>

mysql> show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.24                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1                |
| version                 | 5.7.24                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | linux-glibc2.12              |
+-------------------------+------------------------------+
8 rows in set (0.00 sec)

thanks,
Umesh

I have updated a file to your SFTP site: mysql-bug-data-93455.zip

PLEASE RUN THIS EXACT QUERY:
SELECT
/*+MAX_EXECUTION_TIME(1) */
*
FROM bug93455
WHERE MATCH(words) AGAINST ('"a a a a a a a"' IN BOOLEAN MODE);

In your test case that you did before, you did:
AGAINST ('"Database"' IN BOOLEAN MODE);

I am not sure why you wouldn't do what I did:
AGAINST ('"a a a a a a a a a a a a a a"' IN BOOLEAN MODE)

It seems that the repeated a a a a a... has an effect.

Thank you Miguel, I was able to reproduce only after setting ft_min_word_len to minimum i.e 1 + on a smaller work station (had to double rows). No issues observed in 8.0.13.

regards,
Umesh

Test results - 5.7.24, 8.0.13 etc

Attachment: 93455.results (application/octet-stream, text), 15.24 KiB.

Please remove my email address from the earlier comment!!!!!

Marking initial comment as private, requested web team to remove email details from the comment.