Not the answer you need?
Register and ask your own question!
Many Forum changes were implemented on Tue 22 Sep. Read about new Ranks, Scoring, and Reactions.
Email [email protected] for any comments or concerns.

MySQL Fulltext Query problem on MySQL 5.1

hi_irfhi_irf ContributorCurrent User Role Beginner
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 w*h*d' 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 w*h*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

Comments

  • xaprbxaprb Mentor Inactive User Role Beginner
    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.
  • hi_irfhi_irf Contributor Current User Role Beginner
    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.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.