I've got following MySQL error when clicking on offers:

user warning: 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 query: SELECT DISTINCT(n.nid), n.title FROM node n INNER JOIN job_posting p ON n.nid = p.nid INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN node i18n ON n.tnid = i18n.tnid AND i18n.language = 'en' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all') OR (na.gid = 1 AND na.realm = 'job_view') OR (na.gid = 0 AND na.realm = 'user_relationship_node_access_author'))) AND ((n.moderate != 1)) AND (( CASE n.type WHEN 'stormnote' THEN 0 WHEN 'stormorganization' THEN 0 WHEN 'stormproject' THEN 0 WHEN 'stormtask' THEN 0 ELSE 1 END )=1 ) AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR (n.language = 'pl' AND i18n.nid IS NULL)) AND ( n.status = 1 AND (p.deadline > 1232102504 OR p.expires = 0) )ORDER BY n.created DESC LIMIT 0, 6 in /home/sites/co.uk/public_html/sites/all/modules/contributions/job_posting/job_posting.module on line 912.

Comments

kenorb’s picture

kenorb commented

What's the reason?

ainigma32’s picture

ainigma32 commented

It means that the select needs to read more rows than is allowed. See here http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysv...

This is caused by the contributed modules adding to the query that is generated by the Job Posting module.

If you can't change the MySQL server config, maybe gmarus will have some idea how to fix this.

Good luck!

- Arie

kenorb’s picture

kenorb commented

Unfortunately I can't change.
I've got "genius" account on HeartInternet and you can' change anything related to mysql, they saying that this is the problem of 3rd party, not them.
Talk to the hand.

ainigma32’s picture

ainigma32 commented

Just out of curiosity: how many records do you have in the tables that are being queried (i.e. node job_posting and node_access) ?

- Arie

kenorb’s picture

kenorb commented
Status: Active » Needs review

Around 3,637 in job_posting;)
1,398 total in node_access

I've found some FAQ on HeartInternet hosting provider:

Support Database
My script states that the MAX_JOIN_SIZE setting in MySQL set to low. How do I increase it?
MySQL will refuse to execute select statements that process a very large number of rows. You should either adjust your select statement to make it more efficient, or set SQL_BIG_SELECTS=1 in the session before executing the large select statement. Use something like this to do that .. 
$db->sql_query("SET OPTION SQL_BIG_SELECTS=1");

I was thinking what's the best place to put this query?

The other issue is that I'm not always see this error, so I'll don't know if it will fix this problem:)

kenorb’s picture

kenorb commented
Status: Needs review » Needs work

I've put that in job_posting_list_nodes() function:

function job_posting_list_nodes() {
...
  $order = variable_get('job_posting_sort_order', 'DESC');
  if (!db_result(db_query($count_query, _job_posting_gmt()))) {
    $listings .= '<p>'. t('No job postings to display') .'</p>';  
  }
  else {
    db_query("SET OPTION SQL_BIG_SELECTS=1"); // <!-- HERE
    $query = "SELECT n.nid FROM {node} n INNER JOIN {job_posting} p
...

I think that it's ok.

It can be improved something like:

  $order = variable_get('job_posting_sort_order', 'DESC');
  if (!db_result(db_query($count_query, _job_posting_gmt()))) {
    $listings .= '<p>'. t('No job postings to display') .'</p>';  
  }
  else {
    db_query("SET OPTION SQL_BIG_SELECTS=1");
...
 IF $count_query > mysql_max_join_size {
  db_query("SET OPTION SQL_BIG_SELECTS=1");
}
...
    $query = "SELECT n.nid FROM {node} n INNER JOIN {job_posting} p
gmarus’s picture

gmarus commented
Category: bug » support

I'm changing this to a support request because this is an issue with MySQL configuration and not a coding/logic error in the module. You're seeing this error because your host provider has set non-default values for both/either SQL_BIG_SELECTS and MAX_JOIN_SIZE -- at least that's my interpretation. But don't take my word for it. Here's a reference link so you can see for yourself (and possibly forward to your host provider for comment):
http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#sys...
If they seem unwilling to fix it but you're otherwise happy with their services, your resort to the set option clause may be the simplest workaround.

kenorb’s picture

kenorb commented
Status: Needs work » Postponed (maintainer needs more info)

I'll try to find some general solution:
#361967: Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install

kenorb’s picture

kenorb commented
Status: Postponed (maintainer needs more info) » Postponed
kenorb’s picture

kenorb commented
Status: Postponed » Fixed

http://drupal.org/project/db_tweaks

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.