[buddypress-trac] [BuddyPress Trac] #7171: JOINs in bp-blogs assume global tables are in the same database as BuddyPress's

[buddypress-trac]

#7171: JOINs in bp-blogs assume global tables are in the same database as
BuddyPress's
-----------------------------+---------------------------------------
 Reporter:  johnjamesjacoby  |      Owner:
     Type:  defect (bug)     |     Status:  new
 Priority:  low              |  Milestone:  2.7
Component:  Blogs            |    Version:  1.5
 Severity:  major            |   Keywords:  needs-patch needs-testing
-----------------------------+---------------------------------------
 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 `JOIN`s 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.

--
Ticket URL: <https://buddypress.trac.wordpress.org/ticket/7171>
BuddyPress Trac <http://buddypress.org/>
BuddyPress Trac