MySQL Fulltext Query problem on MySQL 5.1

Polyglot Projects Sphinx & Full-Text Search
1

Hi,

I upraded my database server from 4.1 to 5.1.32. But somehow following query doesn’t shows any results in 5.1 it just running look likes query hung.

mysql 5.1.32> SELECT colname FROM table1
WHERE (related=1 or related=2) AND match(colname) against (‘+dimension whd’ in boolean mode)
AND (LENGTH(TRIM(colname)) - LENGTH(REPLACE(TRIM(colname), ’ ', ‘’)))<4
GROUP BY colname ORDER BY date DESC LIMIT 0,50;

"" between w h d ('+dimension wh*d’ in boolean mode) causing the problem. when i remove those * query runs without any problem. I tried same query on MySQL 4.1 it runs without any problem. Might be i need to edit characterset file but not sure.

mysql> show variables like ‘%ft%’;

±-------------------------±---------------+
| Variable_name | Value |
±-------------------------±---------------+
| ft_boolean_syntax | + -><()~*:“”&| |
| ft_max_word_len | 84 |
| ft_min_word_len | 3 |
| ft_query_expansion_limit | 20 |
| ft_stopword_file | (built-in) |
±-------------------------±---------------+

full text related parameters are same on both i.e. MySQL 4.1 & MySQL 5.1.

Thank you.
Aspen

2

Check EXPLAIN on both servers and see if there is a difference. When you say it looks like it’s hung, what does that mean? Is it really? Check SHOW FULL PROCESSLIST, and check a few samples of SHOW GLOBAL STATUS LIKE ‘Handler%’ and see if it’s doing anything, or if it’s truly hung.

3

Hello,

Hung means query shows in SHOW FULL PROCESSLIST from a long time on MySQL 5.1. Further i executed this query manually on MySQL 5.1 it doesn’t execute didn’t give me results. It’s running only and i didn’t get MySQL prompt back.

Thank you.