Issue related to WPMU on multi-db

Hi,

We are running WPMU on multi-db,
its noticed that plugins(both from premium and others) that create tables goes into the DB named "..._wpmud4".

Is this normal or should it go into the "..._wpmuglobal" DB?

Also, our error_log is getting loaded with table does not exist entries: eg: tables are "wp_user_activity", "wp_tips" etc. << these tables does exists on the "..._wpmuglobal" database.....I guess its looking for those tables in the "..._wpmud4" DB?

Is there anything we missed or have done anything wrong while multi-db was installed. ?

Look forward to your thoughts.

Thanks & Regards

  • mikee17
    • Site Builder, Child of Zeus

    Thanks Barry,

    Adding any new global tables to the db-config.php file might solve the issues related to entries on error_log.

    Also, I guess it does not solve the issue of plugins(both from premium and others) that create tables, being created into the DB named "..._wpmud4" ? It should go into the "..._wpmuglobal" DB right?

    So related to above, if tables are created by plugins elsewhere....say like on to "..._wpmud4" should it be manually moved to "..._wpmuglobal" and then add them on the db-config.php file?

    Thanks & Regards

  • mikee17
    • Site Builder, Child of Zeus

    Thanks Aaron,

    So when running on multi-db, you need to first define the new global tables name to db-config.php and then install the plugin.
    I assume that is the reason why the newly created tables in our case is going into "..._wpmud4" instead of "..._wpmud4" database.

    FYI, We just downloaded and installed 'Automatic follow up emails for new users' from https://premium.wpmudev.org/project/automatic-follow-up-emails-for-new-users, the download zip does NOT have the sql.txt or similar...but, for this plugin it not a problem as you can always open automessage.php and get the tablenames.

    Thanks & Regards

  • mikee17
    • Site Builder, Child of Zeus

    Again related to the above, we have a third party plugin that uses Wordpress $wpdb class for database actions and also does not hardcode the wp_ prefix on any table that are created....
    Still the created tables goes into "..._wpmud4" database instead of going into the DB where are the other tables related to that same blog exists...

    What might be the possible cause for this?

    Thanks & Regards

  • mikee17
    • Site Builder, Child of Zeus

    The installer page of the plugin has the following as we see it:
    global $wpdb;
    $table_name = $wpdb->prefix . "table_name_tbl";
    then uses,
    $sql = "CREATE TABLE " . $table_name . " (.....);";
    dbDelta($sql);
    We guess all required to WPMU and multi-db is met on the above?

    We did not add these tables to the global tables list of our multi-db config php file bcos, these tables will be created for all users that activate this plugin and might have to go into a DB that holds the others tables(like: wp_{blog_id}_comments wp_{blog_id}_options etc) related to that blog....
    Is our assumption again wrong and have to add those tables too to the global list?

  • mikee17
    • Site Builder, Child of Zeus

    <?php
    //***** Installer *****
    require_once(ABSPATH . 'wp-admin/upgrade.php');
    //***Installer variables***
    global $wpdb;
    $table_name = $wpdb->prefix . "wp_eCart_tbl";
    ....
    //***Installer***
    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'":wink: != $table_name)
    {
    $sql = "CREATE TABLE " . $table_name . " (
    id int(12) NOT NULL auto_increment,
    name text NOT NULL,
    price varchar(128) NOT NULL,
    product_download_url text NOT NULL,
    downloadable text NOT NULL,
    shipping_cost varchar(128) NOT NULL,
    available_copies varchar(128) NOT NULL,
    button_image_url text NOT NULL,
    return_url text NOT NULL,
    sales_count int(12) NOT NULL,
    description text NOT NULL,
    thumbnail_url text NOT NULL,
    variation1 text NOT NULL,
    variation2 text NOT NULL,
    variation3 text NOT NULL,
    commission varchar(10) NOT NULL default '',
    a1 varchar(128) NOT NULL,
    p1 int(12) NOT NULL,
    t1 varchar(8) NOT NULL,
    a3 varchar(128) NOT NULL,
    p3 int(12) NOT NULL,
    t3 varchar(8) NOT NULL,
    sra SMALLINT NOT NULL,
    srt SMALLINT NOT NULL,
    ref_text varchar(255) NOT NULL,
    paypal_email varchar(128) NOT NULL,
    custom_input SMALLINT NOT NULL,
    custom_input_label varchar(128) NOT NULL,
    variation4 text NOT NULL,
    aweber_list varchar(64) NOT NULL,
    currency_code varchar(8) NOT NULL,
    target_thumb_url varchar(300) NOT NULL,
    target_button_url varchar(300) NOT NULL,
    PRIMARY KEY (id)
    );";
    dbDelta($sql);
    .....
    ?>
    I hope the above is fine?

  • Andrew
    • Champion of Loops

    Actually, can you toss in an echo on $sql just before the dbDelta and run the routine. Then copy/paste the fully query here.

    I want to see what's getting loaded into $table_name. You can also just toss an echo after that if it's easier.

    Thanks,
    Andrew

  • Andrew
    • Champion of Loops

    Here's your problem:

    "CREATE TABLE wp_93_wp_eCart_tbl "

    Note the wp_ prefix being in the middle of the table name. That is what's throwing Multi-DB off.

    So basically this issue is with the plugin I'm afraid. It should be an easy fix though. Just change this:

    $table_name = $wpdb->prefix . "wp_eCart_tbl";

    To this:

    $table_name = $wpdb->prefix . "eCart_tbl";

    Thanks,
    Andrew

  • mikee17
    • Site Builder, Child of Zeus

    Thanks a million Andrew, that did the trick.

    So, the "wp_" in the middle of the table name even though these table names were using wp_prefix($wpdb->prefix), was preventing multi-db to send the tables of this plugin to the correct destination database...

    I guess, this scenario slightly explains the difference between a human brain and computer. :slight_smile:

    Thanks & Regards

  • Marcus
    • Flash Drive

    I'm having a similar problem for a plugin I'm working on, except the table name should be ok.

    The error I get is that the table already exists, because in dbDelta it first checks with $wpdb->get_col('SHOW TABLES'); to see what tables are already there, but during that query it checks the wrong database (table _d4).

    The problem arises finally when it does execute the real query it executes it in the right db but since the first query said the table didn't exist it tries to create a table which already exists.

    Any ideas? The SQL is below:

    CREATE TABLE wp_vote2publish (
    user_id bigint(20) NOT NULL,
    post_id bigint(20) NOT NULL,
    blog_id bigint(20) NOT NULL,
    ip_address char(15) NOT NULL,
    published tinyint(1) NOT NULL DEFAULT '0',
    KEY user_id (user_id),
    KEY post_id (post_id),
    KEY blog_id (blog_id),
    KEY published (published)
    );

  • Marcus
    • Flash Drive

    Thanks for the quick reply Andrew

    This table is just to go on the global table.

    I realised I made a mistake in the SQL by adding two spaces after the KEY statements, but upon fixing that the problem became that it successfully created and altered the table consistently, but still in the wrong database (again _d4).

    Adding the table to the db-config.php file fixed the problem, although I read further up that it shouldn't be necessary?

    for future reference I'd love to know what I need to do for a table that goes on all blogs :slight_smile:

  • Andrew
    • Champion of Loops

    Adding the table to the db-config.php file fixed the problem, although I read further up that it shouldn't be necessary?

    It's not necessary for blog tables to be added to db-config.php. However, global tables must be added. They really needed to be added before the plugin is installed.

    for future reference I'd love to know what I need to do for a table that goes on all blogs :slight_smile:

    You don't have to do anything for Multi-DB. You only have to specify global tables.

    Thanks,
    Andrew

  • Marcus
    • Flash Drive

    Andrew, I may have spoken too soon. I just reinstalled the plugin from scratch and the problem persists.

    I mentioned before:
    The error I get is that the table already exists, because in dbDelta it first checks with $wpdb->get_col('SHOW TABLES'); to see what tables are already there, but during that query it checks the wrong database (table _d4).

    The problem arises in /wp-admin/includes/upgrade.php Line 1231, the function doesn't call $wpdb before that. I call the dbDelta function when the 'init' action is run.

    This only happens during an upgrade. When first installing the plugin it works fine since there are no duplicate tables anyway.

    Any ideas?

  • Aaron
    • CTO

    If you delete your table from both _d4 and the global db and install your plugin fresh after adding the table to db-config.php does it work?

    Note that you need to make sure the wp_ prefix is not hardcoded in your sql, and the entry in db-config.php should not include the prefix at all: "vote2publish"

  • Marcus
    • Flash Drive

    Hi Aaron,

    Yes, it works if I add the table to db-config and do a fresh install. Table gets installed into the right place.

    Problem is what I'm doing is making improvements to the plugin, and so I'm using dbDelta to update the database table. However, as I mentioned, it searches the wrong database first to see if a table exists and tries to write the wrong sql to the right table.

  • Aaron
    • CTO

    Andrew will look into the dbDelta support further.

    I'm beginning to think it could be a multi-db problem as I have seen other plugins (buddypress) that depend on that function have the same issue with their table modifications on upgrade when using multidb. Specifically when bp went 1.1, a modification of the activity cache table was not run (or in the wrong database) when using multidb.

  • Marcus
    • Flash Drive

    I'm inclined to agree. dbDelta tries to access two databases during one call, not much a plugin dev can do about that.

    Now that you mention it, I did have some problems with bp after upgrading where the tables weren't set up properly and threw up errors.

    Thanks for your input!

  • Andrew
    • Champion of Loops

    Hi Guys,

    I've added this to my list. I have a feeling it's going to be tricky because of queries like this:

    $wpdb->get_col('SHOW TABLES');

    There's no way to know whether that query is looking for a global table or a blog table.

    Let me know if you have any thoughts on this.

    Thanks,
    Andrew

  • Marcus
    • Flash Drive

    I can only think of two solutions:

    1. Not sure if this works as I have not looked into the multi-db plugin so this may be way off, but adding some sort of circumvention for plugin devs to force a global database lookup, such as $wpdb->force_global = true;. In my case I'd set and unset it during an upgrade. Dirty and impractical... but I imagine would work?

    2. Request a hook to be added into dbDelta that passes the query variable? You could do something with that....

  • Andrew
    • Champion of Loops

    Hiya,

    1. Not sure if this works as I have not looked into the multi-db plugin so this may be way off, but adding some sort of circumvention for plugin devs to force a global database lookup, such as $wpdb->force_global = true;. In my case I'd set and unset it during an upgrade. Dirty and impractical... but I imagine would work?

    It would be hard to get plugin developers to update their plugins. While there are hundreds of sites using Multi-DB that still only makes up a tiny percentage of the overall WP users.

    2. Request a hook to be added into dbDelta that passes the query variable?

    We'd never get a hook added to dbDelta for something like this I'm afraid.

    I'm going to look and see how HyperDB handles this.

    Thanks,
    Andrew

  • Marcus
    • Flash Drive

    True about point 1, although it would at least give someone like me a good quick workaround :wink:

    I might have an idea for you.... call a filter, check the query to see if it's one of these fiddly queries, throw an exception, use the trace to get the original query sent to dbDelta, and check the table you're dealing with like that.

  • stergatu
    • Site Builder, Child of Zeus

    Hi. Did you found a solution to this problem, which you can shape with us?
    I have the same problem when trying to update my WPMU installation.

    If it helps I'm using db_scaling = 16 and it always check for the "SHOW TABLES" query the database which I use for the md5ed 'd'

    Thanks in advance.

  • stergatu
    • Site Builder, Child of Zeus

    Andrew, thanks for the prompt reply.
    I'm trying to upgrade a very old version of wpmu, 1.3.3 to 1.5 using the multidb 1.0.1 plugin and having php 5.2.9.
    But I also noticed the same problem in other installations when trying to upgrade 2.7 to 2.8.6 using the according multi db version.

    Any help would be very, very much appreciate.
    Thanks
    Lena

  • stergatu
    • Site Builder, Child of Zeus

    I already tried that, but I get the same behaviour.
    The $wpdb object inside the dbdelta function switches to the 14th database in order to perform the "SHOW tables" query.

    Could it be a bug in the multidb version I use (1.0.1)?

  • stergatu
    • Site Builder, Child of Zeus

    SOLVED!!!
    I manage to find what causes the switch to the 14th database for the query "SHOW TABLES;" of the dbdelta function.

    In multidb 1.01. in the db.php at the function analyze_query() none of the "if ( preg_match '" set, was true for the query "SHOW TABLES;". So it gave $table_name = 'unkown'; which also then gave an empty $blog_id="";
    The md5 for the empty string is "d41d8cd98f00b204e9800998ecf8427e" which leads the $wpdb to use the 14th database.

    I use a workaround for my upgrade needs by adding an extra if() in line 848 of the db.php.
    So instead of:
    else if ( preg_match('/^\s*SELECT.*?\s+FOUND_ROWS\(\)/is', $query) ) {
    $table_name = $this->last_table;
    } else {
    $table_name = 'unkown';
    }

    I have the following.

    else if ( preg_match('/^\s*SELECT.*?\s+FOUND_ROWS\(\)/is', $query) ) {
    $table_name = $this->last_table;
    }
    else if ($query=='SHOW TABLES') { //eleni add this
    $table_name = $this->last_table;

    } else {
    $table_name = 'unkown';
    }

    Works for upgrading 1.3.3. to 1.5 using multidb.
    Do you think that this can cause me troubles in other ?

    Thanks in advance
    Lena

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.