Bug #100136 InnoDB Fulltext Search: server stopword table doesn't work as expected.

• Submitted: 7 Jul 2020 8:19
• Modified: 7 Jul 2020 10:15
• Reporter: Shaohua Wang (OCA)
• Status: Verified
• Category: MySQL Server: FULLTEXT search
• Severity: S3 (Non-critical)
• Version: 5.7, 5.7.30
• OS: Any
• CPU Architecture: Any
• Tags: Contribution

[7 Jul 2020 8:19] Shaohua Wang

Description:
Set innodb_ft_server_stopword_table doesn't work when innodb_ft_user_stopword_table is set to empty in configuration file.

How to repeat:
Run the following mtr test: mtr --mem stopword2.test --mysqld=--innodb_ft_user_stopword_table=

CREATE TABLE my_stopwords(value VARCHAR(100)) ENGINE = INNODB;

INSERT INTO my_stopwords(value) VALUES ('klay');

CREATE TABLE t1 (
        id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
        name varchar(100)
) ENGINE=InnoDB;

INSERT INTO t1(name) VALUES('Klay Thompson');

SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';

CREATE FULLTEXT INDEX idx ON t1(name);

INSERT INTO t1(name) VALUES('Klay Wang');

SET GLOBAL innodb_ft_aux_table='test/t1';
SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_TABLE;
SELECT word FROM INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE;
SET GLOBAL innodb_ft_aux_table=default;

SET GLOBAL innodb_ft_server_stopword_table=default;

# We should not see any results!!!
select * from t1 where match(name) AGAINST('klay');

select * from t1 where match(name) AGAINST('wang');

drop table my_stopwords;

drop table t1; 

Suggested fix:
Analysis:
------
In function fts_load_stopword(), session_stopword_table is NOT NULL, but it's an empty string. so we still use it as stopword table. fts_load_user_stopword() fails, then it goes to fts_load_default_stopword() to load default stopword.

      } else {
                stopword_to_use = (session_stopword_table)
                        ? session_stopword_table : global_stopword_table;
        }    

        if (stopword_to_use
            && fts_load_user_stopword(table->fts, stopword_to_use,
                                      &cache->stopword_info)) {
                /* Save the stopword table name to the configure
                table */
                if (!reload) {
                        str.f_n_char = 0; 
                        str.f_str = (byte*) stopword_to_use;
                        str.f_len = ut_strlen(stopword_to_use);

                        error = fts_config_set_value(
                                trx, &fts_table, FTS_STOPWORD_TABLE_NAME, &str);
                }    
        } else {
                /* Load system default stopword list */
                fts_load_default_stopword(&cache->stopword_info);
        }    

Fix:
------
if session_stopword_table is an empty string, use global_stopword_table.

[7 Jul 2020 8:32] Shaohua Wang

Run the test case, we will the following error message:
[ERROR] InnoDB: User stopword table  does not exist.

[7 Jul 2020 8:37] Shaohua Wang

Contributed patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bugfix_fts_stopword_table.patch (application/octet-stream, text), 612 bytes.

[7 Jul 2020 9:24] MySQL Verification Team

Hello Shaohua,

Thank you for the report and contribution.

regards,
Umesh

[7 Jul 2020 10:15] Shaohua Wang

Nope, Umesh. BTW,we're TXSQL(Tencent MySQL) team.