Here is the error:
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:
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.
How many members do you have?
There are 366 members registered.
Do you have access to the mysql configuration file?
I would need to contact my host. What specifically are you interested in?
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...
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?
Yes, it is small.
What number would you recommend?
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
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
It happens when anyone tries to view the board index, and as far as I know, every other page too.
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.
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.
You could try setting SQL_BIG_SELECTS to 1.
Search for (Subs-DbMysql.php)
if (isset($mysql_set_mode) && $mysql_set_mode === true)
$smcFunc['db_query']('', 'SET sql_mode = \'\', AUTOCOMMIT = 1',
array(
), false
);
add after
$smcFunc['db_query']('', 'SET SQL_BIG_SELECTS=1');
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.