Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
Hi,
On several of the sites I've worked on (mylifetime.com, fastcompany.com, incbiznet.com) we've had two problems with views cache.
- Views serialized cache data being too large for MySQL's max_packet_size
- 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
Comment | File | Size | Author |
---|---|---|---|
#31 | views_cache_inc_01.patch | 1.84 KB | slantview |
#27 | Picture 35.png | 56.31 KB | emilyf |
#13 | max_packets_patch.txt | 1.88 KB | bcn |
views_cache_00.patch | 1.65 KB | slantview | |
Comments
Comment #1
giorgio79 CreditAttribution: giorgio79 commentedThanks 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
Comment #2
moshe weitzman CreditAttribution: moshe weitzman commentedSeems like a safe way to split things up, even if it turns one query into four.
Comment #3
sunThat's indeed a really nice idea. I'll point some users here.
Comment #4
babbage CreditAttribution: babbage commentedWill 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...
Comment #5
doc2@drupalfr.org CreditAttribution: doc2@drupalfr.org commentedIs solution 2 it suitable for most cases, not just for memcached users?
Merlinofchaos, what do you think of that?
Comment #6
merlinofchaos CreditAttribution: merlinofchaos commentedRight 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.
Comment #7
sunA 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.
Comment #8
merlinofchaos CreditAttribution: merlinofchaos commentedThe redesign in D6 also stores significantly less data, so it's much less likely for modules to abuse the cache.
Comment #9
eaton CreditAttribution: eaton commentedAlso, VotingAPI -- one of the offenders -- no longer stores so much data in the Views cache.
Comment #10
Leeteq CreditAttribution: Leeteq commented#9: Is that the case for the current D5 version too?
Comment #11
eaton CreditAttribution: eaton commentedUnfortunately, no -- so this patch is still very relevant for D5.
Comment #12
bcn CreditAttribution: bcn commentedI could not get this patch to apply cleanly, though after applying manually, the max_packet_size errors go away... Thanks to the OP.
Comment #13
bcn CreditAttribution: bcn commentedHer is a re-roll of the original patch... I don't know why the original didn't work.
Comment #14
Babalu CreditAttribution: Babalu commentedi 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
Comment #15
sun@Babalu: Please file a separate issue, Views 2 is a completely different module.
Comment #16
bcn CreditAttribution: bcn commentedTo update, In the few days since I've applied this patch I have not experienced the max_packet_size error described by this issue.
Comment #17
sun#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).
Comment #18
jhedstromI 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!
Comment #19
doc2@drupalfr.org CreditAttribution: doc2@drupalfr.org commentedEDIT: October 8th, 2008: This post of mine was totally unrelated. Forgive me. Arsène
Comment #20
ianchan CreditAttribution: ianchan commentedoops.. sorry
Comment #21
merlinofchaos CreditAttribution: merlinofchaos commentedOh 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.
Comment #22
ianchan CreditAttribution: ianchan commentedsorry... 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.
Comment #23
hansrossel CreditAttribution: hansrossel commentedI 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.
Comment #24
Daryljames CreditAttribution: Daryljames commentedI 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...
Comment #25
Daryljames CreditAttribution: Daryljames commentedWas 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?
Comment #26
christefano CreditAttribution: christefano commentedWorks for me. Thanks, everyone.
Comment #27
emilyf CreditAttribution: emilyf commentedI 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)
Comment #28
minesota CreditAttribution: minesota commentedSubscribed
Comment #29
christefano CreditAttribution: christefano commentedemilyf: please try the patch in comment #13 instead.
Comment #30
Kars-T CreditAttribution: Kars-T commentedThis is wrong:
This line
$tables and the other variables are of the type object! If you look at the lines you erease
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!
Comment #31
slantview CreditAttribution: slantview commentedwow, that seems obvious, but i can believe that got missed. here is a reroll of the patch from #13
Comment #32
Kars-T CreditAttribution: Kars-T commentedCool thanks for the patch!
I tested this on one of my installations and it runs fine. :)
Comment #33
DevElCuy CreditAttribution: DevElCuy commentedworks for me too!
Comment #34
mroswell CreditAttribution: mroswell commentedThanks 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!
Comment #35
laken CreditAttribution: laken commentedThanks, patched worked for me as well when this issue appeared today.
Comment #36
pedrochristopher CreditAttribution: pedrochristopher commentedi 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.
Comment #37
kalengi CreditAttribution: kalengi commentedI applied the patch from #31 and it works perfectly! Thanks slantview
Comment #38
asanchez75 CreditAttribution: asanchez75 commentedpatch works for me !
thanks slantview!
Comment #39
jgj CreditAttribution: jgj commentedI'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.
Comment #40
mroswell CreditAttribution: mroswell commentedThe 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.
Comment #41
Kars-T CreditAttribution: Kars-T commentedThis 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.
Comment #42
esmerel CreditAttribution: esmerel commentedAt this time, only security fixes will be made to the 5.x version of Views.