JOINs in bp-blogs assume global tables are in the same database as BuddyPress's

[johnjamesjacoby]

PROLOGUE

When running BuddyPress in a highly scalable environment (using HyperDB, LudicrousDB, etc...) it's useful to put BuddyPress's database tables in a separate database, maybe even separating reads & writes onto separate servers.

---

PROBLEM

The bp-blogs component assumes that global database tables (like wp_users and wp_blogs) are located in the same database on the same server as BuddyPress.

ERROR

2016/07/05 07:36:04 [error] 22410#22410: *1415849 FastCGI sent in stderr: "PHP message: WordPress database error Table 'flox_networks.wp_blogs' doesn't exist for query SELECT COUNT(DISTINCT b.blog_id) FROM wp_2_bp_user_blogs b LEFT JOIN wp_blogs wb ON b.blog_id = wb.blog_id WHERE wb.deleted = 0 AND wb.spam = 0 AND wb.mature = 0 AND wb.archived = '0' AND user_id = 1

In the above case, the flox_networks database only contains BuddyPress database tables, and no WordPress site tables or global tables.

---

BACKSTORY

Years ago, BuddyPress was audited to remove these types of JOINs in order to work on WordPress.org, to power profiles.wordpress.org. Because the Blogs component was not used there then, this code was never updated.

You can see remnants of this in filters like bp_activity_get_user_join_filter that can be used to intercept the LEFT JOIN query and shim it to point to the correct locations as defined by HyperDB or LudcrousDB.

---

SOLUTION

Improvements to WordPress multisite since BuddyPress 1.5 mean there may be quite a bit that can be cut-out of how the Blogs component works. Sites and their relative data are now adequately cached, and subsequent calls to switch_to_blog() are much less costly, especially with a persistent object cache.

Two things could happen:

• See what can be cut-out of BuddyPress completely, to use WordPress core APIs
• Add filters to BuddyPress queries like we did in the past, and shim around the problem queries

---

EPILOGUE

I'm marking this as Priority: Low but Severity: Major because:

• Low because no one else is likely to run into this issue but Flox.io, or anyone that's running some hugely partitioned WordPress/BuddyPress installation
• Major because pieces of bp-blogs just flat out fail to work in this type of environment

I'm also assigning this to myself to investigate and fix, since it's largely a problem we've created for ourselves on Flox.io, and it's a good opportunity and time for us to send something back upstream.

[johnjamesjacoby]

For additional context, I'll answer a question I expect will come up:

How are you just finding this now?

Flox.io has also totally avoided using BuddyPress's Blogs component, because with our IA plan, WordPress Site functionality overlaps quite a bit with BuddyPress Groups. Both have:

• Invitations
• Members
• Member Roles
• Pages/Sections/Extensions
• Posts/Activity
• Comments/Activity

We recently started looking into how activating Blogs across Flox networks might look and work, and were reminded about these technical limitations.

[boonebgorges]

See what can be cut-out of BuddyPress completely, to use WordPress core APIs
Add filters to BuddyPress queries like we did in the past, and shim around the problem queries

As you note, a couple of years ago, we went through and removed most (all?) JOINs against the global users table. We should do the same thing with JOINs against wp_blogs. While the two points described above are probably good long-term goals, we can mitigate or eliminate the bug described in this ticket by rewriting these queries.

See also #6026, where @imath has written a patch that swaps out some of BP's blog queries with WP_Site_Query, when the latter is available.