homepage Welcome to WebmasterWorld Guest from 54.161.200.144
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL Error 1193: Unkown system variable
MySQL Error 1193
matthewamzn




msg:1580428
 1:01 am on Sep 1, 2005 (gmt 0)

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




msg:1580429
 1:29 am on Sep 1, 2005 (gmt 0)

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




msg:1580430
 2:02 am on Sep 1, 2005 (gmt 0)

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

[mysqld]
ft_min_word_len=3

matthewamzn




msg:1580431
 10:44 pm on Sep 1, 2005 (gmt 0)

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




msg:1580432
 7:12 pm on Sep 2, 2005 (gmt 0)

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




msg:1580433
 10:00 pm on Sep 9, 2005 (gmt 0)

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




msg:1580434
 9:30 pm on Sep 11, 2005 (gmt 0)

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




msg:1580435
 10:40 pm on Sep 12, 2005 (gmt 0)

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




msg:1580436
 12:37 am on Sep 13, 2005 (gmt 0)

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




msg:1580437
 7:29 am on Sep 13, 2005 (gmt 0)

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




msg:1580438
 3:42 pm on Sep 13, 2005 (gmt 0)

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




msg:1580439
 6:38 am on Sep 14, 2005 (gmt 0)

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

iamlost




msg:1580440
 4:05 pm on Sep 14, 2005 (gmt 0)

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




msg:1580441
 12:21 am on Sep 15, 2005 (gmt 0)

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




msg:1580442
 2:51 am on Sep 15, 2005 (gmt 0)

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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved