Project

General

Profile

Bug #10738

BP blogmeta update routine incorrectly overwrites 'post_title' for unrelated activities

Added by Boone Gorges 3 months ago. Updated 3 months ago.

Status:
Resolved
Priority name:
Normal
Assignee:
Category name:
Blogs (BuddyPress)
Target version:
Start date:
2018-11-20
Due date:
% Done:

0%

Estimated time:

Description

I'm pretty sure this is a BP bug but I want to open it here first, to get a sanity check from Ray. Also, we'll need to do some cleanup on the Commons, which will need to reference this ticket.

My logging script for catching requests that generate many database queries just sent me notice of a new blog post (/wp-admin/post.php) generating thousands of database updates. The crux seems to be here:

[46] => Array
        (
            [0] => SELECT DISTINCT a.id  FROM wp_bp_activity a  WHERE a.component IN ( 'blogs' ) AND a.type IN ( 'new_blog_comment' ) AND a.secondary_item_id IN ( 57,50,44,34,25,18,13,6 ) AND a.is_spam = 0 AND a.type NOT IN ('activity_comment') AND a.date_recorded <= '2018-11-20 22:23:39' ORDER BY a.date_recorded DESC, a.id DESC LIMIT 0, 100000
            [1] => 0.023439884185791
            [2] => edit_post, wp_update_post, wp_insert_post, wp_transition_post_status, do_action('transition_post_status'), WP_Hook->do_action, WP_Hook->apply_filters, bp_activity_catch_transition_post_type_status, bp_activity_post_type_update, do_action('bp_activity_post_type_updated'), WP_Hook->do_action, WP_Hook->apply_filters, bp_blogs_update_post_activity_meta, bp_activity_get, BP_Activity_Activity::get
        )

    [47] => Array
        (
            [0] => SELECT * FROM wp_bp_activity WHERE id IN (537629,537500,537472,532145,531304,530685,530361,530385,530186,529724,529655,529577,529255,528960,528842,528805,528398,528207,526911,526546,526148,526140,526124,526010,525751,525400,525418,525363,525210,525186,525166,525148,525141,525121,525061,525008,524973,524634,524574,524254,524004,523935,523696,523499,523121,523925,523920,523933,523252,522856,522829,521903,521545,520986,520135,519929,519836,519720,519114,519100,518899,518341,518144,518148,517864,517334,517300,517100,516435,516299,516269,516098,515319,514844,514810,503886,502795,502359,501720,501376,501332,501268,500828,500805,500619,500551,500528,500254,500158,500084,499800,499557,499429,499352,498761,498746,498738,498743,498448,498157,498198,497686,497320,497281,497250,496998,496984,496679,496673,496672,496507,496304,496143,496087,496085,495761,495106,495090,494947,494945,494944,494472,494379,494361,494305,493985,493914,493880,493858,493752,493333,493313,493285,493283,493282,493241,493181,492778,492580,492555,492552,492176,492117,491930,491658,491574,491502,491435,491182,491082,491037,491033,491039,490989,490929,490848,490793,490589,490545,490504,490524,489981,489780,489757,489720,489698,489793,489614,489531,489489,489364,489340,489307,489285,489268,489245,489440,489165,489092,489078,488993,488877,478749,478714,478368,478357,478347,478208,478130,478108,477622,477609,477599,477594,476942,476794,476716,476668,476636,476318,475833,475473,475074,474663,474486,474279,474121,474036,473982,473768,473649,473461,473406,473395,473359,473351,473147,474574,473049,473041,473028,472849,472722,472571,472449,472298,472177,472158,472078,472051,471559,471449,471061,470946,470917,470816,470621,470605,470514,470458,470454,470434,469647,467418,464195,459730,459839,459406,459274,458713,458670,458553,458261,457975,457779,457696,457615,457527,457603,457212,457136,457327,457055,457047,456921,456807,456754,456744,456307,456227,456096,456000,455910,455518,455436,455188,455050,455027,454996,454686,454283,454264,453302,453195,453159,449683,449229,447959,447542,446802,446388,446221,445841,448830,444493,444420,444352,444267,444250,443984,443974,443739,443737,443525,443407,443392,443383,443362,443268,443233,442820,442587,442573,442482,442272,442257,442021,442008,442004,441961,441690,441648,441673,441607,441585,441507,441449,441411,441161,441015,440988,440972,440812,440698,440206,440076,440005,437939,437902,436507,435846,432052,431990,431737,431086,430680,429995,429978,429608,429285,427662,427444,427242,427204,427073,426920,426849,426817,426803,426792,426739,426574,424074,423757,421758,420957,420672,420664,421112,420520,419404,419197,419227,418998,418691,417936,417310,417249,416970,416488,416295,416179,416006,415783,415804,415649,413551,413490,412820,412424,412144,411990,411118,411028,411020,411002,410941,410891,410794,410759,410008,410137,405496,402924,397890,367401,367198,358486,424613,356936,347629,348938,346545,344323,342517,343335,343340,338929,338477,338383,338048,337818,335753,332759,331836,331490,331041,331012,330824,330126,329482,329469,329456,329433,329410,329354,329334,329217,329207,329119,328811,328624,328456,328023,328004,327884,327774,327736,329069,326597,326527,325893,325371,323708,323135,322651,320544,314177,313856,312837,312124,311853,311046,311012,309821,309749,309715,309590,309081,308811,308667,416679,308000,307754,307450,307434,416682,307274,307150,307034,306648,306804,306078,305762,305634,304971,304800,304979,304554,304541,304277,304171,303923,303573,303345,303219,303055,303030,300683,300548,300528,299494,294645,291973,282341,280592,280383,280373,265733,265708,265695,265713,265711,264906,264549,264251,263723,263119,261708,259087,257794,256887,256756,256447,255446,255263,255660,254963,254841,254465,253144,253068,252974,252520,251719,251279,251277,251199,250726,249599,249374,248845,248797,247637,250217,238312,236183,235873,234931,234704,234016,232494,232471,232341,232177,232274,231128,230836,228656,228214,229653,227846,227322,226942,226805,226688,226219,226044,225834,225884,225676,225515,225363,225240,224994,224743,224635,224373,224278,224262,223566,223547,222970,222775,222332,222007,221895,221853,221766,221225,221080,220739,220492,220327,219885,219513,219158,264745,216940,216132,215467,213294,213186,8035,212881,212762,212505,212313,298264,211398,211223,211143,210394,264752,209121,209113,208858,208659,206903,205245,204662,204417,204389,204293,204750,203898,203873,203620,203447,203300,202822,202714,202666,202704,202361,202187,202095,202626,201916,201656,201646,201547,201057,310291,200571,200420,200426,413249,202001,200359,200345,199949,199585,199083,198045,198029,197980,196799,197174,197304,196725,196479,196414,196298,196179,195548,194857,194520,194164,195534,194095,195541,193975,194001,203905,193437,193346,193344,192960,192128,191598,191549,191516,191158,191009,190898,190626,190393,189899,189814,189319,189312,187390,185554,177124,171826,168620,162029,152825,134503,120336,117812,113558,106513,100841,203911,94831,94434,203919,90038,85044,84672,84638,84388,83849,83803,83713,83639,83583,83275,83265,83240,83225,82561,82008,81938,81303,81294,80850,80639,80632,80617,80586,80389,80317,80166,80152,80136,79268,78600,78603,78258,77649,77646,76131,75835,75592,75463,75442,75336,74476,74362,74074,11825,74300,73327,72490,72285,72268,70864,70465,70296,70006,69892,69693,69368,68575,67626,67472,67286,66895,199515,66749,66349,72295,66076,65861,65821,65685,65559,65288,65166,65160,65004,64981,64710,64125,64098,64006,63587,63251,62204,61749,61446,61452,61122,61051,60484,58330,57979,57978,57695,57408,57369,57266,57177,57017,57416,56985,57402,56797,56662,56623,63635,56610,56408,55834,55688,55605,55150,53164,52497,52097,54746,50892,50674,50450,50170,49797,56493,49503,49418,49149,48798,48128,47755,47575,47273,47262,47227,47246,47031,46909,46720,46535,46427,46420,46354,46304,46090,46066,45808,45737,45505,45464,45440,45214,45075,44775,44565,44519,44411,43945,43831,43649,41763,41089,39579,38504,38499,38451,38340,37986,37528,36746,36689,36335,36192,35914,35816,35428,34573,34437,34421,34356,33790,33752,33636,33509,33412,33347,32873,32730,32573,32555,32092,31898,31907,32146,31902,32095,31748,31464,31285,31278,31243,31311,31078,30865,30629,30655,30498,30444,14198,14103,13707,30805,13157,12543,12515,12441,12169,11491,11477,11476,11472,11469,11468,11464,11450,11445,11244,11243,11001,10886,10781,10756,10743,10742,10741,10740,10679,10678,10617,10446,10444,10316,10312,10115,10035,10028,10027,10001,9992,9962,9906,9884,9880,9825,9703,9618,9500,9498,9077,9074,9073,8281,8071,7993,7968,7938,7826,7825,7810,7531,7510,7498)
            [1] => 0.14506387710571
            [2] => edit_post, wp_update_post, wp_insert_post, wp_transition_post_status, do_action('transition_post_status'), WP_Hook->do_action, WP_Hook->apply_filters, bp_activity_catch_transition_post_type_status, bp_activity_post_type_update, do_action('bp_activity_post_type_updated'), WP_Hook->do_action, WP_Hook->apply_filters, bp_blogs_update_post_activity_meta, bp_activity_get, BP_Activity_Activity::get, BP_Activity_Activity::get_activity_data
        )

After this, BP does lots of stuff with the located IDs, including calling bp_blogs_update_post_activity_meta() on each - which updates them with the incorrect 'post_title' meta.

Tracing it back, I believe the problematic part is https://buddypress.trac.wordpress.org/browser/tags/3.2.0/src/bp-blogs/bp-blogs-functions.php?marks=662-666#L634. When querying for old-style activity comment items, BP queries based on 'action', 'object', and 'secondary_id' - but not 'primary_id'. As such, it returns a large number of activity items associated with posts on different sites (bad) and then proceeds to update the metadata on them (worse).

From what I can see, this problem goes back to when the activitymeta syncing was introduced in https://buddypress.trac.wordpress.org/changeset/8944. Hard to me that neither you nor I nor anyone else caught it until now.

Ray, could you please look this over and verify that my reasoning is correct? If so, I think we need to do the following:
1. Write a patch for BP that fixes the bug there. I think it's a simple as adding 'primary_id' to the activity query linked above, but this needs verification.
2. Write a script that walks through 'new_blog_comment' items on the Commons and resets the corresponding 'post_title', as needed


Related issues

Related to CUNY Academic Commons - Bug #11122: Recurrence of incorrect blog_title meta updatingResolved2019-02-18

History

#1 Updated by Raymond Hoh 3 months ago

  • Status changed from New to In Progress

Ugh. Thanks for uncovering this bug, Boone.

I've created a ticket on BP Trac with the fix here.

I've also patched our version of BuddyPress here: https://github.com/cuny-academic-commons/cac/commit/7e8e59a3963386dfe8a6b5b57d1c48aeb7a0840e

I will write a CLI script to fix this, but will most likely run the script on production later in the week. I've marked the ticket as "In Progress" for now and will update the ticket once I've run the script.

#2 Updated by Boone Gorges 3 months ago

  • Target version changed from 1.14.1 to 1.14.2

Thanks, Ray! I'm going to bump this to the next release.

#3 Updated by Raymond Hoh 3 months ago

Boone, can you take a look at this CLI script and let me know what you think?

<?php
global $wpdb;
$bp = buddypress();

// Offset SQL statement addition.
$startfrom = ! empty( $args[0] ) ? (int) $args[0] : false;
if ( false !== $startfrom ) {
        $startfrom = 'AND id > ' . $startfrom;
} else {
        $startfrom = '';
}

$items = $wpdb->get_results( "SELECT id, item_id, secondary_item_id FROM {$bp->activity->table_name} WHERE type = 'new_blog_comment' {$startfrom} ORDER BY id ASC" );

if ( empty( $items ) ) {
        echo "No items to sync.\n";
        return;
}

foreach ( $items as $item ) {
        switch_to_blog( (int) $item->item_id );
        $comment = get_comment( $item->secondary_item_id );

        if ( ! empty( $comment->comment_post_ID ) ) {
            $post = get_post( (int) $comment->comment_post_ID );

            if ( is_a( $post, 'WP_Post' ) ) {
                bp_activity_update_meta( (int) $item->id, 'post_title', $post->post_title );
            }

            $post = null;

            echo "Post title resynced for activity ID {$item->id}\n";
        } else {
            echo "Post no longer exists for 'new_blog_comment' activity ID {$item->id}\n";
        }
        $comment = null;
        restore_current_blog();
}

I checked cdev to see how many 'new_blog_comment' entries would need to be updated and the number is 14,506, which is quite a bit. The number is probably greater on production.

I was going to do the ORDER BY statement by item_id (or blog ID), but decided to sort against activity ID instead. Will probably run this when the site isn't that active (around 2am Eastern).

#4 Updated by Boone Gorges 3 months ago

Thanks, Ray! This looks good, with the small caveat that there's nothing built in here to (a) avoid rewriting to the database if the value is unchanged, and (b) avoid updating the same activity item more than once. That said, (a) is not a huge worry (just adds a bit of overhead), and (b) is only an issue if you're not careful in the way you run it (offsets, etc). So I think it's good to go.

#5 Updated by Raymond Hoh 3 months ago

  • Status changed from In Progress to Resolved

Good call about adding a check to see if the post title is different than what is already recorded in activity meta before updating it.

Just added this check before running the script. Script ran successfully.

#6 Updated by Boone Gorges 3 months ago

Outstanding! Thanks, Ray.

#7 Updated by Boone Gorges 2 days ago

  • Related to Bug #11122: Recurrence of incorrect blog_title meta updating added

Also available in: Atom PDF