Search code examples
mariadbglobal-variablesmariadb-10.4

Unable to set global variable 'innodb_ft_server_stopword_table' in MariaDB

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?

Solution

  • 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.