Unable to set global variable 'innodb_ft_server_stopword_table' in MariaDB

Question

I am unable to set the global variable 'innodb_ft_server_stopword_table' in MariaDB.

MariaDB version 10.4.13 Windows 10 operating system.

I have created a table called 'mystopwordtable' with one column called VALUE, defined as a varchar(30) collation latin1_swedish_ci.

When I run the command: SET GLOBAL innodb_ft_server_stopword_table = "mydatabase/mystopwordtable"

I get the following error: SQL Error (1231): Variable 'innodb_ft_server_stopword_table' can't be set to the value

I have run the command from the database manager (have tried both HeidiSQL and PhpMyAdmin). I have also run the command in MySQL Shell.

I run the query logged in as user 'root' having full privileges.

I have also tried to set the - similar sounding - variable 'innodb_ft_user_stopword_table'.

After several attempts and hours of research I have been unable to find a way to set it. How can I set the variable?

Answer 1

I tested the latest 10.4.29 (not yet released) however I suspect nothing has changed here for a while.

Due to an abundance of strictness, I also failed with the column was called VALUE. The lower case value is required.

I tested correctly with:

CREATE TABLE `mystopwordtable` (
  `value` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

What was obvious in testing is that the error log of the server will contain a quite detailed error message on the exact error (more than the user SQL error) like:

2023-03-28 15:38:17 8 [ERROR] InnoDB: Invalid column name for stopword table mydatabase/mystopwordtable. Its first column must be named as 'value'.

ref: code to checks performed.