sql error

Started by tjl, March 19, 2008, 10:29:34 PM

Previous topic - Next topic

tjl

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.

kreativekarma

How many members do you have?

tjl

There are 366 members registered.

kreativekarma

Do you have access to the mysql configuration file?

tjl

I would need to contact my host. What specifically are you interested in?

kreativekarma

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...

tjl

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?

kreativekarma


tjl

What number would you recommend?

kreativekarma

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

metallica48423

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

QuoteMicrosoft 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

tjl

It happens when anyone tries to view the board index, and as far as I know, every other page too.

metallica48423

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

QuoteMicrosoft 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

tjl

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.

niko

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');
Websites: Madjoki || (2 links retracted by team, links out of date and taken over.)
Mods: SMF Arcade, Related topics, SMF Project Tools, Post History

WIP Mods: Bittorrent Tracker || SMF Wiki

tjl

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.

Advertisement: