How to change read-only permission to set new value of MySQL server system variable

Question

I am new to MySQL; recently I faced this problem while setting a new value for a system variable.

I tried:

mysql> set global innodb_ft_min_token_size = 6;

but I am getting this error:

ERROR 1238 (HY000): Variable 'innodb_ft_min_token_size' is a read only variable

Is there any way to change the read only permission? I read about InnoDB parameters but could not resolve the problem.

Pearson

Answer 1

The site you linked contains all information needed:

System variables that are true or false can be enabled at server startup by naming them [...]

System variables that take a numeric value can be specified as --var_name=value on the command line or as var_name=value in option files.

Many system variables can be changed at runtime (see Section 5.1.5.2, “Dynamic System Variables”).

The innodb_ft_min_token_size is not a dynamic variable so you will have to change the config file my.cnf and add innodb_ft_min_token_size=6. Alternatively you need to change the startup command of your MySQL server.

After the change you must restart your MySQL server.

Velvetvelveteen

i did these things but still not getting the modified value. – Pearson

i was using link file to access the my.cnf now modified the my.cnf inside /etc so now, i am able to see the changes. – Pearson

Answer 2

Try this: In /etc/mysql/my.cnf file(location in your case could be different, do locate my.cnf to find the location for your system), add/modify:

[mysqld]
innodb_ft_min_token_size = 2

to set the minimum limit to 2. Don't forget to restart the mysql server and rebuilding the fulltext index. For me, the following worked like a charm.

drop index index_stem on  maps;
CREATE FULLTEXT INDEX index_stem ON maps(stem_value);

Bossuet

Answer 3

As of MySQL 5.7.5, the innodb_buffer_pool_size configuration option can be set dynamically using a SET statement, allowing you to resize the buffer pool without restarting the server. For example:

mysql> SET GLOBAL innodb_buffer_pool_size=402653184;

https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html

Euxenite