MySQL Bugs: #845: Cannot change ft_min_word

Bug #845	Cannot change ft_min_word_len
Submitted:	14 Jul 2003 18:18	Modified:	15 Jul 2003 23:07
Reporter:	Michael Winston	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	4.0.13	OS:	MacOS (os X 10.2)
Assigned to:		CPU Architecture:	Any

Description:
I cannot change the variable "ft_min_word_len".  I've tried it in the command line (SET GLOBAL 
ft_min_word_len=2) and in my.cnf (located in /etc/my.cnf).  The former produces an error: 
Unknown system variable 'ft_min_word_len'.  The latter just has no effect.

How to repeat:
Try "SET GLOBAL ft_min_word_len=2"

Full-text variables cannot be changed dynamically. You must set
them at server startup time. See:

http://www.mysql.com/doc/en/Fulltext_Fine-tuning.html

Remember too that you must rebuild your full-text indexes
after changing ft_min_word_len in my.cnf. Otherwise
the change has no effect.

Can't change it dynamically?  Okay, fair enough.  I want it to be a permanent change, anyway.  
However, adding the line
set-variable    = ft_min_word_len=2
to the mysqld section of my.cnf (at /etc/my.cnf) also has no effect.  In fact, it looks like mysql isn't 
even using that file....  No matter what I change in there, it has no effect.

Setting ft_min_word_len with set-variable in the [mysqld]
group should work.  When you say "has no effect", what
do you mean?  For example, if after restarting the server,
do you see this result when you check the value of that
variable?

mysql> SHOW VARIABLES LIKE 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 2     |
+-----------------+-------+

If you do see that value, then have you rebuilt your
full-text indexes?

Sorry that I was unclear (first time reporting a bug).

No, the variable remains at 3.  I thought that might be a minimum, so  tried changing it to 5, but 
that also didn't stick.

Sigh.  Our new MySQL "expert" just realized that he somehow managed to set the permissions on 
my.cnf to root-read only.  Once I changed the permissions I was able to change ft_min_word_len.

Very sorry for the trouble.  Thank you for your patience.

Thanks for the feedback - we consider this case closed, then.

I read this thread and I am having the same exact problem. I did check the /etc/my.cnf user/group - it is mysql/root and privileges are rw_r__r__. No matter what i do with the 

[mysqld]
set-variable    = ft_min_word_len=2

I always get a read out of 

ft_min_word_len                 | 4 

I recently downloaded the latest binaries... its version stamp is 4.0.20 running on linux/intel. Please help.

Thanks in advance.

I read numerous accounts about how to set ft_min_word_len only which cannot be done using the SET statement.

However, my situation is completely different, the client wants the change to be temporary, at application runtime.  I am building a portable web application within its own built-in mini search engine that uses a fulltext search using MySQL (tested in 3.23.58 and 4.0.10).  

One of the restrictions of the fulltext search query is that words are, by default set at min length of 4 characters (ft_min_word_len = 4).  The client needs it changed to 3, BUT, they do not want it changed due to other applications running the same MySQL instance as this new app, so they want it to remain at 4 globally but "3 locally".  Is this even possible?

Thanx
Phil

Paul DuBois:
     can u tell me that if fulltext can be used on single character.
     now,i can get the result list by single  word. but i want to get the result list by single character. how can i do????

in windows im not able to find my.conf 
how should i change ft_min_word_len to 2 ..
please help

I'm using mysql-4.1.10-win32, I had issues setting ft_min_word_len to 3 instead of the default value 4. Finally I got it working. Thought I would share it with you all.

1. dynamically changing 'ft_min_word_len' when the server is already up won't work

2. start the server as show below:

mysqld --console --ft_min_word_len=3

Note: this is just how I start the demon, you might start it differently but add --ft_min_word_len=<your-value> to the command line

3. go to mysql shell and check the value of 'ft_min_word_len' by typing :

mysql> show variables like 'ft_%';

This should show you the value you have set

3. rebuild your database table with index

4. check your query and see if you get the right results.

You should be all set

For Windows 2000/NT environment where mysql is started as service, another way to set the system environment is to use my.ini configuration file.  

Add the following lines to my.ini: 
# Minimum word length to be indexed by the full text search index.
# You might wish to decrease it if you need to search for shorter words.
# Note that you need to rebuild your FULLTEXT index, after you have
# modified this value.
ft_min_word_len = 3

Place my.ini in windows directory or MySQL install directory.

Make sure the server reads the config file use the startup option 
 "--defaults-file".
e.g.  mysqld-max-nt  --install mysql --defaults-file="C:\MySQL\my.ini"

Start server by running: net start mysql

SQL> SHOW VARIABLES like 'ft_min_word_len';

So I am using mySql in a hosted environment (phpwebhosting.com), in this case is it possible for me to change the ft_min_word_len variable? Im pretty sure I dont have the permissions needed to start/restart the server, is this what I would need theoretically or is there a 'workaround'?

Thanks,Paul

I think I know what's happening here. I'm having the same problem: MySQL variables don't reflect the changes I make in my /etc/my.cnf file.

The reason is that somehow the groups that will be read from the configuration file by the MySQL server on the start up are predefined, so if the group mysqld isn't defined, the variables you set on that group won't even be read.

You can check out what groups are going to be read on the server startup by typing "mysql --help" (near the end). In my case it says "The following groups are read: mysql client", so you can easily see that I don't have many options to change. The only groups I can affect are [mysql] and [client], and not the [mysqld], the one I need to.

I'll see if I can change this option and tell you about it.

I'm sorry, that was a mistake I made. The information I was reading was from the [mysql] client and not the [mysqld] server. But I've found what's the problem and myabe it's the same you're having.

I was editing the file "my.cnf" in the "etc" directory of my server account (located at the same directory of "public_html") through FTP, and the file I needed to edit wasn't this one. The file that the mysqld server reads on the start up must be edited using SSH.

For those who don't know, you must login through SSH (using some client like Putty) and edit the file using some editor (that can be VI in linux).

I think that's all. See you...

My problem is i need to know how to set full text min word length equal to 2.
I searched for my.ini file in mysql folder i didnt find it and search failed for my.conf file also...