Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install

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

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()?

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.

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.

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.

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)

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

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

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

/**
 * 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]

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

dafreak

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

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 :-(

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.

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

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.

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

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

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.

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.