News:

Wondering if this will always be free?  See why free is better.

Main Menu

Search error

Started by a10, June 24, 2014, 07:29:21 AM

Previous topic - Next topic

a10

Seems to happen when entering many words in search with spaces in between. 4 spaces ok, error happens with 5 spaces.

Database Error: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
Apply Filter: Only show the errors from this file
File: /customers/1***/httpd.www/forum/Sources/Search.php
Line: 1338
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.


a10

QuoteAnything here, help?

^^ Nothing definite mentioned related to search.

Happens exclusively when using search with many words (and never seen that error before).
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Kindred

Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

a10

^^^ ref 1st post, 4 spaces (=5 words) ok, over that makes the error, whatever the words.
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Kindred

wow...   I am having a problem these days. I keep missing the information in the first post. lol

odd behavior.

Does it happen to you here?
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

margarett

That's one of those real PITA problems...

The query that originates this is:
$ignoreRequest = $smcFunc['db_search_query']('insert_log_search_topics', ($smcFunc['db_support_ignore'] ? ( '
INSERT IGNORE INTO {db_prefix}' . ($createTemporary ? 'tmp_' : '') . 'log_search_topics
(' . ($createTemporary ? '' : 'id_search, ') . 'id_topic)') : '') . '
SELECT ' . ($createTemporary ? '' : $_SESSION['search_cache']['id_search'] . ', ') . 't.id_topic
FROM ' . $subject_query['from'] . (empty($subject_query['inner_join']) ? '' : '
INNER JOIN ' . implode('
INNER JOIN ', $subject_query['inner_join'])) . (empty($subject_query['left_join']) ? '' : '
LEFT JOIN ' . implode('
LEFT JOIN ', $subject_query['left_join'])) . '
WHERE ' . implode('
AND ', $subject_query['where']) . (empty($modSettings['search_max_results']) ? '' : '
LIMIT ' . ($modSettings['search_max_results'] - $numSubjectResults)),
$subject_query['params']
);

Which, as everyone can see, is plain simple :P

I would say (a bit guessing) that, due to you being searching several terms, the JOINS would need to "dig" more rows than your MySQL is configured to allow... Since this doesn't happen everywhere, I would say that you need to "stretch" your server limits...
What's the size of your forum? (topics/posts)
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

a10

Thanks for replying, I'll post some more details in a topic in the Charter board.
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Lou69

Quote from: Kindred on June 24, 2014, 01:06:15 PM
wow...   I am having a problem these days. I keep missing the information in the first post. lol

odd behavior.

Does it happen to you here?

Nah .... the sentence was not exactly clear. I read it as "many words each with 4 spaces between them".  :)

a10

Examples:
Simple Machines Community Forum SMF Support  = 6 words (5 spaces) triggers error
Simple Machines Community Forum SMF = 5 words (4 spaces) ok

Found this: Match all words = database error, Match any words = no error.

QuoteDoes it happen to you here?
No, anything entered in search works fine on this forum.
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

kat

OK. From that, I guess that we have to assume that there's something with your installation that's different to here.

That suggests, for one, a mod. What mods do you have?

Illori

given it is a database error, it would lead me to believe their database configuration is different from most hosts.

kat


a10

Thanks for replying. Mods are in my signature. Got another 2.0.8 forum on the same host (with same mods), no search error there, but I think it's on a different db server. I'll have a go at the host regarding SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# and see what they come up with.
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

kat


a10

Update. On the server, SQL_BIG_SELECTS=0, MAX_JOIN_SIZE=33554432  Ref http://one-docs.com/mysql/
Can't expect to get anything changed, it's shared.

But found one fix that seems to make a difference, enabling "Match whole words only" in search methods. Probably puts much less strain on the server, and seemingly no practical impact on search results. Now impossible to trigger the "Database Error: The SELECT would examine more than MAX_JOIN_SIZE rows; etc etc", and search is fast and reliable.

So ftm, will leave it at that.
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

a10

So, not exactly solved, but confirm problem somehow bypassed with "Match whole words only" ticked, and search doing fine, no errors since last post.

One remark, with "Match whole words only" ticked it still picks partial hits (which is no problem, but wonder why!).
2.0.19, php 8.0.23, MariaDB 10.5.15. Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Advertisement: