Advertisement:

Author Topic: Search error  (Read 3650 times)

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Search error
« on: June 24, 2014, 07:29:21 AM »
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.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

kat

  • Guest
Re: Search error
« Reply #1 on: June 24, 2014, 11:08:54 AM »

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #2 on: June 24, 2014, 12:34:13 PM »
Quote
Anything here, help?


^^ Nothing definite mentioned related to search.

Happens exclusively when using search with many words (and never seen that error before).
2.0.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,498
  • Gender: Male
    • Kindred-999 on GitHub
Re: Search error
« Reply #3 on: June 24, 2014, 12:43:55 PM »
how many is "many"?
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #4 on: June 24, 2014, 12:56:53 PM »
^^^ ref 1st post, 4 spaces (=5 words) ok, over that makes the error, whatever the words.
2.0.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Offline Kindred

  • The Mean One
  • Support Specialist
  • SMF Legend
  • *
  • Posts: 58,498
  • Gender: Male
    • Kindred-999 on GitHub
Re: Search error
« Reply #5 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?
Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

Offline margarett

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,761
  • Gender: Male
Re: Search error
« Reply #6 on: June 24, 2014, 02:02:18 PM »
That's one of those real PITA problems...

The query that originates this is:
Code: [Select]
$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

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

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #7 on: June 24, 2014, 03:25:20 PM »
Thanks for replying, I'll post some more details in a topic in the Charter board.
2.0.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Lou69

  • Guest
Re: Search error
« Reply #8 on: June 24, 2014, 08:11:40 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".  :)

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #9 on: June 25, 2014, 06:35:04 AM »
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.

Quote
Does it happen to you here?
No, anything entered in search works fine on this forum.
2.0.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

kat

  • Guest
Re: Search error
« Reply #10 on: June 26, 2014, 05:52:31 AM »
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?

Offline Illori

  • Project Manager
  • SMF Legend
  • *
  • Posts: 51,259
Re: Search error
« Reply #11 on: June 26, 2014, 06:07:37 AM »
given it is a database error, it would lead me to believe their database configuration is different from most hosts.

kat

  • Guest
Re: Search error
« Reply #12 on: June 26, 2014, 06:14:52 AM »
Or, that. :)

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #13 on: June 26, 2014, 06:26:33 AM »
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.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

kat

  • Guest
Re: Search error
« Reply #14 on: June 26, 2014, 06:36:59 AM »
Good finkin'! :)

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #15 on: July 10, 2014, 09:00:33 PM »
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.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.

Offline a10

  • Charter Member
  • Sr. Member
  • *
  • Posts: 877
Re: Search error
« Reply #16 on: July 13, 2014, 08:21:57 AM »
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.15, ssl, php 7.1.31, MySQL 10.3.13-MariaDB~bionic
Mods: Contact Page, Like Posts, Responsive Curve, Search Focus Dropdown, Add Join Date to Post.