Advertisement:

Author Topic: sql error  (Read 8050 times)

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
sql error
« on: March 19, 2008, 10:29:34 PM »
Here is the error:

Code: [Select]
The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
And here is the offending query:

Code: [Select]
SELECT b.id_board, bp.add_deny
FROM smf_board_permissions AS bp
INNER JOIN smf_boards AS b ON (b.id_profile = bp.id_profile)
LEFT JOIN smf_moderators AS mods ON (mods.id_board = b.id_board AND mods.id_member = 0)
WHERE bp.id_group IN (-1, 3)
AND bp.permission = 'moderate_board'
AND (mods.id_member IS NOT NULL OR bp.id_group != 3) AND (FIND_IN_SET(-1, b.member_groups))

Is there any way I can fix this? Perhaps reducing the number of rows queried? If so, would this require refactoring of the query? And if that is true, what would be the correct way to refactor it?

Thanks for your help.

Offline kreativekarma

  • Semi-Newbie
  • *
  • Posts: 63
Re: sql error
« Reply #1 on: March 19, 2008, 11:05:52 PM »
How many members do you have?

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #2 on: March 19, 2008, 11:17:10 PM »
There are 366 members registered.

Offline kreativekarma

  • Semi-Newbie
  • *
  • Posts: 63
Re: sql error
« Reply #3 on: March 19, 2008, 11:31:16 PM »
Do you have access to the mysql configuration file?

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #4 on: March 19, 2008, 11:37:09 PM »
I would need to contact my host. What specifically are you interested in?

Offline kreativekarma

  • Semi-Newbie
  • *
  • Posts: 63
Re: sql error
« Reply #5 on: March 19, 2008, 11:46:18 PM »
My hubby is a database admin and he is asking these questions, lol! This is the next thing...

Before you contact anyone, perform this sql query: show variables like 'max_join_size';
This is an amount in bytes - convert to MB by dividing by 1024 twice.
Then we'll see if this number is reasonable first...

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #6 on: March 20, 2008, 12:33:42 AM »
It is set to 300,000, so in MB that would be 0.2861, or 292.96 KB.

That does seem a bit small doesn't it?

Offline kreativekarma

  • Semi-Newbie
  • *
  • Posts: 63
Re: sql error
« Reply #7 on: March 20, 2008, 01:00:08 AM »
Yes, it is small.

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #8 on: March 20, 2008, 01:14:02 AM »
What number would you recommend?

Offline kreativekarma

  • Semi-Newbie
  • *
  • Posts: 63
Re: sql error
« Reply #9 on: March 20, 2008, 01:56:23 AM »
100MB if your server has the memory; must take into consideration amount of query cache and other caching variables - here is a general breakdown of many variables in the configuration file - http://dev.mysql.com/doc/refman/5.0/en/server-parameters.html

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: sql error
« Reply #10 on: March 20, 2008, 03:26:35 AM »
when max_join_size is exceeded, basically it is trying to load too much information from the database into memory

In this case the excessive usage is being caused by either the INNER JOIN or the LEFT JOIN in the query.

What are you doing when this error occurs?  It looks to me like a query for checking moderation permissions but that is a guess :P
Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #11 on: March 20, 2008, 04:23:33 AM »
It happens when anyone tries to view the board index, and as far as I know, every other page too.

Offline metallica48423

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 19,842
  • Gender: Male
  • Professional Multislacker!
    • Zentendo
Re: sql error
« Reply #12 on: March 20, 2008, 02:57:51 PM »
well, long story short, it won't work until that limit is raised.  it seems like your webserver might be configured more for hosting small pages rather than larger dynamic sites.  If you don't have access to change the mysql server's settings, you'll need to contact your host.

Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

Quote
Microsoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"

Useful Links:
Online Manual!
How to Help us Help you   
Search
Settings Repair Tool
     

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #13 on: March 21, 2008, 04:08:59 AM »
I've contacted my host and they have told me it is not possible for them to change the 'max_join_size' setting because it is a global setting for all of the databases they host.

Could someone recommend a course of action? Is there any way at all to make this work without increasing the 'max_join_size' variable?

Thanks.

Offline niko

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 2,003
  • Gender: Male
    • Madjoki
Re: sql error
« Reply #14 on: March 22, 2008, 10:02:52 AM »
You could try setting SQL_BIG_SELECTS to 1.

Search for (Subs-DbMysql.php)

Code: [Select]
if (isset($mysql_set_mode) && $mysql_set_mode === true)
$smcFunc['db_query']('', 'SET sql_mode = \'\', AUTOCOMMIT = 1',
array(
), false
);

add after

Code: [Select]
$smcFunc['db_query']('', 'SET SQL_BIG_SELECTS=1');

Offline tjl

  • Semi-Newbie
  • *
  • Posts: 46
Re: sql error
« Reply #15 on: April 03, 2008, 09:23:43 PM »
I have moved to a new host that has a much larger MAX_JOIN_SIZE limit than my previous one. That fixed this problem.

Thanks for your help.