Tag Archives: multisite

Expunge non-public content from a WordPress/BuddyPress installation

It’s a common practice to create local WordPress development environments using a copy of the production database. But this can cause problems with a large production site, as the database can become very large, and it is full of non-public information that you may not want to make available to all members of the development team. This is especially problematic when running a plugin like BuddyPress, which allows users to create a great deal of content with various privacy levels.

To work around this problem for the CUNY Academic Commons, I wrote this plugin: cac-database-cleaner. It will remove all non-public data from a WP database, while still leaving an intact database image that can be used to populate a development environment.

WARNING – This is a dangerous tool, as it deletes large amounts of data. Under no circumstances should you install this plugin on a production site. To use: export your production database; import to a separate database and perform any manual changes necessary for the WordPress site to load locally (such as modification of your local hosts file); activate plugin and navigate to Dashboard > Network Admin > CAC Database Cleaner.

Again, do not use this plugin if you don’t know exactly what you’re doing.

Note that plugin support is ideosyncratic to the CUNY Academic Commons, where we run an old version of BuddyPress Docs, an old fork of BuddyPress Group Documents, a plugin called More Privacy Options, legacy bbPress forums, etc. Feel free to modify the plugin to work with whatever other data you’d like.

Convert multi-db WordPress mysqldump to single-db

On a number of client sites, I use HyperDB or SharDB to spread a WordPress Multisite installation across multiple databases on a single server. However, in my local dev environments, it’s annoying to have thousands of databases. So I use the following technique to create a copy of the remote site that operates in a single database locally.

  1. Use mysqldump to get a backup file. The following command ensures that you don’t pull in information_schema or any other unrelated databases; you can add other DBs to ignore to the NOT IN list:
    $ mysql -u [username] -p -B -N -e "SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME NOT IN ('mysql','tmp','innodb','information_schema','performance_schema')" | xargs mysqldump -u [username] -p --add-drop-table --skip-lock-tables --quick --extended-insert --result-file=[path/to/your/dumpfile.sql] --databases
  2. Use sed to remove all the ‘CREATE DATABASE’ and ‘USE’ lines in the dumpfile. This prevents the multiple databases from being created when importing locally.
    $ sed -i '' -e'/^CREATE DATABASE /d' /path/to/dumpfile.sql
    $ sed -i '' -e'/^USE /d' /path/to/dumpfile.sql
  3. Get the dumpfile to your local machine, and import:
    $ mysql -u [username] -p -e "create database foo"
    $ mysql -u [username] -p foo < ~/path/to/local/dumpfile.sql

    (or whatever technique you use for mysql imports) (don't know why my code formatter is converting < to &lt; but you get the idea).

WordPress/BuddyPress registration and the Office 365 email filter

Just tore through the following problem on a client site (independently discovered by Martha Burtis here). WordPress/BuddyPress sites that allow for self-registration send out emails with activation links of the form: http://example.com/activate/?key=12345 (for BuddyPress) and http://example.com/wp-activate.php?key=12345 (for WordPress multisite). This format trips up the link filter that Microsoft’s Office 365 email service uses. After some experimentation, I figured out that the problem is the word ‘key’ in a URL parameter – once this term is removed from the URL, it passes right through the filter.

So, you can fix the problem by changing the URL parameter in the activation emails. That means (a) changing the text of the email, and (b) changing the server-side logic to expect something other than ‘key’. Here’s how to do it in BuddyPress:

function bbg_activation_email_content( $message ) {
	return str_replace( '?key=', '?activationk=', $message );
add_filter( 'bp_core_activation_signup_user_notification_message', 'bbg_activation_email_content' );

function bbg_screen_activation() {
	global $bp;

if ( !bp_is_current_component( 'activate' ) )
		return false;

// Check if an activation key has been passed
	if ( isset( $_GET['activationk'] ) ) {

// Activate the signup
		$user = apply_filters( 'bp_core_activate_account', bp_core_activate_signup( $_GET['activationk'] ) );

// If there were errors, add a message and redirect
		if ( !empty( $user->errors ) ) {
			bp_core_add_message( $user->get_error_message(), 'error' );
			bp_core_redirect( trailingslashit( bp_get_root_domain() . '/' . $bp->pages->activate->slug ) );

// Check for an uploaded avatar and move that to the correct user folder
		if ( is_multisite() )
			$hashed_key = wp_hash( $_GET['activationk'] );
			$hashed_key = wp_hash( $user );

// Check if the avatar folder exists. If it does, move rename it, move
		// it and delete the signup avatar dir
		if ( file_exists( bp_core_avatar_upload_path() . '/avatars/signups/' . $hashed_key ) )
			@rename( bp_core_avatar_upload_path() . '/avatars/signups/' . $hashed_key, bp_core_avatar_upload_path() . '/avatars/' . $user );

bp_core_add_message( __( 'Your account is now active!', 'buddypress' ) );

$bp->activation_complete = true;

bp_core_load_template( apply_filters( 'bp_core_template_activate', array( 'activate', 'registration/activate' ) ) );
remove_action( 'bp_screens', 'bp_core_screen_activation' );
add_action( 'bp_screens', 'bbg_screen_activation' );

You’d have to do something in the same spirit when not using BuddyPress. For the email, filter ‘wpmu_signup_user_notification_email’. Catching the request and overriding ‘key’ will be trickier. I haven’t experimented with it, but maybe you can hook to ‘activate_header’, detect the presence of $_GET['activationk'], and then redirect to the ‘key=’ URL that wp-activate.php expects.

Hopefully this is enough to help if you’re having the problem.

Selectively deprecating WordPress plugins from Dashboard > Plugins

On large WordPress MS installations where site admins are allowed to manage their own plugins, the list of plugins tends to get crowded over time. Sometimes you introduce a plugin to the network and admins start using it, but some time down the road – a year or two later, even – you decide that you want to deprecate that plugin (maybe to replace it with another one, etc). However, migrating users of one plugin to another plugin is a logistical and technical tangle, and sometimes the best medium-term strategy is to allow existing users of the plugin to keep using it, but to prevent admins from activating it in the future.

Here’s how we’re doing it on the CUNY Academic Commons. In the gist below, $disabled_plugins are the plugins that we don’t want people to activate in the future. In most cases, however, we do want people to be able to deactivate the plugins, so by default, we don’t filter plugins if they’re active. However, we also have an array of $undeactivatable_plugins, which cannot be activated or deactivated.

Props to dev team member Dominic Giglio for writing part of this.

Safely delete spam comments across a large WP network

I’m currently working on a university WordPress network that’s been running for four or five years (an MU veteran!) and has almost 5000 blogs, most of which are defunct (because they’re from previous semesters). Akismet is activated across the network, so there’s not much of a public spam problem. However, even spam comments are stored in the database, and some of the blogs have tens of thousands of spam comments sitting in their tables. I’m going to implement a couple of tricks to keep this from happening in the future (a lightweight honeypot for non-logged-in users, tell Akismet to auto-delete spam comments on old posts). But for now, I’ve got to clean up this mess, because the very large comment and commentmeta tables are causing resource issues.

I wrote a simple script that gradually cycles through all the blogs on the network and deletes comments that have been marked as spam by Akismet. Here it is, with some comments afterward:


  • The number of blogs is hardcoded (4980)
  • The ‘qw_delete_in_progress’ key is a throttle, ensuring that only one of these routines is running at a time. You might call this the poor man’s poor man’s cron.
  • I’ve limited it to 10 comments per pageload, but you could change that if you wanted
  • Put it in an mu-plugins file. When it’s finished running (check the ‘qw_delete_next_blog’ flag in the wp_sitemeta table – it’s done if it’s greater than the total number of blogs on the system), be sure to remove it, or at least comment out the register_shutdown_function line.

Use at your own risk – I’m posting here primarily for my own records 🙂

Kicking the Twitpic habit with WordPress

Twitpic and its ilk are pretty convenient, especially when they’re integrated into mobile Twitter apps. But as recent articles have shown, the terms of service of such services can be downright icky. Twitpic may have changed its tune a few days after the outcry, but honestly, if it takes an outcry to make a company not be evil, then maybe you shouldn’t be dealing with that company.

This is a perfect little side project for Project Reclaim, and something of a no-brainer. Twitpic etc are stripped-down publishing platforms. I already run a couple installations of a non-stripped-down publishing platform, namely WordPress. So I set up my own photo blog in just a couple of minutes.

I already have an instance of WordPress Multisite that I use for a bunch of different purposes. So setting up the blog itself was easy – I went to my Network Admin panel and clicked Add Site. If you’ve never worked with WordPress Multisite before, you should know that it’s already built into the WordPress installation that you may already have. You can read more about how to turn on Multisite at the WordPress Codex, or you can watch a somewhat out-of-date but otherwise charming video of a handsome and engaging speaker talking on this very subject.

Then I found a theme that looks nice with photographs. I didn’t look very far. My favorite visual theme has, for some time, been Allan Cole’s AutoFocus. In the future, I’ll probably build a child theme that has a few tweaks appropriate for my mobile photo blog, but it works pretty nicely out of the box.

Then I fired up my WordPress Android app (there’s one for the iPhone too) and connected it to my new WordPress blog. (You’ll have to enable XML-RPC on your WP blog if you want to use the mobile app.) I tweaked a few of the blog setting in my app, so that the photo would be linked after I published it, and the thumbnails would be of an appropriate size.

Finally, I got a WordPress plugin that sends tweets every time a post is published on the photo blog. I’m using YOURLS (more on this in an upcoming Project Reclaim post), but there are lots of them out there that are freely available. Just search the WordPress plugin repository.

Now, when I want to tweet a picture, here’s what I do. Open the WP app. Create a new post. Click the Media button. Take the photo. Add the content of my tweet in the Title field. Publish. (Don’t have to do it in this order, of course.) Totally painless – and I don’t have to worry about any terms of service. Yippee!

For more reading, here’s another blog post about the very same idea.

New WordPress plugin: Unconfirmed

If you’ve ever been responsible for supporting an installation of WordPress Multisite with open registration, you know that the activation process can be a significant source of headaches. Sometimes activation emails get caught by spam filters. Sometimes they are overlooked and deleted by unwitting users. And, to complicate matters, WP’s safeguards prevent folks from re-registering with the same username or email address. This can result in a lot of support requests that are not particularly easy to handle. Aside from reaching manually into the database for an activation key, there’s not much the admin can do to help the would-be member of the site.

The Unconfirmed Dashboard panel

The Unconfirmed Dashboard panel

My new WordPress plugin Unconfirmed eases this problem a bit, by providing WPMS admins with a new set of tools for managing unactivated registrations. (By naming it “Unconfirmed”, I fully expect that the plugin will join some great movies and books in the pantheon of Important Cultural Objects.) Unconfirmed adds a new panel to your Network Admin Dashboard (under the Users menu). When you visit the Unconfirmed panel, it gives you a list of all pending registrations on your system. The list is easily sortable by registration date, username, email address, and activation key. For each unactivated registration, there are two actions that the admin can perform. “Resend Activation Email” does exactly what it says: it sends an exact duplicate of the original activation email, as created by the WordPress core activation notification functions. “Activate” allows admins to activate a pending registration manually, which will trigger the activation success email to the user.

At the moment, Unconfirmed is compatible with WordPress Multisite (aka Network mode) only. In the future, I may expand the plugin to work with non-MS installations of WP. Unconfirmed works with BuddyPress, too. The plugin was developed for use on the CUNY Academic Commons.

Download Unconfirmed from the wordpress.org repo or follow its development on Github.

Enabling Popularity Contest for WordPress networkwide use

Alex King’s Popularity Contest is a pretty cool way to collect data about which posts on a WordPress site the most popular. The data collected is more sophisticated and customizable than simple analytics, because it distinguishes between page views and things like trackbacks, comments, and other kinds of hits. The plugin supports WordPress Multisite in the sense that it’s possible to activate network-wide; when you do so, the plugin keeps site-specific popularity stats. But what if you want popularity rankings across your entire network?

I recently modified Popularity Contest to do just that. The idea is simple: in order to keep network-wide stats, we need a network-wide table (instead of the default site-specific tables). How do you keep information about all networkwide posts on a single blog? I used Donncha O Caoimh’s Sitewide Tags as a bridge. All posts across the network are copied to the tags blog, and popularity data is indexed on the tags blog.

To make this work, several things are needed. I can’t just give you the files because I’ve altered them in other, irrelevant ways, but I will walk you through the process of setting it up. Also, keep in mind that we’ll be modifying the plugin code for both Popularity Contest and Sitewide Tags, modifications you’ll have to make each time you upgrade the plugins. Make sure you back up your work.

  1. Install Sitewide Tags which can be downloaded from its website. You have to activate a few things in order to turn it on – read the readme carefully. Be sure to take note of the tags blog id number, as we’ll need that in a later step.
  2. In sitewide-tags.php, look for the function sitewide_tags_post(). Near the end of the function is a line that says restore_current_blog();. Immediately after that line, enter the following code:
    update_post_meta( $post->ID, 'tags_post_id', $p );

    That line makes sure that every time a post is aggregated on the tags blog, the original post gets a piece of metadata noting the post id of the corresponding tags blog post. We’ll use that information in a later step.

  3. Download Popularity Contest from its website. I don’t recommend that you activate it yet, especially not networkwide, because it will create a lot of tables that you don’t really need.
  4. The next few steps will require mading some modifications in the main Popularity Contest plugin file, popularity-contest.php. The first modification is to change all references to $wpdb->posts (which, when activated networkwide, will refer to the posts table for the individual blogs) and change them to point to the tags blog post table. A search and replace that replaces $wpdb->posts with wp_posts (or wp_x_posts, if your tags blog is not site number 1 but is instead site x.
  5. The next modification involves the function akpc_init(), near the end of the plugin file. That’s where the table names for the Popularity Contest custom tables are found. We need to make sure that they point to the tags blog. Replace the existing function with this:
    function akpc_init() {
    global $wpdb, $akpc;
    $wpdb->ak_popularity = 'wp_ak_popularity';
    $wpdb->ak_popularity_options = 'wp_ak_popularity_options';
    $akpc = new ak_popularity_contest;

    If your tags blog is something other than blog 1, you could change these table names to match (e.g. wp_15_ak_popularity) but it isn’t really necessary.

  6. Now we have to make Popularity Contest aware of the identity relationships between the tags posts and the original posts. Two snippets of code should do it in most places. First, find the function record_feedback, which starts around line 700. Right before the switch($type) line, insert the following:
    if ( $tags_post_id = get_post_meta( $comment_post_ID, 'tags_post_id', true ) )
    $comment_post_ID = $tags_post_id;

    Next, find the function akpc_api_record_view(), which starts around 2550. Right after array_unique($ids); (around line 2555), insert the following:

    $tags_ids = array();
    foreach ( $ids as $id ) {
    $tags_ids[] = get_post_meta( $id, 'tags_post_id', true );
    $ids = $tags_ids;

    These two modifications make sure that Popularity Contest knows which post on the tags blog corresponds to the post being visited/commented on on the child blogs.

  7. At this point, you can activate the popularity plugin networkwide. Here’s what happens, very roughly:
    • The plugin creates the necessarily popularity tables – just one set for the whole installation.
    • When you publish a new post on any site, it gets copied to the tags blog. Our modification from step 2 makes sure that the copied post ID (let’s say 36) is saved to the original post.
    • When someone visits the original blog post, Popularity Contest fires (because it’s been activated network wide). Our modifications in steps 4 and 5 make sure that the plugin knows to record the activity to the tags blog index, and step 6 make sure that the plugin know which post the activity belongs to.
  8. You’ll need another modification to get the data out, since you’ll want to display it on your site somewhere. The default function for this is called show_top_ranked(). We need to modify so that it gets the requested data from the right place. Replace the stock function with this one:
    function show_top_ranked($limit, $before, $after) {
    switch_to_blog( 1 );
    if ($posts=$this->get_top_ranked_posts($limit)) {
    foreach ($posts as $post) {
    $ud = get_userdata( $post->post_author );
    $before. get_thumbnail( $post->post_author, 36 ) .'<a>ID).'">'
    .$post->post_title.'</a><br /><strong>'. $ud->display_name . '</strong>' . $after
    else {

    Make sure you change the number in the switch_to_blog() call to the id of your tags blog.

I think I’ve remembered everything. Good luck!


For my project, I was moving from a single WordPress site to a multisite situation. The popularity plugin had been running on both setups for a while, so the data was totally messed up and needed to be combined (which meant finding the corresponding post data and adding it together – yeesh!). Here’s the script I used – be careful with it, and keep in mind that it was designed for a *very* specific use. Do not use this code if you don’t understand exactly what every line does!

global $wpdb;

$query = "SELECT * FROM {$wpdb->blogs} WHERE site_id = '{$wpdb->siteid}' ";
$blog_list = $wpdb->get_results( $query, ARRAY_A );

foreach( $blog_list as $blog ) {
//print_r($blog); continue;
if ( $blog['blog_id'] == 1 ) continue;
//if ( $blog['blog_id'] != 83 ) continue;

$tn = 'wp_' . $blog['blog_id'] . '_posts';
$tnmeta = 'wp_' . $blog['blog_id'] . '_postmeta';

$query = "SELECT ID FROM {$tn} WHERE post_type = 'post' AND post_status = 'publish' ";
$posts = $wpdb->get_results( $query, ARRAY_A );

foreach( $posts as $post ) {
$id = $post['ID'];

$query = "SELECT meta_value FROM {$tnmeta} WHERE post_id = '{$id}' AND meta_key = 'tags_post_id' ";
$tags_post_id = $wpdb->get_results( $query, ARRAY_A );
$tpid = $tags_post_id[0]['meta_value'];

$query = "SELECT * FROM wp_ak_popularity WHERE post_id = '{$id}'";
$old_data = $wpdb->get_results( $query, ARRAY_A );
$old_data = $old_data[0];

$query = "SELECT * FROM wp_ak_popularity WHERE post_id = '{$tpid}'";
$new_data = $wpdb->get_results( $query, ARRAY_A );
$new_data = $new_data[0];

if ( $old_data && $new_data ) {
$combined_data = array();

$combined_data['post_id'] = $new_data['post_id'];
$combined_data['last_modified'] = $new_data['last_modified'];

foreach( $old_data as $key => $d ) {
if ( $key == 'post_id' || $key == 'last_modified' )

$combined_data[$key] = (int)$d + (int)$new_data[$key];

$query = 'UPDATE wp_ak_popularity SET ';
foreach( $combined_data as $key => $cd ) {
if ( $key == 'post_id' )
$query .= "{$key} = '{$cd}', ";

$query = substr_replace( $query, '', -2 );
$query .= ' ';

$query .= "WHERE post_id = '{$combined_data['post_id']}'";
$wpdb->query( $query );
print_r( $old_data ); echo "<br>"; print_r($new_data); echo "<br>"; print_r($combined_data); echo "<br>"; echo $query; echo "<br /><br /><br />";


echo "<pre>";
//		print_r($posts);
echo "</pre>";