[resolved] Database error when enabling mutlisite (33 posts)

  1. BSPBrent
    Posted 5 years ago #

    I'm trying to setup network blog support so I can host multiple blogs in one install using subdirectories. Everything seems to go smoothly, right up to the point when I enable network blog support and have to re-login in. Instead of a getting a login page, I get a page "Error establishing a database connection".
    Obviously there is not issue connecting to the database, since the initial install went fine. If I try to go to the wp-admin directory, then I get an error "One or more database tables are unavailable". I enabled repair and tried repairing, but that fails with error that the tables don't exist (i.e. de_2_posts, de_2_comments, de_2_links,...).
    I'm tempted to create those tables manually to see what happens, but I shouldn't have to.

    I can turn multisite support off and everything works fine as a single instance, so this is not a database connection issue. I do have control over the server, but I am not installing in the root directory. The install URL is blog/de/

    define('WP_ALLOW_MULTISITE', true);
    define('MULTISITE', true);
    define('SUBDOMAIN_INSTALL', false);

  2. Did you follow all the steps *exactly*? Becasue those define you pasted are incomplete.


  3. BSPBrent
    Posted 5 years ago #

    Yes, multiple times. I just posted the relevant "defines". As I mentioned, the single blog setup I can get working fine. Everything works if I change MULTISITE to false.

  4. those are not the only relevant defines.

    WP_ALLOW_MULTISITE – enables access to the network menu item
    multisite - true means the network is enabled
    SUBDOMAIN_INSTALL – refers to whether the installation uses subdomains (virtual hosts) or not.
    $base – define the root of the site. If you installed in a folder, the folder would be listed here .This is appended to all of the sites.
    DOMAIN_CURRENT_SITE and PATH_CURRENT_SITE – these values will be used as each site's base address. It must match the main domain.
    SITE_ID_CURRENT_SITE and BLOG_ID_CURRENT_SITE are legacy-named values. These instruct the system that the main site in the network has an ID value of 1, and the entire network has a value of 1.

    Also, did you look at the database? Are the tables even there? Does the db user have ALL permissions on the db? What host are you on? What;s the URL?

  5. BSPBrent
    Posted 5 years ago #

    All the base tables are there. As I mentioned, single blog setup works just fine. This is not a problem with access to the database. But yes, the db user does have ALL permissions.
    I'm not on any host, I'm managing my own servers. Four load balanced web servers. WordPress is on NFS to be available on all web servers and ease of configuration changes.

    DOMAIN_CURRENT_SITE is set to my domain with 'www'.
    $base = '/blog/de/';
    define('PATH_CURRENT_SITE', '/blog/de/');

    Since those values do not change when enabling multisite setup, I doubt the issue is with those declarations.
    I have setup WordPress v2 many times, including the blogs used by wired.com. I'm not new at this by any means, just new to the multisite feature.

  6. Right, those should be fine, but the REST of them seem to be missing.


    /** MultiSite **/
    define('WP_ALLOW_MULTISITE', true);
    define( 'MULTISITE', true );
    define( 'SUBDOMAIN_INSTALL', true );
    $base = '/';
    define( 'DOMAIN_CURRENT_SITE', 'domain.com' );
    define( 'PATH_CURRENT_SITE', '/' );
    define( 'SITE_ID_CURRENT_SITE', 1 );
    define( 'BLOG_ID_CURRENT_SITE', 1 );

    So again, did you copy up ALL the lines to your wp-config? :) (if you feel like sharing yours, that's fine, but remember to NOT share your passwords, ids, or Authentication Unique Keys and Salts.)

  7. BSPBrent
    Posted 5 years ago #

    All the proper configs are there. This is my current config, with the domain changed to be generic. I'm not providing the database login info. That is not where the problem is, otherwise I wouldn't be able to get past the initial setup to enable network blog via the admin interface.

    /** Database Charset to use in creating database tables. */
    define('DB_CHARSET', 'utf8');

    /** The Database Collate type. Don't change this if in doubt. */
    define('DB_COLLATE', 'utf8_general_ci');
    $table_prefix = 'de_';

    define('WP_DEBUG', false);
    define('WP_ALLOW_MULTISITE', true);

    define( 'MULTISITE', true);
    define( 'SUBDOMAIN_INSTALL', false );
    $base = '/blog/de/';
    define( 'DOMAIN_CURRENT_SITE', 'www.mydomain.com' );
    define( 'PATH_CURRENT_SITE', '/blog/de/' );
    define( 'SITE_ID_CURRENT_SITE', 1 );
    define( 'BLOG_ID_CURRENT_SITE', 1 );
    define('WP_ALLOW_REPAIR', true);

  8. I'm not new at this by any means, just new to the multisite feature.

    It's a whole new ball of wax. ;) Which is why I asked the questions I did. That's where we start.

    . I'm not providing the database login info.

    And we would never ask you to. :D these relevant sections are great.

    and sometimes, even if you've done it a hundred times, you can overlook something.

    All the base tables are there.

    irght, but it's looking for de_2_posts, de_2_comments, de_2_links, as you said above .... which would ONLY show up if you have registered a second. (which is kind of impossible)

    I know you can install it with a different prefix and in a folder. looking for blog table 2 is something I haven;t been able to reproduce.

    So... did you turn off plugins?

  9. BSPBrent
    Posted 5 years ago #

    I did not turn off plugins, I emptied the plugins directory. To double check, I turned off multisite and login into the admin area. Switching to plugins shows "No plugins to show".
    For the record, I did create the blogs.dir directory and it is writeable by apache.

    It seems to be failing to create the initial network blog tables (i.e. de_2_*).

  10. Stupid idea.

    Comment out this: define('WP_ALLOW_REPAIR', true);

  11. It seems to be failing to create the initial network blog tables (i.e. de_2_*).

    Those are not the initial tables tho. Those are only created when you go to Super Admin -> Sites and create a new site.

    Becasue they have a #2 in them. Your main site is #1. Did it create the *global* tables?

  12. BSPBrent
    Posted 5 years ago #

    These are the tables that get created:


  13. uapv_thy
    Posted 5 years ago #

    I have the same problem, after my install of network.
    A "repair" of the database when I want to connect in /wp-admin/ say me that it can't find tables wp_2_comments, wp_2_... (9 tables are missing).

    I duplicate theses tables with the good names and it work now (more or less, because i forgot to change a WP_ value in the table WP_2_option about list of role, this is ok now but I still have a problem with list of my site in the super admin menu, but I am going to post this problem in a new threat)

    So with my 9 new tables hand made, I can connect my main site. I can create a new site in the superadmin menu, and it create the 9 tables needs for this site (WP_12_ ... 12 because my sql increment is 10 by 10)

    The problem was this 9 tables missing after network installation.

    bests regards

  14. (WP_12_ ... 12 because my sql increment is 10 by 10)

    The problem was this 9 tables missing after network installation.


    This is the issue here. The _2 tables are for the SECOND site, they are not needed in a FRESH install.

    BSPBrent - do have have yours set to increment tables by anything other than 1?

  15. uapv_thy
    Posted 5 years ago #

    It is a little confuse for me,
    without these 9 tables (WP_2 ...) I had the error :
    "Error establishing a database connection"

    After created them manually, my main site was reachable without error.

    If I post on my main site, this post is store in the WP_2_post table.

    About that and with the explication following I thing that my WP_2_ tables has to be view like WP_1_ tables in a normal configuration

    The increment in my mysql database is due to ours sql plateform which have some mysql servers (less that 10) running in paralell. each of them having a different first value and a increment of 10. If one server is down one of the 8 other can do the job and we can re-synchronize all the database without problem because they all use different values for insert order .
    I am by default on the server 2 (it's why my first table was WP_2_ and the next WP_12_
    I hope it's clear, because for more explanation I have to ask more to my sys op and I'm not sure to understand all his words... ;-)

  16. barneygumble78
    Posted 5 years ago #

    Hello guys
    I have a problem on the upgrade from wpmu 2.9.2 to 3.0.5
    Following the guides, I've just to run the automatic update, update the htaccess file and update the blog network.
    The update itself does not report any error.
    But what I've found is whenever I create a new blog (site) wp says correctly site added, but the only thing updated is a main site db table. Any site-specific table is created.
    Furthermore, on the admin page, the link I have as backend admin page is not the one I'm expecting but the main blog one
    main site backend: http://blog.mainsite.org/wp-admin/
    site created "newsite"
    site backend page: http://blog.mainsite.org/wp-admin/
    and I expect

    Do you have any suggestion for something I may double check looking for what's wrong?

    Thanks, Yuri

  17. barneygumble78 - Please make a NEW post. Your issue is not related to the topic at hand and it dilutes our ability to help anyone.

    To the people having this problem, did commenting out
    define('WP_ALLOW_REPAIR', true);
    have ANY effect?

  18. barneygumble78
    Posted 5 years ago #

    Already done but unsuccessful
    it says wp_<blogid>_<tablename> is missing
    I'm going to open a new post as suggested

  19. BSPBrent
    Posted 5 years ago #

    I started over with a clean code download. I did not have the WP_ALLOW_REPAIR setting in place and still had the same issue. I tried creating the tables manually, but still got the database connection error.

    My databases are set to have an auto increment increment of 3 for replication, but WordPress is looking for de_2... tables. But that is an interesting suggestion. I would certainly hope WordPress doesn't have issues with increment values.

    I enabled the repair option and tried to "repair" the tables, but everything came back ok.

    The de_users table is okay.
    The de_usermeta table is okay.
    The de_2_posts table is okay.
    The de_2_comments table is okay.
    The de_2_links table is okay.
    The de_2_options table is okay.
    The de_2_postmeta table is okay.
    The de_2_terms table is okay.
    The de_2_term_taxonomy table is okay.
    The de_2_term_relationships table is okay.
    The de_2_commentmeta table is okay.
    The de_blogs table is okay.
    The de_signups table is okay.
    The de_site table is okay.
    The de_sitemeta table is okay.
    The de_registration_log table is okay.
    The de_blog_versions table is okay.

  20. I would certainly hope WordPress doesn't have issues with increment values.

    It's legacy code from multisite. It *does* expect an increment value of one.

    (I checked with a dev.)

  21. BSPBrent
    Posted 5 years ago #

    oh no. so does that mean I'm stuck!? Does anyone know where in the code I can check for this?

  22. Ron Rennick
    MultiSite Guru
    Posted 5 years ago #

    What is the id value in the de_site table?

    And, what is the blog_id & site_id on the main site in the de_blogs table?

    Re: Andrea_r's comment above, a 1 might be hardcoded somewhere. I haven't had a chance to check yet. If there is I'll create the ticket & patch.

  23. Ron Rennick
    MultiSite Guru
    Posted 5 years ago #

    If your blog_id for your main site in de_blogs is 2, change it to 1 and your site should run fine.

    I'll create the ticket in trac so we can discuss how to best handle the situation.

  24. Ron Rennick
    MultiSite Guru
    Posted 5 years ago #

  25. BSPBrent
    Posted 5 years ago #

    Yes, the "id" in de_site is 2. For the database server it is on, the auto_increment_offset is set to 2, so that would be the starting increment id.

  26. uapv_thy
    Posted 5 years ago #

    In my case ,
    in the WP_site table I have only one line with my main site.
    with the id=2
    in the WP_blog table I have 3 lines
    my main site have id=2 path=/blogs/
    the first sub-directory blog have 12 path=/blogs/labeltice/
    and the second 22 path=/blogs/licpro/
    (because of an auto-increment of 10 with my mysql server)

    Perhaps link with this problem, I have not the list of all my blolgs in the super admin menu, Only my main site appears.

    (NB my main site was install in a subdirectory not on the root directory)

  27. Ron Rennick
    MultiSite Guru
    Posted 5 years ago #

    To fix your site, try what I suggested in my previous post.

    If your blog_id for your main site in de_blogs is 2, change it to 1 and your site should run fine.

  28. uapv_thy
    Posted 5 years ago #

    I make some experimentations...
    First I try what you suggested : put blog_id=1 to my main site (in the WP_blog table).
    It's continue to go fine (I can post on my blogs and so on)
    but I still not have a complete list of my blogs in the super admin menu.

    So, I saw that my main site (blog) have a site_id = 2 (in the WP_blog table) and all the other have a site_id=1... hummm
    I change the "site_id" with 2 for all and they appear in the menu ! yes!

    To make it as clean as possible, I finally change all site_id to 1 in the WP_blog table and the id of my main site to 1 in the WP_site table.
    In this way I can had new blog without any intervention in the WP_blog table.

    Thx to your help

  29. BSPBrent
    Posted 5 years ago #

    I got the network blog install to work by changing the blog_id in the de_blogs table to 2. However, I also needed to change the SITE_ID_CURRENT_SITE constant in wp-config.php to 2, since that is the starting ID.

    Would changing the BLOG_ID_CURRENT_SITE constant to 2 have worked also instead of updating the database? That seems more of a straightforward solution to work around this "feature". Not that it's hard to update the database.

  30. the problem is the site reference in the db is not the same as the site referred to in the backend.

    changing site_id to 2 basically tells it there's a second *network* in the install.

Topic Closed

This topic has been closed to new replies.

About this Topic