Hi,

On several of the sites I've worked on (mylifetime.com, fastcompany.com, incbiznet.com) we've had two problems with views cache.

  1. Views serialized cache data being too large for MySQL's max_packet_size
  2. Views unserialized cache data being too large for memcached's hard 1MB chunk limit

So I finally made a patch of the fix I implemented splitting the table cache into 4 queries to get around the issues. The first issue can be fixed by changing max_packet_size to a larger number, but the second is a hard limit of memcached by default. You can compile it to have a larger chunk size but then you waste memory and have a slower lookup time.

Here is the patch.

Thanks,

Steve Rude

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

giorgio79’s picture

Thanks for this, I am getting various errors that I believe relate to views cache.

One for example is

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 4104 bytes) in .../modules/taxonomy/taxonomy.module on line 994

Wil try this patch now

moshe weitzman’s picture

Seems like a safe way to split things up, even if it turns one query into four.

sun’s picture

Priority: Normal » Critical

That's indeed a really nice idea. I'll point some users here.

babbage’s picture

Will there be a version of this patch for 6.x soon? I am trying to migrate a database and have hit the max packet size issue...

doc2@drupalfr.org’s picture

Is solution 2 it suitable for most cases, not just for memcached users?

Merlinofchaos, what do you think of that?

merlinofchaos’s picture

Right now there are various reasons the Views cache can be too big, so I've not been behind patches to split them up until steps are taken to reduce the views cache to the size it's supposed to be. This includes the taxonomy problem and I've seen certain votingapi modules incorrectly use the votingapi api to cause this as well. That said, if the patch makes your site work, it's worth utilize. It is, after all, only the cache and that makes it an easy thing to add/remove without too much concern.

dbabbage: There are no bug reports of this nature for 6.x that I'm aware of. I've never heard of anyone running into that from Views on D6, unless there's something in the queue that I haven't gotten to yet.

sun’s picture

A possible reason for why this issue has not come up on 6.x yet might be that there are not as many large-scale as well as shared-hosting deployments of Drupal + Views on D6 as on D5.

merlinofchaos’s picture

The redesign in D6 also stores significantly less data, so it's much less likely for modules to abuse the cache.

eaton’s picture

Also, VotingAPI -- one of the offenders -- no longer stores so much data in the Views cache.

Leeteq’s picture

#9: Is that the case for the current D5 version too?

eaton’s picture

Unfortunately, no -- so this patch is still very relevant for D5.

bcn’s picture

I could not get this patch to apply cleanly, though after applying manually, the max_packet_size errors go away... Thanks to the OP.

bcn’s picture

FileSize
1.88 KB

Her is a re-roll of the original patch... I don't know why the original didn't work.

Babalu’s picture

i have now this problem on my drupal 6.
this patch don't work with the d6 version right ?
i don't found the file views_cache.inc

sun’s picture

@Babalu: Please file a separate issue, Views 2 is a completely different module.

bcn’s picture

To update, In the few days since I've applied this patch I have not experienced the max_packet_size error described by this issue.

sun’s picture

Status: Needs review » Reviewed & tested by the community

#121390: Many CCK fields and using views causing max_allowed_packet errors with MySQL query "UPDATE cache" has been marked as duplicate, contains many follow-ups from users experiencing the max_packet_size error (me included).

jhedstrom’s picture

I was also having the problem detailed in #121390: Many CCK fields and using views causing max_allowed_packet errors with MySQL query "UPDATE cache" and the patch here fixed the issue. Thanks!

doc2@drupalfr.org’s picture

EDIT: October 8th, 2008: This post of mine was totally unrelated. Forgive me. Arsène

ianchan’s picture

Version: 5.x-1.6 » 6.x-2.0-rc4
Status: Reviewed & tested by the community » Active

oops.. sorry

merlinofchaos’s picture

Oh come on, you're being very, very rude. This is a PATCH for the Drupal 5 version of Views. changing the status and the version is DISRUPTIVE and it should be completely obvious that changing the status from patch to active is the wrong move.

ianchan’s picture

Version: 6.x-2.0-rc4 » 5.x-1.6
Status: Active » Reviewed & tested by the community

sorry... didn't think through what I was doing. It's been a long day upgrading modules from 5.x Will re-post to a new issue.

hansrossel’s picture

I was also having the problem detailed in #121390: Many CCK fields and using views causing max_allowed_packet errors with MySQL query "UPDATE cache" and this patch fixes it perfectly, thanks.

Daryljames’s picture

I applied the above stated patch and my site went down completely... have been getting the max_allowed_packed errors, and 500 internal server errors... I switched it back to the original views_cache.inc file, and all of my cck fields have disappeared... the fields are still in the database but won't display on my site in viewsr...

Daryljames’s picture

Was able to finally bring everything back up...
It seems the patch truncated all of my cache tables....

still have the error...

Warning: Got a packet bigger than 'max_allowed_packet' bytes query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'Got a packet bigger than 'max_allowed_packet' bytes\nquery: UPDATE cache_views SET data = 'a:4:{s:6:\\"tables\\";a:62:{s:17:\\"nodefamily_parent\\";a:3:{s:4:\\"name\\";s:10:\\"nodefamily\\";s:4:\\"join\\";a:3:{s:4:\\"left\\";a:2:{s:5:\\"table\\";s:4:\\"node\\";s:5:\\"field\\";s:3:\\"nid\\";}s:5:\\"right\\";a:1:{s:5:\\"field\\";s:10:\\"parent_nid\\";}s:4:\\"type\\";s:5:\\"inner\\";} in /home/mysite/public_html/example.com/includes/database.mysql.inc on line 174
Home

anyone have any ideas?

christefano’s picture

Works for me. Thanks, everyone.

emilyf’s picture

FileSize
56.31 KB

I applied the patch at the top to views-5.x-1.6 and it doesn't fix my issue. I also tried the second patch provided, but that one fails:

(Stripping trailing CRs from patch.)
patching file views_cache.inc
patch unexpectedly ends in middle of line
Hunk #2 succeeded at 148 with fuzz 1.

So I went with the original patch, didn't get any errors when i patched it, but the error is still sitting there. I also tried truncating watchdog and cache_views and no luck.

any ideas on what else i could do or why this didn't work? the error is killing my site (see screenshot)

minesota’s picture

Subscribed

christefano’s picture

emilyf: please try the patch in comment #13 instead.

Kars-T’s picture

Status: Reviewed & tested by the community » Needs work

This is wrong:

-    $data = cache_get("views_tables:$locale", 'cache_views');
-    $cache = unserialize($data->data);
-
-    if (is_array($cache)) {
-      $views_tables = $cache;
+    $tables = cache_get("views_tables_tables:$locale", 'cache_views');
+    $filters = cache_get("views_tables_filters:$locale", 'cache_views');
+    $fields = cache_get("views_tables_fields:$locale", 'cache_views');
+    $sorts = cache_get("views_tables_sorts:$locale", 'cache_views');
+    
+    if (is_array($tables) && is_array($filters) && is_array($fields) && is_array($sorts)) {
+      $views_tables['tables'] = unserialize($tables->data);
+      $views_tables['filters'] = unserialize($filters->data);
+      $views_tables['fields'] = unserialize($fields->data);
+      $views_tables['sorts'] = unserialize($sorts->data);

This line

if (is_array($tables) && is_array($filters) && is_array($fields) && is_array($sorts)) {

$tables and the other variables are of the type object! If you look at the lines you erease

-    $data = cache_get("views_tables:$locale", 'cache_views');
-    $cache = unserialize($data->data);
-
-    if (is_array($cache)) {

The original sources grabs the data from the database and unserealize $data->data which is an object and check the new variable $cache if its an array.

The patch grabs an object from the database and checks if its an array.

I tested this and the whole cache building process is always run. I have a taxonomy of 19.000 Terms and my drupal had a response time of 20 seconds on a node that uses views.

is_object should be used instead of is_array. Or maybe the whole syntax should be rewritte and the whole cache array should only be builded on demand!

slantview’s picture

Status: Needs work » Needs review
FileSize
1.84 KB

wow, that seems obvious, but i can believe that got missed. here is a reroll of the patch from #13

Kars-T’s picture

Status: Needs review » Reviewed & tested by the community

Cool thanks for the patch!

I tested this on one of my installations and it runs fine. :)

DevElCuy’s picture

works for me too!

mroswell’s picture

Thanks for the patch. My “packet bigger than 'max_allowed_packet' bytes query” warning message was so large that it hosed my screen capture software (which is usually happy to take a scrolling screenshot.) I applied views_cache_inc_01.patch (#31), and my Drupal site is back up and running.

I had been using the wonderful Google Maps Tools module when, after several successful days of use, I got the horrendous views cache warning (along with a memory-related WSOD). Thanks, again, Slantview!

laken’s picture

Thanks, patched worked for me as well when this issue appeared today.

pedrochristopher’s picture

i had this problem, apparently because I have 12,500 organic groups. this patch here did not work for me. I had to get the patch from http://drupal.org/node/121390#comment-747338 with the gzip fix. That seems to work so far.

kalengi’s picture

I applied the patch from #31 and it works perfectly! Thanks slantview

asanchez75’s picture

patch works for me !
thanks slantview!

jgj’s picture

Status: Reviewed & tested by the community » Needs work

I've tried every patch on this page and none have remedied the problem for me. This is critical, I have a production site in dire need of new views and attempting to do so is bringing the site to a grinding halt.

mroswell’s picture

The patch worked for me, but others have reported that a patch in a duplicate issue was helpful:

http://drupal.org/node/121390#comment-747338

Might be worth giving that one a try.

Kars-T’s picture

Status: Needs work » Reviewed & tested by the community

This patch is not faulty! So please don't set it to "Needs work".

The patch in #31 splits the single views 1 cache into 4 different entries. This helps but can't solve all your problems with the cache usage of views 1. I reset this patch to "tested by the community" because I checked it and have it running without problems for a long time now.

If you have too many problems think about using Views 2 with Drupal 6.

esmerel’s picture

Status: Reviewed & tested by the community » Closed (won't fix)

At this time, only security fixes will be made to the 5.x version of Views.