MySQL Error 1193: Unkown system variable

MySQL Error 1193

[matthewamzn]

I'm using mysql 4.0.24, and I've read that this version supports changing the fulltext index search length. But I keep getting this error when trying to change the minimum full text search letter size.

mysql> set ft_min_word_len=3;
ERROR 1193: Unkown system variable 'ft_min_word_len'

[iamlost]

Try the following:

* Put the following in an option file:

[mysqld]
ft_min_word_len=3

* Restart server.

* Rebuild your FULLTEXT indices:

mysql> REPAIR TABLE tbl_name QUICK;

NOTE: if you use myisamchk to perform an operation, i.e. repair, that modifies table indices put the following in an option file:

[myisamchk]
ft_min_word_len=3

[matthewamzn]

This might be an amateur question, but how do you type this into the Putty?:

[mysqld]
ft_min_word_len=3

[matthewamzn]

I've read that it's not one of the variables that can be set dynamically. I have to set it at server startup time.

How do you do this?

[iamlost]

As I said create/modify an option file.
Please read the MySQL Reference Manual. [dev.mysql.com]

If you need to specify startup options when you run the server, you can indicate them on the command line or place them in an option file. For options that are used every time the server starts, you may find it most convenient to use an option file to specify your MySQL configuration.

MySQL programs can read startup options from option files (also sometimes called configuration files). Option files provide a convenient way to specify commonly used options so that they need not be entered on the command line each time you run a program. Option file capability is available from MySQL 3.22 on.

I have never used PuTTY, however, my understanding is that it is simply a secure connection tool. You would therefore use it to access the tools (shell) of the server and then use that shell to implement changes. I would recommend that you contact the server admin or host support staff with specific questions - after reading both the MySQL and PuTTY documentation.

Note: a MySQL option file is simply a text file - use any text editor i.e. Notepad to create. Upload into appropriate directory with (see MySQL documentation and your server architecture) however you upload files. If you have an existing option file edit as appropriate.

[matthewamzn]

I got the .my.cnf file uploaded into the root directory of my domain (had to use SSH). I did the re-indexed the table, but it didn't make the changes.

The .my.cnf I made looks like this:

[client]
user = myusername
password = dbpassword
host = localhost
[mysqld]
database = dbname
set-variable = ft_min_word_len=2
[myisamchk]
set-variable = ft_min_word_len=2

[iamlost]

As you specify setting up a .my.cnf file I presume the server is some flavour of Unix. If the file is world-writable it would be ignored (sensible Unix-MySQL security).

Note: I have never used a ".my.cnf" file; only a "my.cnf" file so not sure of nuances.

[matthewamzn]

I'm going to change the global mysql settings instead. How would you change the my.cnf file to accept fulltext searches on 2 letter words? My file currently looks like this:

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[iamlost]

You said you are using MySQL 4.0.24. My comments are based on that and your posted code.

* Note 1: Please take lots of time to read the MySQL Reference Manual [dev.mysql.com] as it details changes and proper usage. It does take digging :-) but the info is there.

* Note 2: My comments/additions/code suggestions are guidelines only. While I believe they are correct I have not referenced them - please take the time to confirm their validity.

[mysqld]
safe-show-database - is deprecated (from 4.0.2) now being enabled by default - if necessary, use the SHOW DATABASES privilege to control access to database.

innodb_data_file_path=ibdata1:10M:autoextend - InnoDB is enabled by default (from 4.0). Note: some OS or associated sub-systems can cause conflicts - run "kill" tests early in development to confirm host servers won't crash, corrupt data, etc. - if OS = Linux disable the write-back cache.

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

ft_min_word_len=2 - including declaration in both [mysqld] and [myisamchk] (see below) ensures both myisamchk and the server use the same full-text parameter values.

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld] - renamed [mysqld_safe (from 4.0), there is supposed to be a symbolic link for legacy reasons but ... renaming is safer.

err-log=/var/log/mysqld.log - renamed log-error=/var/log/mysqld.log (from 4.0.10)

pid-file=/var/run/mysqld/mysqld.pid

[myisamchk]
ft_min_word_len=2 - including declaration in both [myisamchk] and [mysqld] (see above) ensures both myisamchk and the server use the same full-text parameter values.

[matthewamzn]

Thanks, it's working good now. Where should I store my custom stop words list? I tried placing it in the /ect/ directory on my server, but it doesn't seem to find it. Is there a good place to store this?

[mysqld]
safe-show-database
innodb_data_file_path=ibdata1:10M:autoextend
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
ft_min_word_len=2
ft_stopword_file = /ect/noisewords.txt

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[myisamchk]
ft_min_word_len=2

[iamlost]

I expect the following is a posting error but if not needs to be corrected:

ft_stopword_file = /ect/noisewords.txt

Like ft_min_word_len=2, ft_stopword_file = /etc/noisewords.txt should be in both [mysqld] and [myisamchk].

Should you ever use ft_max_word_len it too should be in both.

[matthewamzn]

I made the change, and restarted the server and repaired the tables. But it still doesn't work.

[iamlost]

I am sorry that the problem persists. Niggling at it through infrequent posts is not very efficient.

I have reread the thread and think it might help if you answer some questions:

1. what is your OS version?
2. what is your directory structure (MySQL related only) from root?
3. what do you expect the option file results to be?
4. what is your test query?
5. what test query result shows option file settings not being applied?

[matthewamzn]

It's actually working pretty good now. It's doing the 2 letter fulltext search. I just have to figure out where on my server I can put the mysql stopwords list.

Unix: FreeBSD
Plesk Contol Panel
mysql 4.0.24

[iamlost]

Glad to hear it. Keep up the hard work :-)