MySQL Bugs: #112778: innodb_ft_server_stopword_table can't be set with UPPERCASE 'VALUE'

Bug #112778	innodb_ft_server_stopword_table can't be set with UPPERCASE 'VALUE'
Submitted:	20 Oct 2023 1:05	Modified:	23 Oct 2023 3:00
Reporter:	tianfeng li (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: FULLTEXT search	Severity:	S3 (Non-critical)
Version:	8.0.30	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
As in https://dev.mysql.com/doc/refman/8.0/en/fulltext-stopwords.html ,

The stopword table must be an InnoDB table, containing a single VARCHAR column named value.

However, we do the following attempts:

```
create table my_LOWER_stopwords(value varchar(30)) engine=innodb;
set global innodb_ft_server_stopword_table='test/my_LOWER_stopwords';

create table my_UPPER_stopwords(VALUE varchar(30)) engine=innodb;
set global innodb_ft_server_stopword_table='test/my_UPPER_stopwords';

create table my_MULTI_COl_stopwords(value varchar(30)) engine=innodb;
set global innodb_ft_server_stopword_table='test/my_MULTI_COl_stopwords';

```

I have checked my collation:

```
show variables like '%coll%';
+-------------------------------+--------------------+
| Variable_name                 | Value              |
+-------------------------------+--------------------+
| collation_connection          | utf8mb4_0900_ai_ci |
| collation_database            | utf8mb3_general_ci |
| collation_server              | utf8mb3_general_ci |
| default_collation_for_utf8mb4 | utf8mb4_0900_ai_ci |
+-------------------------------+--------------------+
```

How to repeat:
It seems the column name is case sensitive for a stopword table,
and the description 'a single VARCHAR column' is also confusing for 'my_MULTI_COl_stopwords'.

Hi Mr. li,

Thank you for your but report.

However, we do not understand what are you reporting, since you do not follow the instructions on the page that you have, evidently, read.

Among other things , it says loud and clear that character set and collation must be identical to server's charset and collation.   Next, both server and user stopword tables must have a single column. That is because each row can contain only one word.

You can also set only one table for global stopword table and another one for user stopword table. 

What you have done is set the same system variables three times, which means that only the last one is the valid one.

That is how the system is designed. 

If your server character set and collation do not suite you, you can configure them otherwise.

Not a bug.

We followed the instructions and they worked just fine.

I'm sorry I didn't explain this question clearly.

First attempt works fine (lower 'value'):
```
create table my_LOWER_stopwords(value varchar(30)) engine=innodb;
set global innodb_ft_server_stopword_table='test/my_LOWER_stopwords';
```

Second attemp not works (upper "VALUE"):
```
create table my_UPPER_stopwords(VALUE varchar(30)) engine=innodb;
set global innodb_ft_server_stopword_table='test/my_UPPER_stopwords';
```
Then, an error was reported:
 "Variable 'innodb_ft_server_stopword_table' can't be set to the value of 'test/my_UPPER_stopwords'"

All collation are case insensitive, how come the column 'value' and column 'VALUE' is different?

Hi Mr. li,

We repeated your commands and they worked just fine with the latest 8.0 and 8.1.