MySQL had hit its maximum number of allowed connections (300) due to locked queries against the phpbb3 database. In particular, this query had been running for a long period of time locking other queries behind it due to a subsequent UPDATE query:

Code: Select all

SELECT p.topic_id FROM (phpbb_search_wordmatch m1 CROSS JOIN phpbb_search_wordmatch m2 CROSS JOIN phpbb_search_wordmatch m3 CROSS JOIN phpbb_search_wordmatch m4 CROSS JOIN phpbb_search_wordmatch m5 CROSS JOIN phpbb_search_wordmatch m6 CROSS JOIN phpbb_search_wordmatch m7 CROSS JOIN phpbb_search_wordmatch m8 CROSS JOIN phpbb_search_wordmatch m9 CROSS JOIN phpbb_search_wordmatch m0) LEFT JOIN phpbb_posts p ON (m0.post_id = p.post_id) LEFT JOIN phpbb_topics t ON (p.topic_id = t.topic_id) WHERE m0.word_id = 14410 AND m1.word_id = 14411 AND m1.post_id = m0.post_id AND m2.word_id = 75227 AND m2.post_id = m0.post_id AND m3.word_id = 75228 AND m3.post_id = m0.post_id AND m4.word_id = 14410 AND m4.post_id = m0.post_id AND m5.word_id = 14411 AND m5.post_id = m0.post_id AND m6.word_id = 75227 AND m6.post_id = m0.post_id AND m7.word_id = 75228 AND m7.post_id = m0.post_id AND m8.word_id = 1830 AND m8.post_id = m0.post_id AND m9.word_id = 1947 AND m9.post_id = m0.post_id AND p.post_approved = 1 AND p.forum_id NOT IN (11, 12, 30, 134, 153, 154, 155, 156, 157, 158, 159, 163) GROUP BY p.topic_id, t.topic_last_post_time ORDER BY t.topic_last_post_time DESC;

Can someone please explain to me what I can do to resolve this issue?

Seems like a hung (huge?) search query, I'm not sure. In case your board uses the "Fulltext native" search backend I would suggest changing to "Fulltext mysql" (ACP -> Maintenance -> Search index) especially on bigger boards. This will also reduce the size of your DB by up to 1/3.

can u suggest me which script call such query? is it related topic search query?

I think you missed Stephen's point. The fulltext MySQL search would generate more efficient queries. The "word_id" listed multiple times in your posted query indicate its using phpBB's search table approach. The solution is to go to ACP > Maintenance > search, delete the "Fulltext native" search table and create the "Fulltext mysql" index. Depending on the size of your site, it may take a few hours to finish.

I got the point and I have alread set it up but still it doesn't answer my query. Which script call such kind of query as per phpbb they never use this kind of query in there script so it must be some of the phpbb-seo related mod.