Recently I've got some on my page mysql errors:

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=#

The solution is to run: db_query("SET OPTION SQL_BIG_SELECTS=1");
But in my opinion there should be some central place to do it, because it not make sense to do that in every place where it happen.
It's good to make some additional variable like SQL_BIG_SELECTS default to false (similar to http_request_checking).
And then if mysql_query return the error of too many selects, set the variable to true.
Next time the page shouldn't break again.

The code to implement:

<?php
       
global $db_type;
        if (
$db_type == 'mysql' || $db_type == 'mysqli') {
           
db_query("SET OPTION SQL_BIG_SELECTS=1"); // added
       
}
?>

#361953: The SELECT would examine more than MAX_JOIN_SIZE rows in view.inc on line 728
#359702: user warning: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE

Other related topics:
http://drupal.org/node/143891

If it's not good idea to implement that simple checking into the core, it will be good to create module which will do similar thing.

Files: 
CommentFileSizeAuthor
#20 361967-max-join-size.patch638 bytesCrell
PASSED: [[SimpleTest]]: [MySQL] 21,126 pass(es).
[ View ]
#2 database_tweaks.zip3.69 KBkenorb
#1 database_tweaks.zip3.16 KBkenorb

Comments

kenorb’s picture

Status:Active» Needs review
StatusFileSize
new3.16 KB

Try following module.

kenorb’s picture

Title:Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install» problems with MAX_JOIN_SIZE rows
Version:7.x-dev» 6.x-dev
Status:Needs work» Needs review
StatusFileSize
new3.69 KB

Updated to v0.2
Now supporting SQL_BIG_SELECTS, MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings.
It's for 6.x core

Latest version:
http://drupal.org/project/db_tweaks

kenorb’s picture

Title:problems with MAX_JOIN_SIZE rows» database_tweaks module to increase your MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings
sunshinee’s picture

Version:7.x-dev» 6.x-dev
Status:Needs work» Needs review

nevermind--just having one of those moments...

Dave Reid’s picture

Version:6.x-dev» 7.x-dev
Status:Needs review» Needs work

Feature requests are for 7.x only now. Don't think it's likely to be accepted as a core module, but maybe think about implementing your tweaks in system_install()?

dafreak’s picture

Version:6.x-dev» 7.x-dev
Status:Needs review» Needs work

That's SWEET! I've been looking for this and have tried building it myself with no success. I have been having max_allowed_packet problems on and off for a long time and was madly trying to come up with the solution. Thanks for the lesson in module development! :-D

One thing I noticed is that you tried to set the GLOBAL for max_allowed_packet. That failed in my case as the server is set to require Super privelege to change it. However I just changed GLOBAL to SESSION and we seem to be in business.

Definitely should set this as a project dudes.

dafreak’s picture

Nope. Nope. Spoke too soon. Damn, I got the max_packet warnings again. :-(

Maybe its in the wrong hook? Anyone? Anyone?

Oh well it seems to be the cache_menu table so I'll try an alternate cache method module, maybe.

Freak out.

yolene’s picture

the module you gave is for D6 ? How to get one for D5 ?
Sorry, i'm a newbie :
where shall i put the php code you wrote ? in the views.module file ? Where in the file ?
Thanks in advance for your help.

kenorb’s picture

Probably it will not work for D5
What kind of problem do you have?

yolene’s picture

Ok i got the answer to the question above thanks to a friend, so i'll paste it here, it may help other people who need to set sql_big_select=1 so that the website works.

My workaround was to hardcode it in drupal core :

Go to folder /includes, and find the file : database.mysql.inc

Edit the file :

Find this:

/* On MySQL 4.1 and later, force UTF-8 */
if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
  mysql_query('SET NAMES "utf8"', $connection);
}
return $connection;
}

and add mysql_query('SET SQL_BIG_SELECTS=1 '); so in the end it should look like this :

/* On MySQL 4.1 and later, force UTF-8 */
if (version_compare(mysql_get_server_info(), '4.1.0', '>=')) {
  mysql_query('SET NAMES "utf8"', $connection);
}
mysql_query('SET SQL_BIG_SELECTS=1 ');
return $connection;
}

Voilà there you are, no more db problems BUT don't forget if you ever update drupal to set this again if you meet the same problem (because you original edited database.mysql.inc file will be erased during the update and replaced with a new one)

catch’s picture

Title:database_tweaks module to increase your MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings» Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install

Retitling, seems like a good idea to increase these where we can, same as defaulting to innodb.

dman’s picture

Funny, I touched on just this thing late last night
full story here

My current prototype workaround code - in the module that needs it:

<?php
/**
 * This function attempts to analyse and repair your MySQL config.
 * It is highly likely the ability to do this will be restricted on shared
 * hosts, but try anyway.
 *
 * This change will go away next time the MySQL gets reset, but that's OK.
 */
function import_html_check_max_allowed_packet() {
 
// Warn about known problem with max_allowed_packet

 
$sql = "SHOW VARIABLES WHERE Variable_name = 'max_allowed_packet' ";
 
$result = @db_query($sql);
  if (
$row = db_fetch_array($result)) {
    if (
$row['Value'] >= (IMPORT_HTML_REQUIRED_MAX_ALLOWED_PACKET * 1024 *1024)) {
      return
TRUE;
    }
  }

 
// Try to set it (I don't know what permissions are required to allow this, but it worked for me.
 
$result = @db_query("SET GLOBAL max_allowed_packet = %d * 1024 * 1024", IMPORT_HTML_REQUIRED_MAX_ALLOWED_PACKET);
 
// Check again (may have failed)
 
$result = @db_query($sql);
  if (
$row = db_fetch_array($result)) {
    if (
$row['Value'] >= (IMPORT_HTML_REQUIRED_MAX_ALLOWED_PACKET * 1024 *1024)) {
     
drupal_set_message(t("Adjusted max_allowed_packet on this MySQL server. Large batches should be OK now."));
      return
TRUE;
    }
  }
...
..     
?>

I was surprised that my low-level php-db-user account was even able to pull this off, but I suspected there would be issues on other more carefully restricted hosts.
Interesting to hear that there are alternatives to the GLOBAL modifier out there. (I don't fully grok MySQL configs at that level)

So ... subscribe.
[Edit - fixed link. Got my ' and " mixed up]

dafreak’s picture

dman,
Full story where? You forget the href att to your ? :-)

dafreak

dafreak’s picture

yolene, (#10)

:-D
Thanks for posting that! I was looking for that response a long time ago and think these "database requirements" should be hard coded into core. My solution of working around the problem worked, but only because cache_menu was causing the error and I was able to cache the blob to file instead of db. (Thank you cache router module!) But this looks like the proper place to set the db requirements. I just wonder if my shared host has somehow blocked the changing of the max_allowed_packet value, since the above database tweaks module didn't work for me. Anyway, I'm ALMOST looking forward to seeing this problem again with some module I install someday so I can try your solution.

Cheers,
dafreak

yolene’s picture

You're welcome dafreak... i remember i had met this problem at the very beginning of my drupal experience and had solved it . When i updated Drupal core the problem happened again but i didn't remember how i had solved it before, so it took a while to find this solution and i'm glad if it can save some other people a few headaches :-)

The databasetweak module is for D6 ... are you in D6 ?

I'm just a newbie, so i did this trick, which may be not very "clean", but i guess there is a relevant reason why this code is not in core, because it has been discussed several times in issue tracker.

My shared host DID refuse to change the setting in the db and i didn't have priviledges to do it in phpMyAdmin, but this hardcoding worked as a workaround.

PS : don't wish for bugs to arrive ... your wish may come true. I'm currently fighting with some new problems due to gallery update :-(

mr.andrey’s picture

Title:problems with MAX_JOIN_SIZE rows» Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install
Version:6.x-dev» 7.x-dev
Status:Needs review» Needs work

I ended up doing what you suggested, yolene, because db_tweaks module didn't work. However, I needed to modify database.mysqli.inc and change the syntax a little bit.

In includes/database.mysqli.inc, find this:

mysqli_query($connection, 'SET NAMES "utf8"');

... and directly after, add this:

mysqli_query($connection, 'SET SQL_BIG_SELECTS=1');

Best,
Andrey.

Crell’s picture

This is a Drupal 7 issue. Is there an issue to deal with in the Drupal 7 driver?

kewlguy’s picture

While surfin' around looking into this error "max_allowed_packet is read only" in my drupal-6.16 installation I stumbled upon this posting and I thought I might drop in a line as to how I fixed this error with MYSQL and the max_allowed_packet

Check out this document for the explanation and make sure that you pay attention to the order of option file execution for this setting.
http://dev.mysql.com/doc/refman/5.1/en/option-files.html

If you run into errors while working this one out then disable and uninstall drupal_tweaks (you can leave the module where it is) and then enable the module after you make the adjustments to the last executed mysql options file.

***** (five stars) For Drupal_Tweaks module!

Good luck with your drupal efforts!

UPDATE: I neglected to mention that if you have some form of control panel that manages your php.ini configuration such as cpanel or plesk you will need to go back into there and just save the configuration then the errors about not having the correct permissions to change max_allowed etc will go away.

brisath’s picture

@kewlguy Can you clarify which configuration I need to change in php.ini so that I have the correct permissions to make this change? My shared host is not being cooperative with the Drupal Tweaks and I really need to resolve this issue on my site.

Crell’s picture

Status:Needs work» Needs review
StatusFileSize
new638 bytes
PASSED: [[SimpleTest]]: [MySQL] 21,126 pass(es).
[ View ]

So I don't know if it's truly useful, but here's a D7 patch. Someone see if this is actually useful, and what the performance implications are. :-)

Damien Tournoud’s picture

Hm.

sql_big_selects

If set to 0, MySQL aborts SELECT statements that are likely to take a very long time to execute (that is, statements for which the optimizer estimates that the number of examined rows exceeds the value of max_join_size). This is useful when an inadvisable WHERE statement has been issued. The default value for a new connection is 1, which allows all SELECT statements.

That's from the MySQL manual.

The default is 1, so we should respect 0 if it's set by the database administrator.

I'm leaning toward won't fix here.

Crell’s picture

Status:Needs review» Closed (won't fix)

Hm. Valid point, I didn't realize that was the default.

I'd also agree with won't fix here. Bug your sysadmin if it's set to 0.

kenorb’s picture

If you set the max_join_size system variable to a value other than DEFAULT, sql_big_selects is set to 0.