can I disable cache_menu? how?

Many hosts tell you that you can't adjust the default php settings and promote moving to a VPS or Dedicated.

Have you actually tried using a custom php.ini to change these to verifiy that you actually can not change the settings ?

side note, you should be updating to drupal 6.5 to remain as secure as possible.

my apologies, I was thinking of post_max_size. max_allowed_packet very well may need to be changed on the server in my.conf or my.ini if on a windows server.

I don't know that you can shut off menu cache on demand without hacking core. Will do some more research for you. In the meantime you may get more help by posting the question to the forums.

That's actually a MySQL constraint. Looks like your host has artificially reduced the max packet size. The server default looks to be 1MB. If you suggest to them that you might just go elsewhere then perhaps they won't try to fleece you for another $232 per month.

http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html.

The easiest way round this might be to install an alternative cacheing module that uses files instead of the DB.

which should consider whether it makes sense at all to cache the whole router.

You can disable the menu cache, but it may have a bit of a performance impact.

Anyway, try this and see if it slows down the site.

1. Copy includes/cache.inc to includes/cache-no-menu.inc

2. Edit includes/cache-no-menu.inc so that the the cache_set() function that previously looked like this:

function cache_set($cid, $data, $table = 'cache', $expire = CACHE_PERMANENT, $headers = NULL) {
  $serialized = 0;

Now looks like this:

function cache_set($cid, $data, $table = 'cache', $expire = CACHE_PERMANENT, $headers = NULL) {
  // Added these 4 lines ...
  if ($table == 'cache_menu') {
    return;
  }
  // End of added lines
  $serialized = 0;

Then edit your settings.php file to add the following line:

  $conf['cache_inc'] = './includes/cache-no-menu.inc';

This should make all cache_set operations for cache_menu not do a db query and solve your issue.

@Khalid it seems that the single entry for the full menu router is the main issue. We should look anyhow into whether that's useful to cache.

Yes it must be the "router:" entry that is causing the problem, so the new cache_set() could be further refined:

function cache_set($cid, $data, $table = 'cache', $expire = CACHE_PERMANENT, $headers = NULL) {
  // Added these 4 lines ...
  if ($table == 'cache_menu' && $cid == 'router:') {
    return;
  }
  // End of added lines
  $serialized = 0;

Should certainly lessen any performance impact.

[update] @cindyr: what is the current 'max_allowed_packet' setting for your hosting plan?

@11: From http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html:

The server's default max_allowed_packet value is 1MB.

So I take back all my nasty comments about your host at #5 - the server is using the default MySQL setting. Looks like we may be seeing more of this problem now that OG and other "heavyweight" modules are coming on-stream for 6.x.

See also #317775: Caching entire {menu_router} table causes MySQL error/slow rebuilds and slows menu_link_save.

oops wrong place

Solution was provided so I'm setting this to fixed.

- Arie

Anybody found some solution without disabling cache_menu?

@18: see #10. Only inhibits cacheing of the menu router table, so the performance hit will be less. But does require you to re-patch cache.inc each time you upgrade. Alternatively you could use the option in settings.php to specify your version of cache.inc (you could perhaps put it in sites/all/cache) as an alternative cacheing system, which might reduce the (small) maintenance overhead.

#8 worked for me, solution #10 still caused the error.

It could be the links:navigation:tree-data:* entries. Try something like

function cache_set($cid, $data, $table = 'cache', $expire = CACHE_PERMANENT, $headers = NULL) {
  // Added these 3 lines ...
  if ($table == 'cache_menu' && ($cid == 'router:' || strpos($cid, 'links:navigation:tree-data:') === 0)) {
    return;
  }
  // End of added lines
  $serialized = 0;

Or use a contrib module that provides an alternative cacheing mechanism (e.g. files) to selected tables.

I have quite a few modules on one site and the router: entry is about 400K, and the navigation links entries up to 90K. I wonder what it pushing your site over the limit. (What is your max packet size BTW?)

For cache_form as well:

  if ($table == 'cache_menu' || $table == 'cache_form') {
    return;
  }

#357938: max_allowed_packet on admin/build/modules page
#231143: Taxonomy term lists in node edit forms

Another approach:

http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html says that the dynamic system variable max_allowed_packet exists in both global and session scope. To change the value in global scope you need the SUPER privilege. I don't know what is needed to change a value in session scope, but I can do it on my shared server, as follows:

SET SESSION max_allowed_packet=16*1024*1024

Does this work for you? You could try wrapping the query in db_query() and putting it at the top of cache_set().

db_query('SET SESSION max_allowed_packet=16*1024*1024')

You can get the current value with this query:

SHOW VARIABLES LIKE 'max_allowed_packet';

Nice, I'll try this.

There is no patch.

- Arie

Paste followed line before your line of the issue:

db_query('SET SESSION max_allowed_packet=16*1024*1024')

Line of the problem you can get from the message:

user warning: Got a packet bigger includes/cache.inc on line 109.

There is no patch, because it's general problem and this issue can be in different places in different modules.

I believe this should be left as active until such time as an actual patch is created. As it stands there are some workarounds but not a fix that be patched into core. Though to get something like this put into core it would have to be patched against 7.x and backported I believe.

At the very least a patch that would allow one to turn off menu cache in the admin UI if necessary?

Yes, the fix at #23 / #27 is very much a workaround in the context of this support request.

>patch that would allow one to turn off menu cache
This fix actually doesn't do that any more but reconfigures the active MySQL connection (i.e. for the duration of the current page request) - it increases the max packet size so that the limit is less likely to be breached.

A permanent fix for core would want to put the query somewhere else; hard to say where would be best, but since we are talking about a MySQL option here it would need to be somewhere in the MySQL DB layer. I imagine other DB APIs have a similar restriction; which might or might not be breached as easily as this one. At this stage it might even be best to handle the fix via a contrib. module (e.g. in hook_init()).

For those who tried to disable cache_form table, it's very bad idea and some forms are not submitted properly.
http://drupal.org/node/93779#comment-1254794

Try this.
#361967: Increase MAX_JOIN_SIZE and MAX_ALLOWED_PACKET settings in system.install
You can increase max_allowed_packet, but only if they support it.

see patch: http://drupal.org/node/251792

this shoudl resolve the problem also.

Thanks, AFAICS #251792: Implement a locking framework for long operations will fix the problem in many (most?) but not all cases. It will help because as a performance enhancement unrelated to this issue it does away with the caching of the router: blob. But per #20 and #21 there may (especially on large sites) be other blobs (links:navigation:tree-data:...) over 1MB.

I'd be rather surprised if of the other cached items are nearly as large as the router - I guess if you set a bunch of links to be always expanded and when you're deep in the admin menu.

>I'd be rather surprised if of the other cached items are nearly as large as the router
Well yes, I'm being a bit pessimistic - the biggest I've seen for other cached menu items is 90K (#21), tho' kenorb (#20) managed to get something other than router: over 1MB.

So #251792: Implement a locking framework for long operations will be at least a "90% solution".

Also see http://drupal.org/node/379976 which is essentially the relevant part of the solution proposed at #31.

Other solution if you have problem with big packet stored into cache_menu or cache_router table, is good to change cache engine to non-database like: www.drupal.org/project/fastpath_fscache
I think there are already lot of solutions described in this thread, so marking it as fixed.

Thank you kbahey for your solution in comment 8

I developed a site on my development server and started to migrate it to my production server. I exported my DB (using phpMyAdmin) and then tried to import it to the production server using phpMyAdmin. I tried this numerous times, What would happen all the time was that it would successfully import 24 of 161 tables, stop and give this error "Mysql Error 1153: Got a packet bigger than ‘max_allowed_packet’ byte".

I did a bunch of googling and it appeared that the solution was to go into the server's "/etc/my.cnf" file and change the value of "max_allowed_packet" from 1MB to 16MB. As a test I tried this on my local MAMP's sql. I tried importing my DB with no success. I could not import the DB, it had the same behavior as my production server.

I did some more googling and came up with this thread. I followed the procedure in comment 8 (http://drupal.org/node/321154#comment-1059946), flushed all the caches and exported my DB from my development server. The result was a considerably smaller exported DB. I tried importing this into my production server again using phpMyAdmin.
Bingo! Worked right away! It did this quickly with no errors.

This is very cool. Thanks

Hi, Khalid/ Giles:

from all the discussion happening on the forum, I for sure knew that I was running into a wall, with my shared hosting a/c.

However, your fix saved me, and probably my weekend too. It works just as well as before, minus the worry-lines. Thanks a ton !!

cheers
-vallabh

@ kenorb #37
you call this fixed? I call this "hacked".. if it isn't fixed in core so that it will be included automatically in the drupal download I call it a hack.. of course I use the hack that kbahey describes in #8 but still it's a hack. And then they mention that you will have to save that file otherwise you will override it when you upgrade.. that's totally not the drupal way. Yes, I'm happy it works but still 2 years later.. I will not remember that.. unless I would log everything so detailed.. but if everybody does that.. what's the use of the "drupal way"?

So why is it closed then? Because there are enough hacks that make the problem go away?
There must be a reason why the issue was there in the first place.. I'm running a site with almost "no" content and 15 content types with in total maybe 100 different fields and installed about 50 modules. More than that I haven't done with the site and I get the problem.

I don't understand why this is so easily set to fixed.
Please comment cause I really want to understand why.

Cheers.

#37 is just workaround.

Problem:
Got a packet bigger than 'max_allowed_packet' bytes query: UPDATE cache_menu SET data = '...'

Why does it happen?
On high traffic websites normally you shouldn't use database cache, because it's killing your site performance.

Proper solution:
Implement cache engine on your server, i.e.
http://drupal.org/project/memcache

If you have high traffic, you should be conscience of upgrading your environment. You can't run high traffic website on the slow server without any caching. It's how Drupal is designed.

OK, I've about 28000 book pages plus 18000 taxonomy terms. cache_menu is killing the mysql server, cos I only HAVE about 20 GB of hard disk space on the /srv /var /tmp partition. (And haven't needed even half of that, until cache_menu hit the fan).

Running a local wget means that the cache_menu table is another 2.5 GB larger every half hour or so ... not funny. I'm not putting that beast onto the live server.

I haven't seen just how large the table will get, as it really honestly killed my localhost: "not enough memory to run [whatnot]".

I'll be killing off cache_menu by following the hints in #8 and #10.

Thanks for those!

Does anyone know if cache_menu will be rebuilt if the data in it is deleted? That way we could just export the database with all tables except cache_menu (cache_*?) and avoid the 1MB max packet limit on import.

I just tried it, and it appears to have worked fine. I truncated the cache_menu table (in phpMyAdmin) and immediately after a page load the data was re-cached.

All caches are rebuilt if emptied out. That includes cache_menu.

#8 http://drupal.org/node/321154#comment-1059946 worked for me. Still have to mesure side effects

Hello!
Working with D 6.17, had the same issue,
user warning: Got a packet bigger than 'max_allowed_packet' bytes query: UPDATE cache_menu SET data = '...'
on administrative pages (actually I believe specifically on "edit" pages, dont remember well, dizzy of all the erros, warnings and problems Drupal gives me)

Anyway -> # 8 resolved, #10 brought it back.... (like #20)
Didn't have the energy and time to read further than that. Maybe tomorrow, next week or next life...
For the moment #8

@47: That follows. If you find that site performance is suffering (on account of menus not being cached) then have a look at http://drupal.org/node/379976 and especially the module mentioned there. A proper fix would be to get your host to increase the max_allowed_packet on your server.

You are right, I actually DO NOT WANT to avoid caching of menus, I just want to have NO ERROR when going to edit/admin pages!!

Tried increasing max-package thing, through drupal tweaks, and went up to 32M but it didn't make the error disappear. So I'm kinda forced to apply that patch.
Anyway increasing max-package still feels like a kind of bug-solution: the basic problem really is that all that stuff shouldn't even be cached when going to edit/administration pages in the first place.
Maximum caching for visitors ok, but not so necessary on admin pages. Or at least be able to differentiate and choose!! How come there is more caching for admins than for anonyms anyway??

By the way I seem to have additional caching-problems using domain access. Anonymous users get the same cached homepage on ALL domains, see: http://drupal.org/node/866668
Now that I think of it, could that be caused precisely by this patch??
Also Javascripts don't show in blocks through the include command in PHP (reservation forms). Saw something about that somewhere, but don't remember, too many accumulated problems.

>still feels like a kind of bug-solution
The 1MB default packet limit causes problems for many systems, not just Drupal. Your host should be able to increase it. Incidentally I have a big site and the biggest 2 entries are only about 500KB.. one is for admin_menu module and the other is links:navigation:tree-data ... once you've found out which entries are causing the problem you could trying something like #21.

Note that http://drupal.org/requirements recommends 16MB max_allowed_packet.

>be able to differentiate and choose
Sounds like a feature request: http://drupal.org/node/add/project-issue/drupal

>there is more caching for admins than for anonyms anyway
Not sure I'd agree. Drupal uses caching in various places to improve performance both for anon and logged-in users.

Hye,

How #8, #10, #21 become with Drupal 6.22 ?

Thank you