WordPress.org

Ready to get started?Download WordPress

Forums

HyperDB
Can be wp_users and wp_usermeta be on different schemas? (6 posts)

  1. krisahil
    Member
    Posted 2 years ago #

    Hi all,
    We're using HyperDB plugin to share the wp_users table across multiple, non-Multisite WordPress 3.2.1 instances. Each instance uses its own database for all of its tables, except for wp_users, which is placed in a shared database.

    Everything works fine except when WP tries to run a query that joins the wp_users and wp_usermeta tables. It returns 0 rows when that happens.

    In the UI, this happens if you go to:
    /wp-admin/users.php?role=administrator
    Or edit a post and see that the "change author" select list doesn't appear.
    It seems that both of these features use a query which does a join explained like above.

    One query I've isolated is the following:
    SELECT SQL_CALC_FOUND_ROWS wp_users.ID FROM wp_users INNER JOIN wp_usermeta ON (wp_users.ID = wp_usermeta.user_id) WHERE 1=1 AND ( (wp_usermeta.meta_key = 'wp_capabilities' AND CAST(wp_usermeta.meta_value AS CHAR) LIKE '%\"administrator\"%') ) ORDER BY user_login ASC LIMIT 20;

    Here is the relevant info from db-config.php:

    // Add global/main DB for this specific site
    $wpdb->add_database(array(
            'host'     => $db_host,
            'user'     => 'db_user',
            'password' => 'db_pass',
            'name'     => 'db_main',
    ));
    // Use db_users DB for wp_users table; this enables us to share the wp_users
    // table across non-MU, non-multisite instances.
    $wpdb->add_database(array(
            'host'     => $db_host,
            'user'     => 'db_user',
            'password' => 'db_pass',
            'name'     => 'db_users',
            'dataset'  => 'users',
    ));
    $wpdb->add_callback('add_shared_users');
    function add_shared_users($query, $wpdb) {
            if ($wpdb->base_prefix .'users' == $wpdb->table) {
                    return 'users';
            }
    }

    One more detail: If I move the wp_usermeta into same database as wp_users, the above query does return the correct number of rows. So, as a fallback, I could put each site's wp_usermeta table into the shared database.

    Is what I'm trying to do possible with HyperDB? Or is there a better method for sharing wp_users table across multiple, non-Multisite WP3 instances?

    Thanks for any input!
    -Chris

    http://wordpress.org/extend/plugins/hyperdb/

  2. krisahil
    Member
    Posted 2 years ago #

    If anyone else runs into this issue, we resolved it by altering the SQL query as a string in a filter. Example:

    [Code moderated as per the Forum Rules. Please use the pastebin]

  3. Jonathan Brinley
    Member
    Posted 2 years ago #

    @krisahil, I'd love to see your solution that has, unfortunately, been moderated out.

  4. krisahil
    Member
    Posted 2 years ago #

    I copied it to Pastebin. Still learning this WordPress stuff ;-)

    http://pastebin.com/9H3X0yap

  5. vbk100
    Member
    Posted 2 years ago #

    We're using HyperDB plugin to share the wp_users table across multiple, non-Multisite WordPress 3.2.1 instances. Each instance uses its own database for all of its tables, except for wp_users, which is placed in a shared database.

    I never used this plugin. But I think my multisite installation needs this plugin

    I would like to know if the plugin support multisite setup(Multisite installation ) and the entire network

    Also, how about BuddyPress. DO you have any idea if the plugin supports BuddyPress?

    Looking forward for your kind response

  6. krisahil
    Member
    Posted 2 years ago #

    @vbk100, I've never used BuddyPress.

    Re: Multisite/Network installation, I know that previously WordPress MU installations shared the users table, so I'd assume this functionality would've been migrated to WordPress 3 w/ Multisite.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

Tags