BadgeOS queries killed on WPEngine

Resolved cjerrells
(@cjerrells)

9 years ago

Hi,

I’m using BadgeOS 1.4.4 (with the Community addon 1.2.0) on a site hosted on WPEngine. I’m having trouble with users being granted badges they haven’t actually earned yet.

Looking at the debug log I’m seeing a lot of errors like:
[01-Apr-2015 18:20:24] KILLED QUERY (17526 characters long generated in /nas/wp/www/cluster-2186/moremusical/wp-content/plugins/badgeos/includes/steps-ui.php:175): SELECT wp_posts.*, wp_p2p.* FROM wp_posts INNER JOIN wp_p2p WHERE 1=1 AND wp_posts.post_type = 'planning-award' AND ((wp_posts.post_status = 'publish')) AND (FIND_IN_SET(wp_posts.post_type,'forum,topic,reply') OR FIND_IN_SET(wp_posts.ID,'6,7,8,9,10,11,12,13,[...]')) AND (wp_p2p.p2p_type = 'planning-award-to-step' AND wp_posts.ID = wp_p2p.p2p_from AND wp_p2p.p2p_to IN (SELECT wp_posts.ID FROM wp_posts WHERE 1=1 AND wp_posts.ID IN (1609) AND wp_posts.post_type = 'step' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'closed' OR wp_posts.post_author = 93 AND wp_posts.post_status = 'private' OR wp_posts.post_author = 93 AND wp_posts.post_status = 'hidden') AND (FIND_IN_SET(wp_posts.post_type,'forum,topic,reply') OR FIND_IN_SET(wp_posts.ID,'6,7,8,9,10,11,12,[ ... ]')) ORDER BY wp_posts.post_date DESC )) ORDER BY wp_posts.post_date DESC LIMIT 0, 1

In the two places I’ve marked […] there is actually a great long list of numbers.

I’m guessing this might be due to using the Community Activity “Reply to a Forum Topic” step and similar in my badge steps.

WPEngine has a policy of killing queries which are too long.

Is there a way to rewrite these SQL queries so they don’t get so epically long?

This is pretty urgent for me, as my users are getting awarded badges they haven’t earned, which scuppers the whole system a bit!

https://wordpress.org/plugins/badgeos/

Viewing 13 replies - 1 through 13 (of 13 total)

Michael Beckwith
(@tw2113)

The BenchPresser

9 years ago
Hmm, sorry to hear that it’s causing a ruckus. However, I’m also not sure what can be done with it given the vast amount of stuff it’s ending up querying for.

Line 175 from steps-ui.php is right around where this is is from:
```
$connected_activities = @get_posts( array(
	'post_type'        => $requirements['achievement_type'],
	'posts_per_page'   => 1,
	'suppress_filters' => false,
	'connected_type'   => $requirements['achievement_type'] . '-to-step',
	'connected_to'     => $step_id
));
```
So it’s really a case of WordPress itself constructing the query internally.
Thread Starter cjerrells
(@cjerrells)

9 years ago

Hi Michael,

Thanks for the fast response.

It seems pretty surprising that WordPress would generate a query containing (I presume) every single Post ID! I can’t seem to turn up anybody else encountering an issue with this from get_posts to try to find a workaround or solution.

I can’t quite wrap my head around the mapping from the steps-ui.php code to that query to understand why there is even that FIND_IN_SET clause there. It seems like the problem would be mitigated somewhat if the two lengthy FIND_IN_SET expressions were in two successive queries, but I’m out of my depth to figure out how to make that happen.

From the name of the file, it seems like this should only cause problems in the backend UI anyway? Perhaps my problem with users being awarded badges they haven’t earned is actually unrelated to these error messages?

Michael Beckwith
(@tw2113)

The BenchPresser

9 years ago

At least in terms of BuddyPress/bbPress, I can only find 4 instances of “FIND_IN_SET”, and they’re inside the following 4 functions:

bbPress:
bbp_get_topic_subscribers
bbp_get_forum_subscribers
bbp_get_topic_favoriters

BuddyPress:
bb_delete_topic

None of them are being used by BadgeOS that I can tell, so I’m at a loss there at the moment. I did the file search from the root of a WP install, so not even WordPress is using that SQL statement type.

Thread Starter cjerrells
(@cjerrells)

9 years ago

I’ve just done a search across all this install’s files and I found the same occurrences as you. Perhaps it’s generated by a PHP library WordPress uses to generate its SQL queries?

I’m really stuck with this and not sure how to proceed. May have to re-host the site on a VPS rather than WPEngine, but that seems like a pretty drastic measure.

Any ideas?

Michael Beckwith
(@tw2113)

The BenchPresser

9 years ago

I honestly wish I had a better answer. Best I can come up with is that the FIND_IN_SET part is auto-generated by MySQL itself, because it’s not hardcoded in any of the BadgeOS plugins or in WP core.

While perhaps a bit tedious, perhaps make these forum based achievements a submission based one, where they have to provide proof of something, and you award afterwards.

Just trying to come up with some sort of potentially workable alternative here 🙂

Thread Starter cjerrells
(@cjerrells)

9 years ago

Thanks Michael. In this case, manual awarding isn’t practical (it needs to be instant) but perhaps I could use an action on the bbPress post saving hook to call the BadgeOS API and award the relevant achievement.

I’m wondering if this would sidestep the problem, or cause the same steps-ui code to trigger anyway?

It might help to explain the setup a bit more. I have three types of award set up:

– “BP” awards are based on Community actions (one is a BuddyPress “set your profile”, the other a bbPress “Create a forum post”)
– “LD” awards are LearnDash actions (both are completing modules)
– “Big” awards are made up of earning BP and LD awards.

All of the killed SQL queries are being caused by the 4 steps of a particular Big award. Two are the BPs mentioned above and two are LDs. I’ve checked this by logging the requirements[‘achievement_type’] and step_id passed into the steps-ui.php code block mentioned earlier in this thread. All four of the steps are causing killed queries occasionally (not just the BP forum post one).

This seems to cause the Big award to be awarded incorrectly (sometimes not actually earned yet, sometimes not awarded once earned) as well as sometimes the actual BP and LD awards themselves not being awarded when earned.

So I have two questions:

1. Is it possible that the killed queries in that steps-ui.php block are indeed responsible for awards being (not) given incorrectly?

2. Will manually calling the BadgeOS API to award the BP “forum post” award actually solve this, or will the same SQL queries end up being generated anyway?

Thanks again for your help with this. If I can avoid changing hosting that would be a big relief – and I fear this problem is only going to get worse even if I switch host and increase the SQL query length limit. So I’m really keen to find a workaround if at all possible and I very much appreciate your advice!

Michael Beckwith
(@tw2113)

The BenchPresser

9 years ago

If the killed query wasn’t done setting all of the requirements, or requirements not matching up properly because of it, then yes I could see that being part of the why here. If the query failed before even starting to update the requirements or failed afterwards, then the achievements not awarding would be a different issue.

If you can find a workable way to get the appropriate achievement ID, and the user ID, you should be able to award it without much issue using badgeos_award_achievement_to_user(). I checked and this one doesn’t do any “maybe deserves” checks or anything. It just awards, no questions asked.

Thread Starter cjerrells
(@cjerrells)

9 years ago

So I’ve found a way to reproduce this issue on demand, which helps. If I call badgeos_get_required_achievements_for_achievement_list() for the Big award, the 4 queries for its steps fire and get killed (and as a result the output from the function is lacking its normal hyperlinks to each step).

I’ve deleted the forum step from the BP award and set it to be Admin-awarded instead of earned by steps. But strangely I’m still getting the killed queries, and they all include the “FIND_IN_SET(wp_posts.post_type,’forum,topic,reply’)” and “FIND_IN_SET(wp_posts.ID,’6,7,8,9,10[…]” clauses! This seems a bit bizarre since now none of the awards the Big award is dependent on have a forum-related step… Could something other than that “post in forums” step be responsible for this long clause being generated?

Michael Beckwith
(@tw2113)

The BenchPresser

9 years ago

What’s the query string looking like for this new finding, if you are able to retrieve it?

Thread Starter cjerrells
(@cjerrells)

9 years ago

For some reason wordpress.org won’t let me post a reply with the queries in. Please see them here instead: http://pastebin.com/raw.php?i=Q0pf8vik

Michael Beckwith
(@tw2113)

The BenchPresser

9 years ago

Is this causing issues any time you’re doing anything with step-based achievements? Or just when you have specific things selected? Trying to determine if it’s possible the way steps ui stuff was done is causing them to run regardless.

Thread Starter cjerrells
(@cjerrells)

9 years ago

It seems to be just this Big award and the 4 related achievements. I don’t think I’ve seen any cases of achievements being awarded incorrectly apart from these 5.

What’s special about this Big award is that I think it’s the only achievement I have set up which has earning other achievements as its steps. My other achievements all use LearnDash or Community activities as their steps.

(I do have one other bizarre issue where a user’s BuddyPress profile is showing the wrong achievement. It shows the correct achievement type on the tab, and the user does have one achievement of that type, but the achievement shown is actually one from a different type which has its “shown to user” set to off! Viewing the page doesn’t trigger killed queries though, so I’m assuming that’s a separate issue to this one. Mentioning it in case I’m wrong!)

Michael Beckwith
(@tw2113)

The BenchPresser

8 years, 9 months ago

Any recent developments here cjerrells?

Viewing 13 replies - 1 through 13 (of 13 total)

The topic ‘BadgeOS queries killed on WPEngine’ is closed to new replies.