Bloated wp_usermeta table

We have a Multisite installation using the New Blog Templates plugin. The table wp_usermeta has over 98,000 entries for only 10 sites. We have tried using the following function in the functions file with no success. add_filter('wp','cleanup');
function cleanup() {
error_log("cleanup");
global $wpdb;
$dups = $wpdb->get_results( "SELECT *, COUNT(umeta_id) AS n FROM {$wpdb->usermeta} GROUP BY user_id, meta_key HAVING n > 1");

foreach( $dups as $dup ) {
$wpdb->query( $wpdb->prepare( "DELETE FROM {$wpdb->usermeta} WHERE user_id = %d AND meta_key = %s AND umeta_id <> %d",
$dup->user_id, $dup->meta_key, $dup->umeta_id ) );
}
}
We can't upgrade to the newest version because we would have to upgrade hundreds of installations to make this work correctly.

Is there any way to clean duplicate entries in the wp_usermeta table without breaking the installation?

  • Timothy Bowers
    • Chief Pigeon

    Hey peakstudios.

    Sorry I'm a little confused:

    The table wp_usermeta has over 98,000 entries for only 10 sites.

    We can't upgrade to the newest version because we would have to upgrade hundreds of installations to make this work correctly.

    To confirm, are you saying there are 98,000 members in the usermeta table from 10 websites or from hundreds of websites in multiple installations?

    http://codex.wordpress.org/Database_Description#Table:_wp_usermeta

    Sorry, I'm just trying to understand what exactly happened with your install and what you are looking to achieve. You mention duplicate entries? Duplicate users?

    If you could please elaborate a little further.

    Take care.

  • peakstudios
    • WPMU DEV Initiate

    This is not an install issue it is an issue with your blog theme template plugin. It creates thousands of peices of duplicate user meta. it has an issue with "_" so you see meta values like:
    wp_11_10_9_8_7_6_5_4_3_2user_level
    wp_11_10_9_8_7_6_5_4_3user_level
    wp_11_10_9_8_7_6_5_4user_level
    wp_11_10_9_8_7_6_5user_level
    wp_11_10_9_8_7_6user_level

    and so one for every meta value. The fix we found on your forum didn't work it's the code above. and doesn't find these duplicates.

    It has done this pattern every time a site is made making the total results = 97,307 before we ran the code above the wp_user_meta table had over 170,000 results.

    We need a function to clean these tables so we can keep the sites running properly without doing an upgrade to wp 3.3.1

  • Timothy Bowers
    • Chief Pigeon

    Hey gain.

    This is not an install issue it is an issue with your blog theme template plugin. It creates thousands of peices of duplicate user meta. it has an issue with "_" so you see meta values like:

    Sorry, when I refer to your install I simply mean everything surrounding it including our plugin.

    I'll ask the plugin developer to also weigh in on this as he is better acquainted with how the plugin functions on the DB level and would have a better idea than I. :slight_smile:

    Take care.

  • Timothy Bowers
    • Chief Pigeon

    Hey again.

    As this seems to be an issue with an older version, the best bet is first to upgrade to the latest version 1.5, and then look to see if there are any issues.

    As VeBailovity mentioned:

    the bug that caused the issue as described there has been fixed a few versions back.

    The version you are using is nearly a year old 'Released Date: 2011-2-17".

    If you could please update.

    Take care.

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.